Exporting 100 + Queries to Excel

  • Thread starter Thread starter samcannet1
  • Start date Start date
S

samcannet1

I have 100+ queries that need to be exported from access to aseperate
excel file. They all start with the same prefix. Is there a way to do
this automatically and if so how?

Thanks
Samantha
 
In
I have 100+ queries that need to be exported from access to aseperate
excel file. They all start with the same prefix. Is there a way to do
this automatically and if so how?

Thanks
Samantha
Click to expand...

You can loop through the QueryDefs collection to get the names of all
the matching queries, and then use the TransferSpreadsheet method to
export them. Something like this air code:

'----- start of code -----
Sub ExportQueries()

On Error GoTo Err_Handler

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strQueryName As String

Set db = CurrentDb

For Each qdf In db.QueryDefs
strQueryName = qdf.Name
If strQueryName Like "MyPfx*" Then
SysCmd acSysCmdSetStatus, "Exporting " & strQueryName & "
...."
DoCmd.TransferSpreadsheet _
acExport, _
acSpreadsheetTypeExcel9, _
strQueryName, _
"C:\MyExportFolder\" & strQueryName & ".xls", _
True
End If
Next qdf

DoCmd.Hourglass False
MsgBox "Done!"

Exit_Point:
DoCmd.Hourglass False
SysCmd acSysCmdClearStatus
Set db = Nothing

Err_Handler:
DoCmd.Hourglass False
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub
'----- end of code -----

In the above "MyPfx" is the prefix used for the queries you want to
export.
 
Hi Sam,

Set db =currentdb

With db.queryDefs
''you enumerate all the query
For i = 0 To (.Count - 1)
tmp_str= .item(i).name
''' you check if in the query name there's your prefix and if there's you
export the query
If tmp_str Like "yourcommonprefix*" Then
DoCmd.TransferSpreadsheet acexport, acSpreadsheetTypeExcel9,
tmp_str , "the path where you wanna store your XL files\" & tmp_str & ".xls"
end if
Next i
End With

I didn't test the code... so perhaps there's some bug but... I think it
could do the trick.

HTH Paolo
 
Back
Top