Pulling Data from an SQL sever into Excel 1

[CaptainD]

What's the best way to pull data from an SQL server into an Excel spreadsheet, MSQuery, passed from a stored procedure ?

I have a staffing program (for a fire department) that we moved to an SQL server. Now I need to create a Payroll program (for time sheets) that pulls regular hours worked, overtime hours, time off etc. I can create a view through an Access data project and get the information, now I need to do the calculations, FLSA entries etc. and Excel might be my best choice for that, I don't know, thought I'd check here to get some feed back. I'm in the process of learning stored procedures and I'm not real familier with them at this time. Exporting through Access is to many steps for the floor personnel, I need it more automated. If someone has a better suggestion like doing it in VB or what ever I'm open.

 

[PAH]

I use Get external data MS QUERY Initially pulling data straight from the Database via sql, then used this to build the Stored Procedure and a VBA front end for inputing variables to the spreadsheet

 

[Databaseguy]

If you MUST go to Excel, I'd "push" the data from Access. However, you can group, total, sub total, etc with Access reports. Perhaps you should look into that also.

If you're going the Excel route, here's the basic idea. You'll have to fix it up...

'This is for one field only. Modify the activecell.offset and rst.fields to add additional columns.
Private Sub PopulateExcel()
Dim dbs as database
Dim rst as recordset
Dim objExcel As Excel.Application
Dim strSQl as string
StrSql = "Select * From YourTable"
Set rst = DBS.OpenRecordset(strSQL)
Set objExcel = New Excel.Application
With objExcel
    .Visible = True
    .Workbooks.Add
    .cells(x,y).select 'starting cell, x is col, y row
    .cells(x,y).activate
    Do while not rst.eof
   .activeworksheet.activecell.value = rst.fields(name or index).value
   .activeworksheet.activecell.offset(x,y).select
   .activeworksheet.activecell.offset(x,y).activate
    rst.movenext
    Loop
End With
End Sub

HTH

 

[CaptainD]

I'm looking at using Excel because that is what the Secrataries are use to using, they are lost when they get to Access. Thanks, I'll try your suggestion