Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

ADO recordset into Excel

carlcocarlco Posts: 9Member
This will take some explaining. I have a VB6 app that users use to get specific data from an Oracle database. Connection is ADO. When they get what they want, they can send the recordset to an Excel template for printing or further manipulation. The program loops through the recordset determining which column in the template to place the data field value into based on other fields in the same record. For example, the first record may have the following fields:

ID = 100
Version = A
Type = Star
Time = 24 hr
Data = 5.2

It would go into the template as follows:

Sample Ver Star5hr Star10hr Star24hr
100 A 5.2

**SORRY, THIS DOESN'T DISPLAY CORRECTLY!! YOU SHOULD SEE 5 COLUMN HEADERS, AND THE VALUE "5.2" SHOULD BE UNDER "Star24hr"**

This is done by making the cell under "Sample" active, then placing the "Recordset("ID")" value into that cell. Then I use the .offset to make the cell to it's right active, and insert the value "Recordset("Version")". Then I have a series of "if-then" statements such as:

If recordset("Type") = 'Star' and _
recordset("Time") = '24 hr' then
currcell.offset(0,3).activate
activecell.value = recordset("Data")
currcell.offset(0,-5).activate
End if

where currcell is a variable for the current activated cell.

Then the program loops to the next record in the database, checks to see if the "Sample" value is the same, if yes it finds the appropriate column for the data and inserts it. That way you can get the entire row filled in with data. If "Sample" is different from the value already in currcell, the activecell moves down one row and the process is repeated for the new sample.

HERE IS THE PROBLEM! :

Sometimes there is more than one value to enter into a specific column per sample. This means that if there is already a value in "Star24hr", the program must know to move to the cell below, enter the value, but then somehow return to the row above and await the next set of conditions to determine where to put the next value. Otherwise, I would get many partially filled rows that would need to be consolidated somehow. If I simply moved down a row for each record, then I would have alot of rows with only one piece of data in each.

EXAMPLE: If the sample "100" has 8 records in the database, 1 record with data for "Star10hr", two records for "Star5hr", and 5 records for "Star24hr", I want the following:

Sample Ver Star5hr Star10hr Star24hr
100 A 4.3 3.3 5.2
2.5 5.0
6.1
7.0
10.1
200 A 1.0 2.3 3.2

**AGAIN--SORRY, TRY TO FOLLOW THE COLUMNS AND PLACE 2 VALUES UNDER "Star5hr", 1 UNDER "Star10hr" and 5 UNDER "Star24hr", THEN FOR SAMPLE 200, 1 VALUE IN EACH COLUMN.**

THIS CAN BE DONE if all the "Star24hr" values are placed IN THE SAME CELL, separated by a bunch of spaces. This forces excel to place each value below the previous one within the same cell, and visually, it's exactly what I want. However, with multiple values in one cell, Excel loses it's ability to do conditional formatting and also to take those data points and graph them, etc. This brings up the point that multiple entries in a cell CAN be split up into one value per COLUMN using the space delimitation, but can these values be similarly split up WITHIN one column and into different ROWS???

SO, I need to get these 8 records to occupy only 5 rows (whatever is the greatest number of records for a specific column), with each value occupying its own cell.

Can this be done???

Is there another way, a trick, a workaround????

Thanks in advance, sorry for the length but I hope it's clear.


Comments

  • StannyBoyStannyBoy Posts: 115Member
    Try using a statement like this:

    select case starValue
    case Star5Hour
    if isempty(cells(2,2)) then
    cells(2,2)=value
    else
    cells(1,2).end(xldown).offset(1)=value
    endif
    case Star24Hour
    if isempty(cells(2,3)) then
    cells(2,3)=value
    else
    cells(1,3).end(xldown).offset(1)=value
    endif
    .
    .
    .




Sign In or Register to comment.