×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Using VBA to populate Excel from SQL

Using VBA to populate Excel from SQL

Using VBA to populate Excel from SQL

(OP)
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 :(

RE: Using VBA to populate Excel from SQL

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.

RE: Using VBA to populate Excel from SQL

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close