Excel Export to multiple worksheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've got this data in an Access table

State Date User B3-AC FTE
AZ APR 16 2006 UAUKT 7 0
AZ APR 29 2006 UAUKT 0 0
AZ APR 28 2006 UAUKT 0 0
CA APR 27 2006 UAUKT 3 0
CA APR 26 2006 UAUKT 0 0
FL APR 25 2006 UAUKT 0 8
FL APR 24 2006 UAUKT 0 8

I want to export this data to one Excel workbook, with a separate Excel
worksheet to represent the data for each state.
 
Here is some code quick and dirty. It basically has a query that is filled
with parameters of the different states that you want, and then it copies the
recordset onto each sheet.

Option Compare Database
Option Explicit

Sub SaveExcelFile()
Dim qry As DAO.QueryDef
Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim rec As DAO.Recordset
Dim rec2 As DAO.Recordset
Dim strQuery As String
Dim strState As String
Dim fld As DAO.Field
Dim i As Integer

strQuery = "SELECT state FROM mytable GROUP BY state;"
Set qry = CurrentDb.CreateQueryDef("", strQuery)
Set rec = qry.OpenRecordset()
qry.SQL = "Select * from mytable where state=[which state]"

Set xl = New Excel.Application
xl.Visible = True
Set wb = xl.Workbooks.Add

With rec
Do Until .EOF
strState = .Fields("state")
qry.Parameters("which state") = strState
Set rec2 = qry.OpenRecordset()
i = 0
Set ws = xl.Worksheets.Add
ws.Name = strState
For Each fld In rec2.Fields
i = i + 1
ws.Cells(1, i) = fld.Name
Next
ws.Range("a2").CopyFromRecordset rec2
.MoveNext
Loop
End With

Set qry = Nothing
Set rec = Nothing
Set rec2 = Nothing
Set xl = Nothing
Set wb = Nothing
Set ws = Nothing
Set fld = Nothing
End Sub


Please let me know if this worked for you and if I can provide any more
assistance.
 
Back
Top