TransferSpreadsheet error in Access 2007 exporting to .xlsx file type


I'm getting the below error message when I try to open an Excel 2007 workbook (.xlsx) that was created during an export from Access 2007 (see code below).

I've seen similar posts, but my issue still persists even when I try using one of these:

acSpreadsheetTypeExcel12Xml
acSpreadsheetTypeExcel12

It will work for .xls files using acSpreadsheetTypeExcel9, but I can't get it to work for .xlsx files.  Any help is greatly appreciated.

Thanks,
Tracy
exportPath = "C:\ExportPriorities\"
fileName = "Technology Request Prioritization List as of " & Format(Date, "YYYY_MM_DD") & ".xlsx"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "QS-New", exportPath & fileName, True

Open in new window

Error-Message.png
LVL 24
TracyVBA DeveloperAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
try deleting the created .xlsx file, then run the codes again

it works here.
0
 
Rey Obrero (Capricorn1)Commented:
change this

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "QS-New", exportPath & fileName, True

with

DoCmd.TransferSpreadsheet acExport, 10, "QS-New", exportPath & fileName, True
0
 
Rey Obrero (Capricorn1)Commented:
or use this

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12xml, "QS-New", exportPath & fileName, True
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
TracyVBA DeveloperAuthor Commented:
I've tried both of these, and I still get the same error.
0
 
TracyVBA DeveloperAuthor Commented:
I call a routine that deletes the file if it already exists before I export the data.

So there's no file with the same name in the folder I'm exporting to.


Public Function FolderCheck()
'Function Purpose:
'To create folder if it doesn't already exist
 
On Error GoTo errHandler
   
    Dim fso As FileSystemObject
   
    Set fso = New FileSystemObject
   
    If Not fso.folderexists(exportPath) Then
        fso.createfolder (exportPath)
    Else
    End If
   
finished:
    Exit Function
   
errHandler:
    Call errorHandler(Err.Number, Err.Description, "MOD_99Utilities", "FolderCheck", exportPath)
    Exit Function
 
End Function
 
Public Function FileCheck()
'Function Purpose:
'To delete file if it already exists
 
On Error GoTo errHandler
 
    Dim fso As FileSystemObject
   
    Set fso = New FileSystemObject
   
    If fso.FileExists(fileName) Then
        fso.DeleteFile (fileName)
    Else
    End If
       
finished:
    Exit Function
 
errHandler:
    Call errorHandler(Err.Number, Err.Description, "MOD_99Utilities", "FileCheck", fileName)
    Exit Function
   
End Function

Open in new window

Sub exportExcel()
'Function Purpose:
'To export 5 queries into an Excel document
 
On Error GoTo errHandler
 
    exportPath = "C:\ExportPriorities\"
    fileName = "Technology Request Prioritization List as of " & Format(Date, "YYYY_MM_DD") & ".xlsx"
   
    Call FolderCheck
    If errFlag Then Exit Sub
    Call FileCheck
    If errFlag Then Exit Sub
   
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "QS-New", exportPath & fileName, True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "QS-ClientSolutions", exportPath & fileName, True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "QS-eForms", exportPath & fileName, True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "QS-Other", exportPath & fileName, True
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "QS-Completed", exportPath & fileName, True
 
finished:
    Exit Sub
 
errHandler:
    Call errorHandler(Err.Number, Err.Description, "MOD_2FormatExcel", "exportExcel", _
                      exportPath & fileName)
    Exit Sub
 
End Sub

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
try adding the name of the sheet


    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "QS-New", exportPath & fileName, True,"Q1"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "QS-ClientSolutions", exportPath & fileName, True,"Q2"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "QS-eForms", exportPath & fileName, True,"Q3"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "QS-Other", exportPath & fileName, True,"Q4"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "QS-Completed", exportPath & fileName, True,"Q5"
 
0
 
TracyVBA DeveloperAuthor Commented:
I tried exporting to a different folder called test and it worked using 10.  I don't know why it didn't work in the ExportPriorities folder.  I had been testing with it, so maybe something got messed up.  I deleted that folder and retried and it works now.  Weird.
0
 
TracyVBA DeveloperAuthor Commented:
Thanks for the help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.