asked on

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

Rey Obrero (Capricorn1)

change this

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

with

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

Rey Obrero (Capricorn1)

or use this

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

Tracy

ASKER

I've tried both of these, and I still get the same error.

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tracy

ASKER

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

Rey Obrero (Capricorn1)

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"

Tracy

ASKER

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.

Tracy

ASKER

Thanks for the help.