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

I tried this solution (with my own file names/locations and access query names) and it gives me a runtime error '3436' Failure creating file.  Any ideas on why?

Private Sub ExportAnalysis_Click()
DoCmd.TransferSpreadsheet acExport, 10, "Analysis-Cost", "C:\Users\xxxxx\Google Drive\Accounts\Analysis.xlsx", True, "Sheet1"
DoCmd.TransferSpreadsheet acExport, 10, "Analysis-Turnover", "C:\Users\xxxxx\Google Drive\Accounts\Analysis.xlsx", True,"Sheet2"
End Sub
Daisy BrownAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ShumsDistinguished Expert - 2017Commented:
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

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
0
Rey Obrero (Capricorn1)Commented:
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
0
Daisy BrownAuthor Commented:
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.
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Daisy BrownAuthor Commented:
I found this code which worked  Thanks for your assistance

Function ExcelSend()
On Error GoTo ExcelSend_Err
Dim dbCurr As DAO.Database
Dim rsNTK As DAO.Recordset
Dim rsHUM As DAO.Recordset
Dim rsHUL As DAO.Recordset
Dim strFolder As String
strFolder = "U:\PharmNetworks\TEST\"
Set dbCurr = CurrentDb()
Set rsHUM = dbCurr.OpenRecordset("SELECT * FROM [HUM01WAC]")
Set rsNTK = dbCurr.OpenRecordset("SELECT * FROM [NTK01WAC]")
Set rsHUL = dbCurr.OpenRecordset("SELECT * FROM [HULTCWAC]")
DoCmd.TransferSpreadsheet acExport, , "HUM01WAC", strFolder & "LoadFile.xlsx", True
DoCmd.TransferSpreadsheet acExport, , "NTK01WAC", strFolder & "LoadFile.xlsx", True
DoCmd.TransferSpreadsheet acExport, , "HULTCWAC", strFolder & "LoadFile.xlsx", True

rsHUM.Close
rsNTK.Close
rsHUL.Close

Set rsHUM = Nothing
Set rsNTK = Nothing
Set rsHUL = Nothing
Set dbCurr = Nothing

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
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.
0
Daisy BrownAuthor Commented:
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.
0
Daisy BrownAuthor Commented:
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.
0
Dale FyeOwner, Developing Solutions LLCCommented:
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
0
PatHartmanCommented:
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?
0
Dale FyeOwner, Developing Solutions LLCCommented:
@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.
0
PatHartmanCommented:
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?
0
Daisy BrownAuthor Commented:
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.
0
Rey Obrero (Capricorn1)Commented:
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
0
Daisy BrownAuthor Commented:
Hi Rey - that works.  Thanks!
0
Daisy BrownAuthor Commented:
I tried multiple approaches until one worked.
0
Dale FyeOwner, Developing Solutions LLCCommented:
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\

Open in new window

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

Open in new window

as you had it in your original post, I'll wager that even the code you accepted will fail.
0
Daisy BrownAuthor Commented:
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.
0
Dale FyeOwner, Developing Solutions LLCCommented:
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"

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
0
Daisy BrownAuthor Commented:
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.
0
Rey Obrero (Capricorn1)Commented:
Good luck with your project;-)
0
PatHartmanCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.