VB6/Excel - Using Char(10) to fit a long rcrdset as two lines within same recordset

[tgifgemini]

Hi everyone,
if you can recall from my previous posting, I had explained that my "spreadshit does not fit into the same page", but I've now unraveled what the other programmer did to accomplish this goal:
looking at the input table to my spreadsheet, he first used "queryDef" to generate a query, then transfered the query output into a recordset (Access file). Because the recordsets are too long to fit into a spreadsheet, causing the spreadsheet to break up into two parts during printing, he used the "char(10) to break up the recordsets forming two lines of data within the same recordset. For example:

Query Table items:
----------------------
firstname Lastname amount oldhours newhours olddate newdate
====== ====== ===== ====== ===== ===== ======
Michael Jackson 100.00 15.00000 20.00000 02/2/07 02/03/07

Recordset output using char(10) to split the recordset into two line:
----------------------------------------------------------------------------
firstname Lastname amount hours date
====== ====== ===== === ======
Michael Jackson 100.00 15.00 02/02/07
--------- -------------- -------- 20.00 02/03/07

As you can see from the above record , the previous one line of reecord is now two lines, but within the same recordset, making it possible to fit the long record into the spreadsheet.

Now, I was using "CopyFromRecordset" function to import my access file into the spreadsheet, but the carriage return does not take effect to split the record into two lines within the same row.

My objective is to programatically populate the spreadsheet making it possible to fit two lines within the same recordset(row) as in the above example.

Thanks for your anticipated input.
tgifgemini

[QVeen72]

Hi,

Open the RecordSet from the query and Populate Access.
Check this Code:
RST>Recordset Open

Dim i As Long
    i=0
    Do While Not RST.EOF
          i=i+1
          ExelWS.Cells(i, 1).Value = RST(0)
          ExelWS.Cells(i, 2).Value = RST(1)
          ExelWS.Cells(i, 3).Value = RST(2)
          ExelWS.Cells(i, 4).Value = RST(3)
          ExelWS.Cells(i, 5).Value = RST(4)
          i=i+1
          ExelWS.Cells(i, 4).Value = RST(5)
          ExelWS.Cells(i, 5).Value = RST(6)
          '
          RST.MoveNext
    Loop

Regards
Veena

[tgifgemini]

Thanks Veena,
I'm gonna give it a go when I get to work tomorrow.
Have a nice weekend.
GiftX.

[tgifgemini]

Hello Veena,
you gave me these codes to populate a spreadsheet one at a time from a recordset. What happens when you don't know how many cells/rows you need to populate in the spreadsheet? How would you code populating number of cells/rows based on the number of data in the recordset?
Below is the initial code you supplied:
Open the RecordSet from the query and Populate Access.
Check this Code:
RST>Recordset Open

Dim i As Long
i=0
Do While Not RST.EOF
i=i+1
ExelWS.Cells(i, 1).Value = RST(0)
ExelWS.Cells(i, 2).Value = RST(1)
ExelWS.Cells(i, 3).Value = RST(2)
ExelWS.Cells(i, 4).Value = RST(3)
ExelWS.Cells(i, 5).Value = RST(4)
i=i+1
ExelWS.Cells(i, 4).Value = RST(5)
ExelWS.Cells(i, 5).Value = RST(6)
'
RST.MoveNext
Loop

Regards
Veena

[QVeen72]

Hi,

The above code works irrespective of Number of records in the Recordset.(up to Max Limit of excel which i think is 64k of rows..not very sure)

as far as columns is considerd, u have to code to break into second line.

Regards
Veena

[tgifgemini]

Good morning Veena,
just a little calarification:
does these codes:

ExelWS.Cells(i, 1).Value = RST(0)
ExelWS.Cells(i, 2).Value = RST(1)
ETC......

is the above code setting up the columns(recordset nodes -
Fname, Lname, age, salary, hours etc.... ?
in otherwords setting up each record?
I am just wondering why I'm hard-coding

rs(0) to rs(6)

Thanks
tgifgemini.

[jireh]

try to make a variable for y axis by setting it to the number of records of your data to make your code more flexible...

[QVeen72]

Hi tgif,

as i dint know ur Field Names, i wrote rs(0)...etc..

u can use:

ExelWS.Cells(i, 1).Value = RST("FName")
ExelWS.Cells(i, 2).Value = RST("LName")

Replace ur field names.

REgards
Veena

[tgifgemini]

Thanks veena and all other contributors to this thread. It's quite clear now. I thought as much that's that objective but I wanted to be sure.
You all have a great week.
tgifgemini.