Module to open excel and export query works with Access/Excel 2000 but not with 2003

[paul.brown]

Hi this module to export a query to Excel works Access/Excel 2000 but
not with Access/Excel 2003. The Module comes up with the error handler
message Please ensure that you have not already got MoIncident.xls
open. and gets to the portion marked **** before it fails.

I have looked around in the help and forums to no avail.

Any guidance will be much appreciated.

Paul

Private Sub CmdMAccInc_Click()
On Error GoTo ErrorHandler

Dim strCurDir As String

'Find Current Directory
strCurDir = CurrentDb.Name
strCurDir = Left(strCurDir, Len(strCurDir) - Len(Dir(strCurDir)))

MsgBox "here"
MsgBox strCurDir

****
'Update spreadsheet with Query Data
DoCmd.TransferSpreadsheet acExport, 8, "qryMOIncident", strCurDir &
"\MoIncident.xls", True



'Open Spreadsheet and allow user to ammend and save as required.
Dim objExcel As Object
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Open (strCurDir & "MoIncident.xls")

Exit Sub

ErrorHandler:
MsgBox "Please ensure that you have not already got MoIncident.xls
open"

End Sub

 

[Alex Dybenko]

Hi,
try to check actual error description, perhaps it will give an idea what
going wrong:

MsgBox "Please ensure that you have not already got MoIncident.xls open,
Err: " & err.description

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[paul.brown]

Hi

With error handling commented out get the error message

run-time error 2220
Microsoft accesss cant open the file
h:\SaftyAuditDB\MoIncident.xls

Someone suggested that it was permissions, however I am an
Administrator, H is my home drive and as stated it works with access
2000 so I dont see why this should be the case.

Any advice appreciated.

Paul

 

[Guest]

It may be a timing issue. Once the TransferSpreadsheet is initiated, it
immediately tries to open the spreadsheet. It could be that Windows has not
had time to complete writing the data to the spreadsheet and releasing it.

In any case, I would recommend you replace the code where you open the
spreadsheet for the user with a Shell function.