Unable to Open XLS files from within a macro

[darronb]

Hi All


I have written a macro in Excel 97 to open several XLS spreadsheets and extract the data. This works fine.

We have now been moved to Citrix and the Office version is 2003. I have tried to run the same macro within Excel 2003 and it will not allow me to open the excel files.

I have tried both URL’s and mapped drives to no avail.

Does anyone have any ideas

Thanks

 

[ClulessChris]

Can we see the code?
Where doese it crash?
What's the error message?

Everybody is somebodys Nutter.

 

[darronb]

This is the bit of code I am using to open the file.

When it gets to this it displays an error message trap that is written into the code. Which is also below

Workbooks.Open FileName:= _
"T:\G2 Service\Relogs\Data Files\Crystal Call Analysis G2.xls"

ErrorRoutine:
'MsgBox "An error has occurred. The macro will end."

MsgBox "The most recent error number is " & Err & _
". Its message text is: " & Error(Err) & _
"You should contact Chris or Darron for further advice"

I have just rem'ed the trap out and the error is

"Run Time Error 1004 - Method 'open' of object Workbooks failed.

 

[combo]

Can you record macro with the same action for this file and execute it successfully?

combo

 

[WalkerEvans]

darronb,

I believe your parentheses are misplaced. Try doing it this way:

Workbooks.Open FileName:= _
        T:\G2 Service\Relogs\Data Files\"Crystal Call Analysis G2.xls"

This should work for you.



----------------------------------------------------------------------------------
"A committee is a life form with six or more legs and no brain." -- L. Long

 

[ClulessChris]

WalkerEvans

Was that a mis-post? I don't see how it could be correct.

Everybody is somebodys Nutter.

 

[darronb]

I am still having a problem, and below is a bit more information.

I am trying to open an xls spreadsheet (which was created by Access 97) from a macro in Excel 2003. The spreadsheet will not open and gives me the error. "Run Time Error 1004, Method "open" of object "Workbooks" failed.

Below is the code i am using to try to open XLS file

ChDir "T:\G2 SERVICE\RELOGS\"
Workbooks.Open Filename:= _
"T:\G2 SERVICE\RELOGS\Data Files\G2north.xls"

When I try to open this file with Excel 2003 it gives me the error" Errors were detected, but Microsoft Office Excel was able to open the file by making the repairs listed below. Save the file to make these repairs permanent.
Renamed invalid sheet name"

Is there a way anyone knows of opening this file from a macro and bypass the error.

NOTE:- This problem does not present itself when you use Excel 97, or 2007.