TransferSpreadsheet works in XP but not in 2K

[DSburgh]

I have some VBA code that pushes a query out to Excel. It works fine on my machine with Windows XP and Access 2003 but not on my managers machine which has Windows 2000 and Access 2000 (don't ask me why the interns machine is brand new and the managers is 3+ years old). Here is the code I have:

Private Sub cmd_Excel_Push_Click()
Dim strPath As String
Dim ExcelFile As Object
strPath = Environ("temp") & "\shipper_" & Format(Now, "dddd_mmmm_dd_yyyy_hh_mm_ss") & ".xls"
MsgBox ("This excel file will be saved to your temp directory. Please resave it to another location.")
'MsgBox strPath
Select Case strTable
Case Is = "Shipper"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_excel_shipper", strPath
Case Is = "Comp"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_excel_comp", strPath
Case Is = "Both"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_excel_both", strPath
End Select
Call Shell("Excel " & strPath, vbMaximizedFocus)
End Sub

The error message I get says "Cannot find C:\Documents" and then "Cannot find and". It seems like it cannot find the temp folder but I thought Environ can be used in 2K and XP.

As of right now I am the only one that has Access 2003 but I would like it to work with both versions.

 

[bubba100]

Check your Access references on the machines that have Win/Acc 2000.

One, that has on occasion given me probelem is the Microsoft Office XX Library. I would get an error message that had nothing to with the problem ("improper date format").

I believe that Office 2000 is "9", Office XP is "10" and Office 2003 is "11".

 

[DSburgh]

Bump, anyone have any more suggestions?

 

[jebry]

Hi!

Have you checked the path printed out in the MsgBox (the one you have commented out) to be sure that it exists on the machine in question?



Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[DSburgh]

jebry,

the excel file is exported to the windows temp folder using the Environ("Temp"). I just discovered that it successfully exports the excel file so it must be erroring out when the Shell function tries to open it.

Brian

 

[jebry]

Hi!

Try this:

Private Sub cmd_Excel_Push_Click()
Dim strPath As String
Dim ExcelFile As Object
strPath = Environ("temp") & "\shipper_" & Format(Now, "dddd_mmmm_dd_yyyy_hh_mm_ss") & ".xls"
'MsgBox strPath
Select Case strTable
Case Is = "Shipper"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_excel_shipper", strPath
Case Is = "Comp"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_excel_comp", strPath
Case Is = "Both"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_excel_both", strPath
End Select3
MsgBox ("This excel file will be saved to your temp directory. Please resave it to another location.")
Call Shell("Excel " & strPath, vbMaximizedFocus)
End Sub

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[DSburgh]

I tried replacing the Shell line with:
Call Shell("EXCEL """ & strPath & """", vbMaximizedFocus)
It works on my machine and if it doesn't work on a 2K machine I will post again but I think that should fix it.