05-17-15, 23:28 #1Registered User
- Join Date
- Jul 2014
Unanswered: Exporting into an excel spreadsheet - error unless the formatting or layout is ticked
Not really sure if this should be posted in the VBA forum or Access
I am trying to write code to run an Access query and put it in an excel spreadsheet.
I have used
Private Sub ExportExcel()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "AccessQuery", "C:\Users\KSmith\Documents\KSQuery01.xlsx", True
This gives me an error " is not valid name etc. I have done lots of reading on this and cannot track an error using any of the suggestions.
So then I decided I would give away the code and just use the excel shortcut in (the Access toolbar) External ---> Export ---->Excel I get the same error. (However it lets me cut and paste into an excel spreadsheet and all is fine)
But then if I tick the "Export data with formatting and layout" this error disappears
My question - how can I translate this the formatting and layout criteria into the code to avoid that error?
05-18-15, 01:02 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
Are you using a reserved word or symbol in your column and or table names. I dont know if excel has such a list but there are many words and symbols which shoukdnt be used in access.
it could be that you cannot overwrite an existing file, dunno if that throws this error.I'd rather be riding on the Tiger 800 or the Norton