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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

paul.brown@dayzim.com - 16 Nov 2006 08:00 GMT

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

Reply to this Message

Alex Dybenko - 16 Nov 2006 08:24 GMT

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

Signature

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

> 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
[quoted text clipped - 37 lines]
>
> End Sub

Reply to this Message

paul.brown@dayzim.com - 16 Nov 2006 15:45 GMT

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

> Hi,
> try to check actual error description, perhaps it will give an idea what
[quoted text clipped - 51 lines]
> >
> > End Sub

Reply to this Message

Klatuu - 16 Nov 2006 16:09 GMT

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.

> Hi
>
[quoted text clipped - 67 lines]
> > >
> > > End Sub

Reply to this Message