?
Solved

TransferSpreadsheet error in Access 2007 exporting to .xlsx file type

Posted on 2011-05-06
8
Medium Priority
?
1,090 Views
Last Modified: 2012-05-11

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
0
Comment
Question by:Tracy
  • 4
  • 4
8 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35706645
change this

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

with

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

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35706655
or use this

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

Author Comment

by:Tracy
ID: 35706680
I've tried both of these, and I still get the same error.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 35706735
try deleting the created .xlsx file, then run the codes again

it works here.
0
 
LVL 24

Author Comment

by:Tracy
ID: 35706782
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35706837
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
 
LVL 24

Author Comment

by:Tracy
ID: 35706861
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
 
LVL 24

Author Closing Comment

by:Tracy
ID: 35706870
Thanks for the help.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question