Results 1 to 2 of 2
  1. #1
    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
    End Sub

    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 Click image for larger version. 

Name:	Question.jpg 
Views:	3 
Size:	73.1 KB 
ID:	16357

    My question - how can I translate this the formatting and layout criteria into the code to avoid that error?

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts