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
DoCmd.TransferSpreadsheet acExport, 10, "Analysis-Turnover", "C:\Users\xxxxx\Google Drive\Accounts\Analysis.xl
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
Hi,
Try below:
Source is from here
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
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.xl
DoCmd.TransferSpreadsheet acExport, 10, "Analysis-Turnover", "C:\Users\xxxxx\Google Drive\Accounts\Analysis.xl
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
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.
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.
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.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
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?
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
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.
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
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\
C:\Users\xxxxx\Google Drive\Accounts\
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"
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.