Solved

DoCmd.TransferSpreadsheet Error Access 2010

Posted on 2014-02-04
6
466 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)
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 3

Accepted Solution

by:
RCUllrich earned 0 total points
Comment Utility
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)
Comment Utility
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
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

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…
This collection of functions covers all the normal rounding methods of just about any numeric value.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

762 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now