Solved

DoCmd.TransferSpreadsheet Error Access 2010

Posted on 2014-02-04
6
480 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

863 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

19 Experts available now in Live!

Get 1:1 Help Now