Solved

DoCmd.TransferSpreadsheet Error Access 2010

Posted on 2014-02-04
6
492 Views
Last Modified: 2016-02-11
I am getting an error when I open an Excel spreadsheet that was created by my Access 2010 application. The error is:
"Excel found unreadable content in tablename.xlsx'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes."

When I click Yes, the spreadsheet opens and has all the data that I sent. I have to do a "Save As" to save this repair.

I am using Access 2010 VBA's DoCmd.TransferSpreadsheet command to export data from a temp table that I created.
Other functions within this same application can create Excel files using the same DoCmd function without encountering this error. In this case I am creating a temporary table with the name that I want the Excel "tab" to have. The other examples use a query or a Table as the source of the data.

Example of the code:
DoCmd.TransferSpreadsheet acExport, , strTempTableName, pstrExcelFileSpec

where:
strTempFileName = "C01A_Pool_203"
pstrExcelFileSpec = "O:\Home\YieldTable_2013_12.xlsx"
0
Comment
Question by:RCUllrich
  • 4
  • 2
6 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39834385
Have you tried explicitly selecting the output format (2nd argument SpreadsheetType)?  Try setting that 2nd argument to "acSpreadsheetTypeExcel9", or the value 8.
0
 
LVL 3

Author Comment

by:RCUllrich
ID: 39835506
I have tried that since about 9 months ago I purposely discovered that it works better to leave out that argument when doing an export. The user is not prompted to change the file type from an xls to xlsx when I leave out that argument.

I would recommend to everyone using Access 2010 with Excel 2010 to leave that parameter blank for exports since Access does not have a value for Excel 2010 format.
0
 
LVL 3

Author Comment

by:RCUllrich
ID: 40798307
I am still getting this error and haven't gotten any solution from Experts Exchange. Here is some more detail in hope that someone has a solution.

I am creating a multiple tabbed spreadsheet in Office 2010 by repeatedly calling

      DoCmd.TransferSpreadsheet acExport, , strTempTableName, pstrExcelFileSpec

with a different strTempTableName and the same Excel File Specification. The file specification (pstrExcelFileSpec) is in the form X:\Foldername\ExcelFilename.xlsx. As you may know the Excel worksheet tab is named the same as the exported table and that is why I create separate temporary tables within my code loop. I don't specify the Excel version since Access doesn't have a spreadsheet type for Excel 2010 and the user is not asked about converting from an older version of Excel when I use the highest "type" available in Excel.

There are currently 17 tabs in the resulting spreadsheet. Each tab has the correct data. There are only 1 to 4 rows of data per tab with 12 columns. I can't find anything wrong with spreadsheet.

Any additional ideas for this problem?
Thanks,
Bob
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 3

Accepted Solution

by:
RCUllrich earned 0 total points
ID: 40801013
I have solved my problem (Do I award myself points????)  I am using a dynamic query instead of a temporary table as the source for the DoCmd.TransferSpreadsheet. There must be some difference that I couldn't determine between exporting the contents of temporary tables to Excel versus exporting queries to Excel. With this new method there is no longer any error in creating or viewing the resulting spreadsheet.

My code (only partially shown here) creates a dynamic (temporary) query :
dim qdf as DAO.QueryDef

strSQL = "SELECT PoolID FROM YieldTable WHERE ..."
 rs = gDB.OpenRecordset(strSQL, dbOpenSnapshot)
 Do While Not rs.EOF
    strTabName = rs!PoolID
    strSQL = "SELECT fields1, field2, ... FROM TableName Where PoolID = " rs!PoolID
    Set qdf = gDB.CreateQueryDef("Temp_qry" & strTabName, strSQL)
    qdf.Close
    DoCmd.TransferSpreadsheet acExport, , "Temp_qry" & strTabName, pstrExcelFileSpec
    'Delete temporary query after transfer completed
    for Each qdf in gDB.QueryDefs
        if qdf.Name = "Temp_qry" & strTabName then
            gDB.QueryDefs.Delete   "Temp_qry" & strTabName
            Exit For
        End If
    Next qdf
    rs.MoveNext
loop
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40801266
I didn't pick up on this before, but you can leave the filespec out of the transferspreadsheet method for exports, it has no effect on those.

You might want to retry the original again without the filespec.

Yes, if you found the solution yourself, you can accept your post as the solution.  If, however, advice provided by someone contributes to your stumbling upon a solution, you can also split the award and throw the contributors a few points based on your perception of their contribution to your solution.  This actually happens quite frequently, where someone posts a suggestion which does not actually solve the problem, but points a user in the right direction.
0
 
LVL 3

Author Closing Comment

by:RCUllrich
ID: 40807598
There may be a hidden bug in Microsoft 2010 in either Excel or Access so this solution is definitely a "Work around".
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

809 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