Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
File exists but cannot open from Excel
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
File exists but cannot open from Excel
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. " wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
File exists but cannot open from Excel
On Jul 4, 6:52 pm, Joel wrote:
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. " wrote: Hi, Hope someone can help and that this is the correct place to post. Have tried searching through google groups and MSDN. I am usingExcel2003 and using vba to store a list of files (as strings on a spreadsheet) from a server location. I then allow users to search thefilewith Ctrl+F andopenthefilebased on the name. Over 30,000 files and a year of using it and no problems until today. One .xlsfilewill notopen. Have opened thefilethrough explorer manually with no problem and no one else has itopen. 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.opendoesn't and ShellExecute doesn't also, same errorcannotfindfile. 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 thefileand return all its properties like date modified, name etc. and the .path matches the string. Any ideas? Only seems to happen if thefileis 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- Hide quoted text - - 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
File exists but cannot open from Excel
On Jul 4, 10:50 pm, wrote:
On Jul 4, 6:52 pm, Joel wrote: 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. " wrote: Hi, Hope someone can help and that this is the correct place to post. Have tried searching through google groups and MSDN. I am usingExcel2003 and using vba to store a list of files (as strings on a spreadsheet) from a server location. I then allow users to search thefilewith Ctrl+F andopenthefilebased on the name. Over 30,000 files and a year of using it and no problems until today. One .xlsfilewill notopen. Have opened thefilethrough explorer manually with no problem and no one else has itopen. 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.opendoesn't and ShellExecute doesn't also, same errorcannotfindfile. 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 thefileand return all its properties like date modified, name etc. and the .path matches the string. Any ideas? Only seems to happen if thefileis 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- Hide quoted text - - Show quoted text - 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
File exists but cannot open from Excel
On Jul 5, 11:30 am, wrote:
On Jul 4, 10:50 pm, wrote: On Jul 4, 6:52 pm, Joel wrote: 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. " wrote: Hi, Hope someone can help and that this is the correct place to post. Have tried searching through google groups and MSDN. I am usingExcel2003 and using vba to store a list of files (as strings on a spreadsheet) from a server location. I then allow users to search thefilewith Ctrl+F andopenthefilebased on the name. Over 30,000 files and a year of using it and no problems until today. One .xlsfilewill notopen. Have opened thefilethrough explorer manually with no problem and no one else has itopen. 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.opendoesn't and ShellExecute doesn't also, same errorcannotfindfile. 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 thefileand return all its properties like date modified, name etc. and the .path matches the string. Any ideas? Only seems to happen if thefileis 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- Hide quoted text - - Show quoted text - 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.- 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?) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro in Excel using an Array to Delete File in Directory if it Exists | Excel Programming | |||
Check if file exists / open it | Excel Programming | |||
In Excel - Use Windows Explorer instead of File Open to open file | Excel Discussion (Misc queries) | |||
How to programmatically test whether VBA code exists in an Excel file | Excel Programming | |||
check if a file exists / is open | Excel Programming |