TransferSpreadsheet great but....

[Guest]

I'm using the Docmd.TransferSpreadsheet to export data out of Access and into
Excel. I'm followed some of the examples in this group. The export works
flawlessly on about 5 of 15 machines. The other machines - running runtime -
generate the error - "Your database or project contains a missing or broken
reference to the file 'Excel.exe' version 1.4."

Need Help badly

Regards
Mike Sundman

 

[Rick Brandt]

I'm using the Docmd.TransferSpreadsheet to export data out of Access
and into Excel. I'm followed some of the examples in this group. The
export works flawlessly on about 5 of 15 machines. The other machines
- running runtime - generate the error - "Your database or project
contains a missing or broken reference to the file 'Excel.exe'
version 1.4."

Need Help badly

Regards
Mike Sundman

Does your app include a reference to Excel? If so why? It's certainly not
required for TransferSpreadsheet. If you have it for other automation
switch to late binding so you can remove the reference. Otherwise everyone
will have to have the same version of that Excel library.

 

[Guest]

Below is a copy of the code i'm using. It has been scabbed together from bits
and pieces found in here. How can I make it usable by everyone.

Public Sub modMoveQueryToExcel()

Dim MyXL As Object
Dim xlwk As Excel.Workbook
Dim strShopOrderNumber As String
Dim strCustomer As String
Dim strTabName As String

strShopOrderNumber =
Forms![frmMainISO9000Database]!tabISOPages.Pages!tabHours.Controls!txtCTblShopOrder
strCustomer = DLookup("strCustomer", "tblUnlinkedWorkInProgressData",
"strShopOrderNumber=Forms![frmMainISO9000Database]!tabISOPages.Pages!tabHours.Controls!txtCTblShopOrder")

strTabName = strShopOrderNumber & "-" & Left(strCustomer, 20)

'this is to close the spreadsheet, if it is open
Set MyXL = CreateObject("Excel.application")
Set xlwk = GetObject("I:\Data
Bases\ExcelExportTemplates\CrossTableVer1.xls")
xlwk.Close
Set xlwk = Nothing

'this is to send the query over
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
"qryCrossTable_Export", "I:\Data
Bases\ExcelExportTemplates\CrossTableVer1.xls", , strTabName

'this is to open Excel and the spreadsheet again
MyXL.Visible = True
MyXL.Workbooks.Open "I:\Data
Bases\ExcelExportTemplates\CrossTableVer1.xls"

End Sub

 

[George Nicholson]

The 6th TransferSpreadsheet argument, "Range", is supposedly only for
importing. The XP and 2003 Help files both say that "when exporting, if you
enter a range the export will fail". So, when you say "works flawlessly on
about 5 of 15 machines", is the data actually being exported to a specific
range or does it simply just show up in that file as a new, generically
named worksheet?

It may not be a matter of why it doesn't work on 10 machines, but why it
does work at all on 5. Does leaving this argument blank solve or cause
problems?

All this assumes that all machines actually have Excel installed...

I almost suggested using DoCmd.OutputTo because of its AutoStart option, but
OutputTo replaces existing XL files, it doesn't add sheets to existing ones
like TransferSpreadsheet does, which it appears is what you want to do.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

Below is a copy of the code i'm using. It has been scabbed together from
bits
and pieces found in here. How can I make it usable by everyone.

Public Sub modMoveQueryToExcel()

Dim MyXL As Object
Dim xlwk As Excel.Workbook
Dim strShopOrderNumber As String
Dim strCustomer As String
Dim strTabName As String

strShopOrderNumber =
Forms![frmMainISO9000Database]!tabISOPages.Pages!tabHours.Controls!txtCTblShopOrder
strCustomer = DLookup("strCustomer", "tblUnlinkedWorkInProgressData",
"strShopOrderNumber=Forms![frmMainISO9000Database]!tabISOPages.Pages!tabHours.Controls!txtCTblShopOrder")

strTabName = strShopOrderNumber & "-" & Left(strCustomer, 20)

'this is to close the spreadsheet, if it is open
Set MyXL = CreateObject("Excel.application")
Set xlwk = GetObject("I:\Data
Bases\ExcelExportTemplates\CrossTableVer1.xls")
xlwk.Close
Set xlwk = Nothing

'this is to send the query over
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
"qryCrossTable_Export", "I:\Data
Bases\ExcelExportTemplates\CrossTableVer1.xls", , strTabName

'this is to open Excel and the spreadsheet again
MyXL.Visible = True
MyXL.Workbooks.Open "I:\Data
Bases\ExcelExportTemplates\CrossTableVer1.xls"

End Sub

Does your app include a reference to Excel? If so why? It's certainly
not
required for TransferSpreadsheet. If you have it for other automation
switch to late binding so you can remove the reference. Otherwise
everyone
will have to have the same version of that Excel library.