Problem exporting multiple tables into different worksheets in oneworkbook

[Erick C]

Hello everybody -
I am having a problem with a bit of code, and I am hoping someone may
be able to help me out. The code currently opens a window to select
the path to save the exported file. It then takes two tables in the
database and exports the data into individual worksheets. I can get
the data to export into the worksheets, but I am running into problems
with the name of the second worksheet. When I open the Excel file I
get an error:
"Excel found unreadable content in 'filename.xls'. Do you want to
recover contents of this workbook?
When I click on Yes a repairs window opens and it says "Renamed
Invalid Sheet Name".
I am guessing it does not like the name of the second worksheet that I
am trying to export, since I use this same code to export a single
table with no problems. My code is below:

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean
Dim fso As New FileSystemObject

strBrowseMsg = "Select the folder where the Cycle Count Analysis file
will be created:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If

strTable = "Location Analysis Recalculation"
strFile = Format$(Date, "Medium Date") & " Revised Cycle Count
Analysis.xls"
strPathFile = fso.BuildPath(strPath, strFile)
MsgBox strPathFile
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTable, strPathFile

strTable = "Location Analysis Summary for Finance"
strFile = Format$(Date, "Medium Date") & " Revised Cycle Count
Analysis.xls"
strPathFile = fso.BuildPath(strPath, strFile)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, , "'Cycle Count Overview'"

MsgBox "Revised Cycle Count Analysis File Has Been Exported.",
vbOK, "Status"


Can someone please help me figure this out?
Thank you!

 

[Ken Snell [MVP]]

You're trying to use the Range argument for the second export. See this
article for information about the pitfalls associated with this process:

Using the Range Argument of TransferSpreadsheet when Exporting Data to an
EXCEL File (VBA)
http://www.accessmvp.com/KDSnell/EXCEL_Export.htm#ExpRange


An easier way is to create and save a query named "Cycle Count Overview",
and have this query return all the records and fields from the "Location
Analysis Summary for Finance" table. Then export this query instead of the
table.

 

[Erick C]

Hi Ken -
I actually tried to build this into one of my other databases and I
got the same result. I made sure that all of the table names exactly
matched the name that I used in the range argument. It still resulted
in a worksheet naming error when the file is opened. Here is my other
code with matching names:

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean
Dim fso As New FileSystemObject

strBrowseMsg = "Select the folder where the Cycle Count Scorecard will
be created:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If

strTable = "ADM Summary"
strFile = Format$(Date, "Medium Date") & " Cycle Count Scorecard.xls"
strPathFile = fso.BuildPath(strPath, strFile)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTable, strPathFile

strTable = "Region Summary"
strFile = Format$(Date, "Medium Date") & " Cycle Count Scorecard.xls"
strPathFile = fso.BuildPath(strPath, strFile)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, , "'Region Summary'"

strTable = "Location Summary"
strFile = Format$(Date, "Medium Date") & " Cycle Count Scorecard.xls"
strPathFile = fso.BuildPath(strPath, strFile)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, , "'Location Summary'"

It is exporting everything that I want it to into individual tabs, but
the names are still all messed up. I do not know if this can be done
given the fact that I am already doing so much to identify the path,
and it is causing problems with the name later on.
If you do not think that the export will work correctly then I will
work something else out, I was just hoping it would work and save some
grief.

Thank you!

 

[Erick C]

Hi again, Ken.
I was just messing around with the export names, and the exports
actually work if I change the code so the names as "location summary"
rather than "'location summary'". It seems as though when the names
are only in quotes without the apostrophe. Just wanted to let you
know that I think that I got it to work.
Thanks!