Daisy Brown
asked on
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.xl sx", True, "Sheet1"
DoCmd.TransferSpreadsheet acExport, 10, "Analysis-Turnover", "C:\Users\xxxxx\Google Drive\Accounts\Analysis.xl sx", True,"Sheet2"
End Sub
Private Sub ExportAnalysis_Click()
DoCmd.TransferSpreadsheet acExport, 10, "Analysis-Cost", "C:\Users\xxxxx\Google Drive\Accounts\Analysis.xl
DoCmd.TransferSpreadsheet acExport, 10, "Analysis-Turnover", "C:\Users\xxxxx\Google Drive\Accounts\Analysis.xl
End Sub
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.xl sx", True, "NameOfsheet1"
DoCmd.TransferSpreadsheet acExport, 10, "Analysis-Turnover", "C:\Users\xxxxx\Google Drive\Accounts\Analysis.xl sx", True,"NameOfSheet2"
End Sub
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.xl
DoCmd.TransferSpreadsheet acExport, 10, "Analysis-Turnover", "C:\Users\xxxxx\Google Drive\Accounts\Analysis.xl
End Sub
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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.
ASKER
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.xl sx", 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
DoCmd.TransferSpreadsheet acExport, 10, "Analysis-Cost", "C:\Users\xxxxx\Google Drive\Accounts\Analysis.xl
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?
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.
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?
ASKER
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("SELE CT * FROM [RM Questions]")
Set rsSP = dbCurr.OpenRecordset("SELE CT * 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.
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("SELE
Set rsSP = dbCurr.OpenRecordset("SELE
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
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
ASKER
Hi Rey - that works. Thanks!
ASKER
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\
back to:C:\Users\xxxxx\Google Drive\Accounts\
as you had it in your original post, I'll wager that even the code you accepted will fail.
ASKER
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
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'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"
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
ASKER
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.
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.
Try below:
Open in new window
This will make spreadsheet file in C:\Users\xxxxx\Google Drive\Accounts\Analysis.xlSource is from here