Access having problems opening Excel remotely

[Ian Jack]

I've been given a MS Access module written in London last year to cannibalise for its formatting functions and sub-procedures. Having spent the week analysing the code and mapping the new database to it, I'm now stuck on what seems to be a trivial error. Namely that Access cannot find a pre-created workbook on the local drive.

This is the line used in the code

XLapp.Workbooks.Open FileName:=CheckingFileLocation

• CheckingFileLocation = "C:\AER 2006 Report Production\AER Trading.xls"
• XLapp is declared 'As New excel.Application'

and are both declared as a public variables.

The line produces Run-time error 1004 with the message:

'C:\AER 2006 Report Production\AER Trading.xls' could not be found. Check the spelling of the file name, and verify that the file location is correct.

I've been given administrator rights by my IT dept, unlocked the macro security in Excel, tried hard-coding the Excel spreadsheet location, changed the file location and shaken my fist at the screen, all to no avail. Can anybody help?

 

[alastair69]

Have you checked your file location is spelt as set out above.

 

[Ian Jack]

I have, but now you've made me paranoid so I'll have another check.

 

[alastair69]

When you say "Excel remotely" do you mean it is on your PC or somebody else PC?

 

[Ian Jack]

Al69. Sorry, I'll make myself more clear. I'm using 'remotely' in the sense that my application opens Excel on the same machine. Is it a misnomer to use it so?

 

[alastair69]

ok so you have checked your spelling and is the same error apearing?

 

[Ian Jack]

I have inded and yes, the same error is still haunting my screen.

 

[alastair69]

can you attach a copy of both your Database and the AER Trading.xls and i will have a look for you.

 

[Ian Jack]

That's very kind of you, but I'd get fired quicker than a Kalashnikov in a Kabul bakery were I to release this data before we go to print, to anyone. It's northing personal you see. Besides, I've not even got a proper database yet, just a view with the table structures.

 

[ideefixe]

If you move the file to the same folder as the access DB and use the Application.Path method (can't remember what it is in Access right now, it would be App.Apth in VB and ThisWorkbook.Path in Excel) does it work then?