Using VBA to populate Excel from SQL

[redlam]

hi - i am a dba by nature but i've been assigned the task of populating a pre-formatted excel spreadsheet from a SQL stored procedure. i've managed to use VBA to establish a connection to the database and execute the stored procedure which returns data to the spreadsheet but i'm having trouble maintaining the required format. the results of the stored procedure look like this:

Project Date Employee Hours
PTMS 1/1/01 Alan Smith 3
PTMS 1/1/01 Dave Jones 5
PTMS 1/2/01 Sue Allen 8

i need to populate a spreadsheet and have it look like this (with dynamic formatting based on number of records returned for each grouping):

Project
PTMS

Date
1/1/01

Employee Hours
Alan Smith 3
Dave Jones 5

Date
1/2/01

Employee Hours
Sue Allen 8

basically - the client is desperately trying to use excel as a report writer. they want dynamic grouping and conditional formatting but they also want the functionality of excel as a spreadsheet - ugh. anyone have any 'simple' solutions???? using crystal reports or access is not an option

 

[Katerine]

Sorry I can't give you a complete answer, but I can try to point in a couple directions I might take if I were to have this issue:

- The first thing I would try is to create a report in Access which groups the data, first by project and then by date, and generate code in Access to automatically export the report to an Excel format.
This option is simplest, but it might cause problems with the user if it doesn't look exactly the way the user wants it.

- If the above option isn't acceptable, you'd need to code in Excel to iterate through the query result recordset, and fill in each cell one by one. I have practically no experience programming for Excel, but here's a bit of commented pseudocode..

In the Excel program:

' Be sure to set reference to Access object library.

Dim dbs as database, rstEmployeeHours as recordset

set dbs=the access database
set rstEmployeeHours=dbs.openrecordset(SQL query name, dbOpenDynaset)

with rstEmployeeHours
if not .BOF 'Check for empty recordset
.MoveFirst
do until .eof
' Check for new project (project not equal to
' previous record's project)
' If new project, move down a cell or two (whatever)
' and insert project name. Move down another and
' insert date.

' Check for new date. Same basic thing.

' Move active cell to data cells and insert data
' for the detail information.
loop
end if
end with


Hope that helps Please let me know if there's anything I can try to further clarify.

 

[Katerine]

Quick note: I just noticed a rather significant problem with my code above, which you've probably already noticed, but just in case..

Right before the "Loop" statement, be sure to include a ".MoveNext" statement. Without this statement, the above code is an infinite loop.