Any tools to check any error on opening excel files?

[Eric]

Does anyone have any suggestions on how to determine the error files in
following case?

I have a summary excel file, which contains a macro to open, save and close
many files. However, if any file has any error, then when I open the error
one, it
will pop up an error message for "... read only ...", then the macro stop
for any further process. If I can identify which one causing this error
berfore running the macro, then I can fix it first before running the macro.
Does anyone have any suggestions on how to check any error for a large
number of files in any simple way without checking it manually by opening
them?

Does anyone have any suggestions?
Thank you in advance for any suggestions
Eric

 

[Bill Manville]

Let's first be clear what issues we are trying to deal with from the
following list (please only indicate those which you expect to
encounter):
- Workbook currently open for updating by someone else
- Workbook is password protected
- Workbook has been saved as read-only recommended
- Workbook has been corrupted and cannot be opened
- Workbook has a Workbook_Open macro that pops up a message box

Bill Manville
MVP - Microsoft Excel, Oxford, England

 

[Eric]

Thank you for your reply

There are 2 possibles issue to cause any error up to my knowledge.

CASE 1
when a file is opening, then it will perform updatelinks, but the source of
data is currently used by other application, therefore the process of
updatelinks cannot perform completely.
CASE 2
A file is opened and perform updatelinks, when it performs close and saving,
the file is failed to write into harddisk because of currently busy, therefore
workbook has been corrupted and cannot be opened.

I think CASE 2 seems to be the reason for error, because I often see the
error message "Fail to write ..." beside the clock on right bottom on XP
toolbar.

Do you have any suggestions on how to solve this problem? or
Do you have any suggestions on how to check any corrupted files without
opening them?
Thank you very much for any suggestions
Eric

 

[Bill Manville]

Failure to write - sounds like your disk/network needs attention.

I have not experienced such problems and I have done quite a lot of
opening, changing and saving of workbooks over the last 20 years of
using Excel.

Best to solve the problem at source if you can.

I don't know of a way to detect that a file is corrupted without
attempting to open it.

Bill Manville
MVP - Microsoft Excel, Oxford, England

 

[Bill Manville]

Just remembered that people have encountered saving problems in the
past when anti-virus software is set up to check files as they are
created. There can be a conflict of activity between Excel (writing to
a temporary file and then renaming it) and the anti-virus software
checking the file. However, that usually results in the temporary file
not being renamed successfully rather than in corruption, I think.

However, if you do have anti-virus software that checks new files when
they are created it would be worth trying with that feature disabled.

Workbook corruption should not be treated as a normal occurrence to
work around but as a problem to be resolved.

Bill Manville
MVP - Microsoft Excel, Oxford, England

 

[Eric]

Thank you very much for suggestions

My computer contains 2 CPUs. I usually open 2 copy of excel applications at
the same time, in order to maximize the usage [100%] of CPUs resources. Each
excel application performs the same process open, updatelinks, save and close
for different files, but all files are located under the same harddisk [E
drive and the XP is located on C drive]
Will it be the conflict of activity between Excels (writing to
a temporary file and then renaming it)? as you described.

If this is the true cause of the problem, can I solve this problem by
locating "A" group of files under "A" harddisk, and "B" group of files under
"B" harddisk? which is physically separated 2 groups of files, in order to
avoid the conflict of activity for temporary files. Since temporary files
for "A" group of files will store in "A" harddisk and temporary files for "B"
group of files will store in "B" harddisk, there will be no chance for the
temporary file not being renamed successfully. Will it solve the problem?

Furthermore, I find out that there is only 500MB free space for harddisk [E
drive 500MB to run Excel application] and [C drive has 600 MB free space to
run XP], will it be the root cause of the problem?

Does you have any suggestions?
I look forward to your reply
Thank you very much for your suggestions
Eric

 

[Bill Manville]

Having two concurrent processes opening, changing and saving different
sets of files should not cause a problem. I was trying to think of
reasons why file>save has failed in the past and the virus checker one
was the only one I could think of. The temporary files are given
unique 8-character names so they shouldn't clash.

Low free space on disk could be significant - but it would be the disk
where the files were being saved to that I would look at. However, if
there is insufficient space for the workbook being saved, the result is
usually a "document not saved" message, not corruption to the workbook.

Is there any significant difference you can think of between workbooks
that are liable to corruption and workbooks that are not?

Bill Manville
MVP - Microsoft Excel, Oxford, England