Exporting Access data to multiple Excel Sheets

[G-Man]

Hi,

I am a VBA novice, looking for some help exporting a set of Access 2002 query results to a series of named excel files.

I am not sure of the best way to do this, and I can't figure out if my way will even work because I am stuck iwth and "Invalid Argument" error in my OpenRecordSet statement.

My module looks like this:

Public Sub ExportMyQuery()
Dim strRoot As String
Dim strSQL As String
Dim strFO As String
Dim strFile As String
Dim strTable As String
Dim rs As Recordset
'
strRoot = "C:\temp\"
strTable = "tempFOdata"
'
Set rs = CurrentDb.OpenRecordSet("FOs", dbOpenSnapshot)
If Not rs.EOF Then rs.MoveFirst
Do While Not rs.EOF
strFO = rs!Field1.Value
strFile = strRoot & strFO & ".xls"
strSQL = "SELECT [d1 FY04-RawData wo zero ac].[Field Office], [d1 FY04-RawData wo zero ac].County, [d1 FY04-RawData wo zero ac].[Unit Cost], [d1 FY04-RawData wo zero ac].[MinOfAve# Cost], [d1 FY04-RawData wo zero ac].[MaxOfAve# Cost], [d1 FY04-RawData wo zero ac].[StDevOfAve# Cost]" & _
" INTO " & strTable & _
" FROM [d1 FY04-RawData wo zero ac]" & _
" WHERE [d1 FY04-RawData wo zero ac].[Field Office]='" & strFO & "';"
DoCmd.RunSQL "DROP TABLE " & strTable & ";"
CurrentDb.Execute strSQL
DoCmd.OutputTo acOutputTable, strTable, acFormatXLS, strFile
rs.MoveNext
Loop

End Sub

Any suggestions?

 

[SydneyGeek]

Hi, because Access 2000 and up use both DAO and ADO, you need to qualify your references. Try...

Public Sub ExportMyQuery()
Dim strRoot As String
Dim strSQL As String
Dim strFO As String
Dim strFile As String
Dim strTable As String
Dim dbs as DAO.Database
Dim rs As DAO.Recordset
'
strRoot = "C:\temp\"
strTable = "tempFOdata"
'
Set dbs = CurrentDb()
Set rs = dbs.OpenRecordSet("FOs", dbOpenSnapshot)
If Not rs.EOF Then rs.MoveFirst
Do While Not rs.EOF
strFO = rs!Field1.Value
strFile = strRoot & strFO & ".xls"
strSQL = "SELECT [d1 FY04-RawData wo zero ac].[Field Office], [d1 FY04-RawData wo zero ac].County, [d1 FY04-RawData wo zero ac].[Unit Cost], [d1 FY04-RawData wo zero ac].[MinOfAve# Cost], [d1 FY04-RawData wo zero ac].[MaxOfAve# Cost], [d1 FY04-RawData wo zero ac].[StDevOfAve# Cost]" & _
" INTO " & strTable & _
" FROM [d1 FY04-RawData wo zero ac]" & _
" WHERE [d1 FY04-RawData wo zero ac].[Field Office]='" & strFO & "';"
DoCmd.RunSQL "DROP TABLE " & strTable & ";"
CurrentDb.Execute strSQL
DoCmd.OutputTo acOutputTable, strTable, acFormatXLS, strFile
rs.MoveNext
Loop

End Sub

That should initialise the database and recordset. Post back if you have any other issues.

Denis

 

[G-Man]

Thanks for the input.

Unfortunately, now I get a message "Compile Error: User Defined Type NOt Defined" when the module reaches the "Dim dbs As DAO.Database" statemenet.

Any aditional suggestions?

Thanks,
SF

 

[G-Man]

Okay, I found my answer on on another web site: http://www.techonthenet.com/access/questions/db_object.htm

To correct the problem, you need to open your Access database. Press Alt-F11 to open the Microsoft Visual Basic window. When the Microsoft Visual Basic window appears, select References under the Tools menu.

The References window should appear. Scroll down until you find the option called "Microsoft DAO 3.6 Object Library" and check this item. Then click on the OK button.

Thanks for checking in.