I apologize for the long message. I've done a lot of testing and scouring the web to isolate the issue and am only posting here.
I am using Excel 2007 on Windows XP (32bit) and Windows Vista (64bit).
Background: My process loads up single web pages (.htm), does stuff, puts the data onto another sheet and then closes the .htm file without saving. Workbooks.Open works for this purpose, but it is roughly two to three times slower than Workbooks.Add and exhibits performance degredation after some period of time. For reference: .Open goes through a "cycle" in rough one and a quarter seconds, whereas .Add can process two or three files in one second (with the same exact code). With a few million files to go through, the time adds up
. Part of my process later deletes the temp file that is created.
The XP system I have does this process fine, but other XP systems at my school have the same problem as mentioned below.
Problem: On one of the computers (Vista), the htm files that have a full path of more than 46 characters usually crashes Excel.
How many directories and length of folder name or file name does not seem to matter. Different drives give the same problem. Setting the files to read-only also has no effect. Trusted locations (with subdirectories checked) does not seem to have an effect. The file will occasionally load, but furthur attemps will crash Excel.
Is there some setting I can adjust (maybe in the registry?) to make this work?
Example: Workbooks.Add strExample
If strExample was more than 46 characters (47 or more), Excel crashes. I've tried breaking it down to using three strings or even directly post the address. It doesn't matter (don't know why it would either).
With 46 characters and under, Excel will give a "'FileName' cannot be accessed. The file may be corrupted, located on a server that is not responding, or read-only." After hitting cancel, VBA will popup with a Run-time Error 1004 - Method 'Add' of object 'Workbooks' failed. All of this, however, is fine for my purpose because I have DisplayAlerts set to False in the code and my macro can continue.
I know that strExample should be some sort of template for Excel. The 47 character thing doesn't apply if a .xlsx or .txt file is being "added".
It may be a coincidence, but the filename and extension of what I want to load is 47 characters. However, I've tried another webpage (google.com) and named it with various filename lengths and the problem mentioned above still exists. For organizational purposes, I do not have a lot of leeway in renaming the files and would prefer to keep it as is.
The odd thing is that .Add at first did not work on this machine. Then I toyed around with Excel and switched the drives the files were located on. .Add then worked. However, it stopped working again for some unknown reason. The XP machine behind me has ran .Add fine from the beginning and the full path of the file is in the 80s.
Again, I apologize for the long message. Thanks in advance.
I am using Excel 2007 on Windows XP (32bit) and Windows Vista (64bit).
Background: My process loads up single web pages (.htm), does stuff, puts the data onto another sheet and then closes the .htm file without saving. Workbooks.Open works for this purpose, but it is roughly two to three times slower than Workbooks.Add and exhibits performance degredation after some period of time. For reference: .Open goes through a "cycle" in rough one and a quarter seconds, whereas .Add can process two or three files in one second (with the same exact code). With a few million files to go through, the time adds up
. Part of my process later deletes the temp file that is created. The XP system I have does this process fine, but other XP systems at my school have the same problem as mentioned below.
Problem: On one of the computers (Vista), the htm files that have a full path of more than 46 characters usually crashes Excel.
How many directories and length of folder name or file name does not seem to matter. Different drives give the same problem. Setting the files to read-only also has no effect. Trusted locations (with subdirectories checked) does not seem to have an effect. The file will occasionally load, but furthur attemps will crash Excel.
Is there some setting I can adjust (maybe in the registry?) to make this work?
Example: Workbooks.Add strExample
If strExample was more than 46 characters (47 or more), Excel crashes. I've tried breaking it down to using three strings or even directly post the address. It doesn't matter (don't know why it would either).
With 46 characters and under, Excel will give a "'FileName' cannot be accessed. The file may be corrupted, located on a server that is not responding, or read-only." After hitting cancel, VBA will popup with a Run-time Error 1004 - Method 'Add' of object 'Workbooks' failed. All of this, however, is fine for my purpose because I have DisplayAlerts set to False in the code and my macro can continue.
I know that strExample should be some sort of template for Excel. The 47 character thing doesn't apply if a .xlsx or .txt file is being "added".
It may be a coincidence, but the filename and extension of what I want to load is 47 characters. However, I've tried another webpage (google.com) and named it with various filename lengths and the problem mentioned above still exists. For organizational purposes, I do not have a lot of leeway in renaming the files and would prefer to keep it as is.
The odd thing is that .Add at first did not work on this machine. Then I toyed around with Excel and switched the drives the files were located on. .Add then worked. However, it stopped working again for some unknown reason. The XP machine behind me has ran .Add fine from the beginning and the full path of the file is in the 80s.
Again, I apologize for the long message. Thanks in advance.
Last edited:
