File exists but cannot open from Excel

[vss_01]

Hi,

Hope someone can help and that this is the correct place to post. Have
tried searching through google groups and MSDN.

I am using Excel 2003 and using vba to store a list of files (as
strings on a spreadsheet) from a server location. I then allow users
to search the file with Ctrl+F and open the file based on the name.

Over 30,000 files and a year of using it and no problems until today.
One .xls file will not open. Have opened the file through explorer
manually with no problem and no one else has it open. If I create a
hyperlink on a spreadsheet with the full server path etc, the
hyperlink doesn't work in the same way that workbooks.open doesn't and
ShellExecute doesn't also, same error cannot find file.

Have tried proving to myself that each of procedures are correct, i.e.
have tried one procedure each with workbooks.open, ShellExecute &
ActiveWorkbook.FollowHyperlink. I have tested the string by using
FileSystemObject.FileExists which returns true and I can Set a
dimension as the file and return all its properties like date
modified, name etc. and the .path matches the string.

Any ideas? Only seems to happen if the file is on a server location,
i.e. I copied the entire folder to my c: drive and works fine. I have
used GetFolder to prove that the folder can be opened using the
string.

Any help or possible known issues/resolutions that I can try would be
apreciated.

Thanks

 

[Guest]

Does the workbook contain a macro? I suspect that a firewall is preventing
the file from opening when the hyperlink is activated. Using window explorer
you are not going through the firewall. The firewall can do a lot of checks
from macros to obscene words inside the file.

I would first try to remove macros, then systematically delete items from
the spreadsheet until you find the offending item.

 

[vss_01]

Does the workbook contain a macro? I suspect that a firewall is preventing
thefilefrom opening when the hyperlink is activated. Using window explorer
you are not going through the firewall. The firewall can do a lot of checks
from macros to obscene words inside thefile.

I would first try to remove macros, then systematically delete items from
the spreadsheet until you find the offending item.












- Show quoted text -

Thanks for the reply Joel, looking for any help I can get.

The file that I am opening does have macros and I will remove them and
see what result I get, the macros should be the same as the vast
majority of the files, as they are made from a template. It could have
been modified by a user I guess, so that's something I hadn't thought
of to try, cheers. Hadn't occurred to me that something like that
could give a file not found error.

Cheers

 

[vss_01]

Thanks for the reply Joel, looking for any help I can get.

Thefilethat I am opening does have macros and I will remove them and
see what result I get, the macros should be the same as the vast
majority of the files, as they are made from a template. It could have
been modified by a user I guess, so that's something I hadn't thought
of to try, cheers. Hadn't occurred to me that something like that
could give afilenot found error.

Cheers- Hide quoted text -

- Show quoted text -

Just an additional to this, seems to be related to either the file
name itself or the number of characters in the file name or path.

Tested that before I even disabled the macros in the file and when I
shortened the file name it opened!

Now I have to test whether it is the entire path length or the file
name length that is the problem are a character in the file name etc.

This to me seems some sort of limitation or bug with Workbooks.Open,
as FileExists returns true and when I save a .msg or .doc file with
the exact same file name in the same folder they will open without a
problem.

 

[vss_01]

Just an additional to this, seems to be related to either the file
name itself or the number of characters in the file name or path.

Tested that before I even disabled the macros in the file and when I
shortened the file name it opened!

Now I have to test whether it is the entire path length or the file
name length that is the problem are a character in the file name etc.

This to me seems some sort of limitation or bug with Workbooks.Open,
as FileExists returns true and when I save a .msg or .doc file with
the exact same file name in the same folder they will open without a
problem.- Hide quoted text -

- Show quoted text -

Ok seems after testing that no matter where the .xls file is either
server location or on local disk, if the full path is more than 218
characters then a hyperlink on a spreadsheet and Workbooks.Open cannot
open the file and claim that it can't be found even though FileExists
proves that it can.

Is this a bug that MS should be made aware of, or is there some
defined limitation to Hyperlinks and Workbooks.Open to limit the
string character length?

Or is there something that I am not doing correct or don't understand?

How would one find out if there is supposed to be some limit, if you
don't have vb help (if vb help even mentions it?)