Exporting multiple access queries as separate tabs in the same Excel Workbook

Hi,

Try below:

Private Sub ExportAnalysis_Click()
Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                               TableName:="Analysis-Cost", _
                               Filename:="C:\Users\xxxxx\Google Drive\Accounts\Analysis.xlsx")
Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                               TableName:="Analysis-Turnover", _
                               Filename:="C:\Users\xxxxx\Google Drive\Accounts\Analysis.xlsx")
End Sub

Select all Open in new window

This will make spreadsheet file in C:\Users\xxxxx\Google Drive\Accounts\Analysis.xlsx with two worksheets called Analysis-Cost & Analysis-Turnover
Source is from here

just replace Sheet1 and Sheet2 with sheet names that don't exists in the excel file

change "NameOfsheet1" & "NameOfsheet2" with name of sheet that you prefer

Private Sub ExportAnalysis_Click()
 DoCmd.TransferSpreadsheet acExport, 10, "Analysis-Cost", "C:\Users\xxxxx\Google Drive\Accounts\Analysis.xlsx", True, "NameOfsheet1"
 DoCmd.TransferSpreadsheet acExport, 10, "Analysis-Turnover", "C:\Users\xxxxx\Google Drive\Accounts\Analysis.xlsx", True,"NameOfSheet2"
 End Sub

I have tried both approaches and both yield: Run-time error '3436':
Failure creating file.  

Also, I have tried via a macro which works (export with formatting) it works but it wants to replace the file and only keeps the last version with the last query name exported as a single tab in the workbook.

You may find that it worked because the sheet names contained no special characters.  Your earlier efforts included dashes in the sheet name.

It is only fairly recently that worksheet names started allowing special characters.  It is quite possible that the people on the Access team didn't get the memo.  There are several instances where Access fails when file names do not conform to old file naming standards.

PS - those open recordsets aren't doing anything for you.

Hi Pat - Thank you for the additional information; however, the initial attempt was actually just the sample that I followed as my names did not contain any special characters.  I appreciate the  info on the open record sets as well, will try removing them.

Pat - I tried removing the open recordsets and realized that is what is opening the queries that will populate the spreadsheets.  When I removed them,  I got an object variable not set error so it looks like they need to stay.  It was worth a try.

Have you tried:

DoCmd.TransferSpreadsheet acExport, 10, "Analysis-Cost", "C:\Users\xxxxx\Google Drive\Accounts\Analysis.xlsx", True, "Sheet1$"

Note the $ at the end of the sheet name.  I've found that this makes as difference in some formats.

But I just noticed that you are attempting to do this to GoogleDrive, and that might not work, I've never tired it.  Try changing the path to a local path on your computer and see if that works.

HTH
Dale

Daisy, I can assure you that it is not necessary to open a recordset in memory prior to using the TransferSpreadsheet method.  I have dozens of applications that use the Transfer methods and NONE opens a recordset first.  You seem to have something that works but since your solution is not normal, it would be important to understand why it works when normal methods fail.

Dale, where do you see a reference to Google Drive?  That could certainly be influencing the code.  Is [HUM01WAC] the name of a table linked to a spreadsheet on a Google Drive?

@Daisy,

What was the actual solution?  The archive these questions and answers so that if someone has a similar problem in the future, it would be good to know how you solved it.

@Pat,

Google drive is in the path for the output file.

I see it now.  It was in the earlier threads but not in the one that actually worked.  Is that why the export worked?  Because it was no longer exporting to Google Drive?

Here's the actual code that worked (removed the other 20 queries and tabs for conciseness):

Function ExcelSend()
On Error GoTo ExcelSend_Err

Dim KillFile As String
KillFile = "c:\temp\Sample Questionnaire.xlsx"
'Check that file exists
If Len(Dir$(KillFile)) > 0 Then
    'First remove readonly attribute, if set
    SetAttr KillFile, vbNormal
    'Then delete the file
     Kill KillFile
End If

Dim dbCurr As DAO.Database
Dim rsRM As DAO.Recordset
Dim rsSP As DAO.Recordset

Dim strFolder As String
strFolder = "C:\TEMP\"
Set dbCurr = CurrentDb()
Set rsRM = dbCurr.OpenRecordset("SELECT * FROM [RM Questions]")
Set rsSP = dbCurr.OpenRecordset("SELECT * FROM [SP Questions]")

DoCmd.TransferSpreadsheet acExport, , "RM Questions", strFolder & "Sample Questionnaire.xlsx", True
DoCmd.TransferSpreadsheet acExport, , "SP Questions", strFolder & "Sample Questionnaire.xlsx", True

rsRM.Close
rsSP.Close

Set rsRM = Nothing
Set rsSP = Nothing
Set dbCurr = Nothing

ExcelSend_Exit:
Exit Function
ExcelSend_Err:
MsgBox Error$
Resume ExcelSend Exit
End Function

Now I just need to figure out how to check if my query is empty so it doesn't create an Excel tab with no data.

try this codes

Function ExcelSend()
 On Error GoTo ExcelSend_Err

 Dim KillFile As String
 KillFile = "c:\temp\Sample Questionnaire.xlsx"
 'Check that file exists
 If Len(Dir$(KillFile)) > 0 Then
     'First remove readonly attribute, if set
     SetAttr KillFile, vbNormal
     'Then delete the file
      Kill KillFile
 End If
Dim strFolder As String
 strFolder = "C:\TEMP\"
 'check if query will return records
 if dcount("*","RM Questions")> 0 then
 
 DoCmd.TransferSpreadsheet acExport, , "RM Questions", strFolder & "Sample Questionnaire.xlsx", True
 end if
 
 if dcount("*","SP Questions")> 0 then
 
 DoCmd.TransferSpreadsheet acExport, , "SP Questions", strFolder & "Sample Questionnaire.xlsx", True
 end if
 
 ExcelSend_Exit:
 Exit Function
 ExcelSend_Err:
 MsgBox Error$
 Resume ExcelSend Exit
 End Function

Hi Rey - that works.  Thanks!

I tried multiple approaches until one worked.

I would still argue that the difference is that in your later tests, you were attempting to write to a local drive.  If you change the folder from:

C:\Temp\

Select all Open in new window

back to:

C:\Users\xxxxx\Google Drive\Accounts\

Select all Open in new window

as you had it in your original post, I'll wager that even the code you accepted will fail.

Dale, I want it to write to C:\Temp\ - the reference to the google drive was from a previous poster's sample code.  It is working fine so hopefully you didn't put big bucks on that wager.

Daisy,

I'm glad it is working for you, but if you look closely, your original post includes this statement

DoCmd.TransferSpreadsheet acExport, 10, "Analysis-Cost", "C:\Users\xxxxx\Google Drive\Accounts\Analysis.xlsx", True, "Sheet1"

Select all Open in new window

Which does refer to "\Google Drive\".

I still cannot figure out why your current code is requiring you to open a recordset before exporting; that is not normally required with the TransferSpreadsheet method.

Dale

I agree and when I use the TransferSpreadsheet from Access, it does not require it since it processes the query automatically but without it in this macro, it doesn't know to open the query unless explicitly stated.  And yes the original post did include that as a sample of the code I was trying to create but it was not my actual file location reference.

Good luck with your project;-)

Daisy,
Your procedure is VBA.  It is NOT a macro.  Macros in Access are different from macros in Word and Excel which are in fact written in VBA.  When you write a macro in Access, you choose from a pre-defined list of commands and that is all you can do.