X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Export Multiple Queries To Multiple Worksheets In Excel, Access 2016    
post Nov 15 2019, 06:33 PM

Posts: 8
Joined: 26-September 19

I have the following queries named:

9th Main Query
A&C Main Query
E&L Main Query
HS2 Main Query
MAC Main Query
STEM Main Query
ITC Main Query

Database is named - Failures Absences Discipline

I would like to export all of them to one Excel file AND have each query on a separate worksheet.

I've seen some older posts on this topic using earlier versions of Access, but not for the newer version I'm using. Access 2016.
I also had a hard time following them since I'm not a VBA expert either.

I've only done some very basic VBA and from what I've seen this is the way to do it.

Any help is appreciated. Thanks
This post has been edited by rmcafee: Nov 15 2019, 06:34 PM
Go to the top of the page
post Nov 15 2019, 07:14 PM

UA Moderator
Posts: 76,865
Joined: 19-June 07
From: SunnySandyEggo

Hi. The Access version number shouldn't matter. Try using the TransferSpreadsheet method and provide the desired Sheet name in the Range argument.

Sent from phone...

Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
post Nov 16 2019, 05:07 PM

Posts: 1,997
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Here's an example that exports 3 named queries to 3 separate sheets in a named workbook.
Substitute you own query names in the qryNames array, and your own workbook full path.
Good luck.

' ----------------------------------------------------------------
' Procedure Name: TestExportQueriesToWksheetsInWkBook
' Purpose: Routine to transfer named queries to separate sheets in named Excel workbook
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 16-Nov-19
'Test transfer of 3 queries InstrRevQry, PeopleRankingQuery, Top6Sample
' ----------------------------------------------------------------
Sub TestExportQueriesToWksheetsInWkBook()
10        On Error GoTo TestExportQueriesToWksheetsInWkBook_Error
          Dim db As dao.Database
          Dim qdf As dao.QueryDef
          Dim qryNames(2) As String
20        qryNames(0) = "InstrRevQuery"
30        qryNames(1) = "PeopleRankingQuery"
40        qryNames(2) = "Top6Sample"
          Dim i As Integer
          Dim MyOutputwrkbk As String
50        MyOutputwrkbk = "C:\users\Jack\documents\TestExport.xls"
60        Set db = CurrentDb
70        For i = LBound(qryNames) To UBound(qryNames)

80            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, qryNames(i), MyOutputwrkbk, True
90        Next
100       db.Close
110       Set db = Nothing
120       Debug.Print "Finished exporting " & i & "  worksheets into " & MyOutputwrkbk & " at " & Now()
130       MsgBox "Finished exporting " & i & "  worksheets into " & MyOutputwrkbk & " at " & Now()
140       On Error GoTo 0
150       Exit Sub


160       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure TestExportQueriesToWksheetsInWkBook, line " & Erl & "."

End Sub

Sample completion message :
Finished exporting 3  worksheets into C:\users\Jack\documents\TestExport.xls at 16-Nov-19 5:01:06 PM

Good luck with your project!
Go to the top of the page
post Nov 18 2019, 11:29 AM

Posts: 8
Joined: 26-September 19

orange999, Worked like a charm. Added my additional queries (total of 6 queries) to the code, made some minor edits in the code to accommodate the larger number of queries, and it exported perfectly.

One change I did make was to export it to xlxs instead of xls. That required me to change one line of code in 120 to "acSpreadsheetTypeExcel12Xml".

Here is the completed code for anyone who wants to compare to my original post or your code.

thanks a ton!!!

Sub TestExportQueriesToWksheetsInWkBook()
10 On Error GoTo TestExportQueriesToWksheetsInWkBook_Error
Dim db As dao.Database
Dim qdf As dao.QueryDef
Dim qryNames(6) As String
20 qryNames(0) = "9th Main Query"
30 qryNames(1) = "A&C Main Query"
40 qryNames(2) = "E&L Main Query"
50 qryNames(3) = "HS2 Main Query"
60 qryNames(4) = "MAC Main Query"
70 qryNames(5) = "STEM Main Query"
80 qryNames(6) = "ITC Main Query"
Dim i As Integer
Dim MyOutputwrkbk As String
90 MyOutputwrkbk = "C:\users\e198607541\Desktop\Failures Attendance & Discipline Report.xlsx"
100 Set db = CurrentDb
110 For i = LBound(qryNames) To UBound(qryNames)

120 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, qryNames(i), MyOutputwrkbk, True

130 Next
140 db.Close
150 Set db = Nothing
160 Debug.Print "Finished exporting " & i & " worksheets into " & MyOutputwrkbk & " at " & Now()
170 MsgBox "Finished exporting " & i & " worksheets into " & MyOutputwrkbk & " at " & Now()
180 On Error GoTo 0
190 Exit Sub
Go to the top of the page
post Nov 18 2019, 12:02 PM

UA Moderator
Posts: 76,865
Joined: 19-June 07
From: SunnySandyEggo

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.

Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
post Nov 18 2019, 06:18 PM

Posts: 7,115
Joined: 22-December 10
From: England

If you get any unexpected results in the sheets, post back.

Typically cases where:
There are more than 255 characters to export (Memo/Long Text fields)
numeric values or test values just aren't in the sheets, but were in the data.
Dates start appearing as numbers

Go to the top of the page
post Nov 18 2019, 06:52 PM

UtterAccess VIP
Posts: 3,675
Joined: 19-August 03
From: Auckland, Little Australia

Dates start appearing as numbers

Happens a bit, unfortunately. I try to always use non ambiguous dates dd/mmm/yyyy.

The code as posted has line numbers, which are handy when it errors and you have an error handler, but a pain to deal with coding. The code posted does nothing with the line numbers anyway. Same as DB. Does nothing with it that I can see.

I prefer to use something more modular, so you can add new queries easily. I would have a template file, and multiple worksheets named similar to the queries (I usually add 'Data' to the end of the worksheet name, as I usually dont want to show them, but rather I have pivots etc using them as data sources).

I would rename your queries so they dont have spaces, or special characters. I then put a prefix on them, like "qryExp" - this will show you want these exported. Can add a number after this to get it to sort e.g. "qryExp001Ninth", "qryExp002A&C", etc. Renaming like this you could make it more modular by simply adding a new query with the naming convention, and looping the querydefs collection. No need to list each one in code.

Worksheets in the template would be called "Ninth", "A&C" etc.

I have lots of code around doing this - it has error handling, and reads from ini files, splashscreen, auditing and more, so might be a bit much, but if you want to know, let me know.


Beer, natures brain defragging tool.
Go to the top of the page
post Nov 18 2019, 07:07 PM

UtterAccess VIP
Posts: 3,675
Joined: 19-August 03
From: Auckland, Little Australia

Function TestExportQueriesToWksheetsInWkBook() As Boolean
'Date:          Tuesday, 19 November 2019 12:50:58 PM
'Author:        Stephen Cooper
'Email:         XXXXXXX@XXXXXXXX.com
'In parameters

On Error GoTo HandleError:

TestExportQueriesToWksheetsInWkBook = True

Dim intMouseType As Integer
Dim strErrorMsg As String
Dim varReturn As Variant
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strQdfName As String
Dim strPrefix As String
Dim strSuffix As String
Dim strPath As String
Dim strTemplateName As String

intMouseType = Screen.MousePointer

DoCmd.Hourglass True

strPrefix = "qryExp"
strSuffix = " Main Query"

strPath = "C:\users\e198607541\Desktop\"
strTemplateName = "Failures Attendance & Discipline Report.xlsx"

Set db = CurrentDb

For Each qdf In db.QueryDefs
    strQdfName = qdf.Name
    strErrorMsg = strQdfName
    If UCase(Left(strQdfName, Len(strPrefix))) = UCase(strPrefix) Or UCase(Right(strQdfName, Len(strSuffix))) = UCase(strSuffix) Then
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, strQdfName, strPath & strTemplateName, True
    End If

Debug.Print "Finished exporting " & i & " worksheets into " & strPath & strTemplateName & " at " & Now()

On Error Resume Next
'Close all recordsets etc here
varReturn = SysCmd(acSysCmdClearStatus)
Screen.MousePointer = intMouseType
Set db = Nothing
Exit Function

Select Case Err.Number
Case Else
    LogError "TestExportQueriesToWksheetsInWkBook|" & CurrentProject.Name & "|" & strErrorMsg & "|" & Err.Number & " - " & Err.Description & "| Line number " & Erl
    MsgBox strErrorMsg & " " & Err.Number & " " & Err.Description, vbInformation, "Error"
    TestExportQueriesToWksheetsInWkBook = False
    'DoCmd.Close acForm, strUpdateForm, acSaveNo
    Resume ExitHere
End Select

End Function

Sub LogError(Optional strErrorMsg As String)
'Date:      11th June 4:38 PM
'Author:    Stephen Cooper
'Email:     stephen.cooper@xxx.com.au
'Ph:        8963
'In parameters  -   strErrorMSg - A string with values of variables
'Description:   This will write to a pipe delimited text file all the errors and various
                'times, users etc.
'Notes:         'Error should have user name already.
'Example        LogError("SC5|3089 Object doesnt exist|strSQL = Delete * FROM tblNotHere")   )
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Dim strFileName As String
Dim strFilePath As String
Dim fso As Variant
Dim f As Variant
Dim strUserName As String

'strUserName = GetUser
strUserName = "Insert Name here or search for code GetUser"
strFileName = "ErrorLog.txt"
'strFilePath = CheckPath(GetString("ErrorPath"))
strFilePath = "C:\users\e198607541\Desktop\"

Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.OpenTextFile(strFilePath & strFileName, ForAppending, True)

f.Write strUserName & "|" & strErrorMsg & "|" & Now & vbCrLf
    DoCmd.Hourglass False
    Exit Sub
    Select Case Err.Number
        Case Else
            MsgBox "Ironically there was an error in the error handler " & Err.Number & " " & Err.Description, vbInformation, "Error"
            Resume ExitHere
    End Select
End Sub

Have a button and have it like

if TestExportQueriesToWksheetsInWkBook then
    msgbox "Whatever you want it to say i as it was a success",vbinformation + vbokonly,"Success"
end if'TestExportQueriesToWksheetsInWkBook

Note - air code - untested 'kinda'. Results may vary, if symptoms persist, see you local UA forum board.

Beer, natures brain defragging tool.
Go to the top of the page
post Nov 26 2019, 12:08 PM

Posts: 262
Joined: 27-September 01

The code here seems to hard-code the user's Documents folder, which seems like not a great idea. Retrieving the user's documents folder is a subject that I'm sure has been discussed here before, though I didn't find it in a quick search. I gather that there's more than one way to do it, and that if the script host isn't disabled, the best way is with CreateObject(“WScript.Shell”).SpecialFolders. It deserves its own post.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    10th December 2019 - 10:45 PM