Excel 2002

[swoozie]

I recently received a new PC at work with XP and Office 2002, With this upgrade, my code no longer works.

In excel right now (since I can't get any further) I am having issues with the Open method.

i even recorded a macro to see if the syntax was different but is wasnt.

        Workbooks.Open Filename:= _
        "Drive &  Path & Filename.xls"

but it won't work.  Gives an error of :

Run time Error '1004
Method 'Open" of object 'Workbooks' Failed

[si_the_geek]

do you have a file called "Drive & Path & Filename.xls"??

I think what you meant is:

Workbooks.Open Filename:= _
        Drive &  Path & "Filename.xls"

[swoozie]

No, I was only using that as an example of the file name and path

I recorded a macro and the syntax used in the macro is the same as in my code so I can not figure out the issue.

[si_the_geek]

well this code works for me in Excel 2002 (xp):

Workbooks.Open Filename:= _
        "C:\Si\dictionary\common words (in order).xls"

so I can only assume that there is another issue.

I would recommend checking that this file exists just before you run it, maybe like this:

'enter file name & path here
my_file = "C:\Si\dictionary\common words (in order).xls"
               'check it exists
Msgbox "File exists? " & (dir(my_file) <> "")
               'open it
Workbooks.Open Filename:= my_file

I'm assuming that there will be a typo in the name (or the path isnt correct on your new machine), and the message will say "False".

if that doesnt work, open the file manually - you'll probably get some sort of message saying it was written in a previous version. if you save it then it will hopefully clear the problem.

[swoozie]

Well I sort of figured out what was wrong, but I am not sure how to correct it. the file I am opening is download from Crystal Reports in Excel. The old version of Excel did not look at the file for errors before open, this version does. I disabled all the error chacing that I could locate in Options, but that didnt seem to correct the issue. Excel is reading an error on the sheet name.

Any suggestions?

__________________

[si_the_geek]

what is the error? what options do you get when the message comes up?

could you alter the original document to not have the problem in it?

[swoozie]

for some reason, Excel 2002 sees an error in all the files that I download from Crystal Reports, it automatically makes the repair and renames the sheet and the file and pops up the message

"Microsoft Excel File Repair Log

Errors were detected in file 'H:\SR Project Billing\Template Folder\SR Billing Extract.xls'
The following is a list of repairs:

Renamed invalid sheet name."

I dont want excel to assume anything. Anyone have any ideas?

[salvelinus]

Can you first check for the problem in the filename, then rename it? Or just plain rename it, if that's an option. If not, can you copy the file to a different name, then copy it back? I don't work with Crystal, but have seen plenty of people who do have exasperating problems.

[swoozie]

The problem isnt in the filename it is inthe worksheet name and before I can do anything to the file to correct, excel screws it up.

Does anyone know a way to open the file through an early version on excel?

[si_the_geek]

couldnt you alter the Crystal Report, and make it give valid sheet names?

[swoozie]

No, I dont have access to the crystal report. anyways when you download a crystal report in excel format it always modifies the name that will be displayed on the sheet. This never usde to be an issue because excel would just open as is and you could edit. No excel wont let you open as is.

There has to be a setting somewhere to turn off this auto repair but I havent pund it yet. I have been everywhere.