Solved

DoCmd.TransferSpreadsheet Error Access 2010

Posted on 2014-02-04
6
556 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

738 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