Export queries to multiple excel worksheets in the same workbook using VBA.

Hi,

I would like to export a number of access queries to different worksheets in the same workbook using Access VBA.  What's the best method?



geraintcollinsAsked:
Who is Participating?
 
jefftwilleyConnect With a Mentor Commented:
Create a table to put your query names into and use this.

Function OutToExcel()
Dim rsOUT As DAO.Recordset
Dim strOutPutFile As String
Dim strSpecTable As String
Dim strLoc As String
Dim strCo As String

    strOutPutFile = "C:\Whatever.xls"   '<----------Your spreadsheet name

' JT Prepare to create a fresh output file by deleting any old files if they exist
    If FileExist(strOutPutFile) Then
        Kill strOutPutFile
    End If

' JT Create the Output Excel spreadsheet
    strSQL = "Select * from MyQueryTable;"  '<-----------Your query table
    Set rsOUT = CurrentDb.OpenRecordset(strSQL)
    Do Until rsOUT.EOF
        strOutQry = rsOUT!("MyQueryFieldName")    '<---------the name of the field in the query table
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, strOutQry, strOutPutFile, True
    rsOUT.MoveNext
    Loop
    rsOUT.Close
    Set rsOUT = Nothing

End Function
0
 
geraintcollinsAuthor Commented:
very good - thanks mate
0
All Courses

From novice to tech pro — start learning today.