"Unable to read file" after using SaveCopyAs

[D.Farns]

I built a simple "backup" module within an existing Excel file. The backup
module simply makes a copy of the open workbook using the SaveCopyAs method.
The backup module runs fine. The problem is that after the SaveCopyAs method
is run, I save and close the original workbook. The next time I try to open
the original workbook, I get "Unable to read file" after choosing to enable
macros.
here's the sequence of events:

open original workbook (after having run SaveCopyAs method in VBA when open
previously)
choose enable macros
"unable to read file" error occurs
choose enable macros again (not sure why prompted a 2nd time)
workbook opens with a message saying that repairs have been made
repairs consist of two pivot tables being removed due to corruption

This Excel workbook has been functioning fine for some time until this
backup module was created & run. Running Excel 2003 SP1 on WinXP. Maybe
something in my ThisWorkbook.Open event is triggering although nothing has
changed there? BTW, the backup copy created by SaveCopyAs opens fine, no
errors/repairs.

Any thoughts/suggestions? I've searched both this site and MS KB.

thanks
Dfarns

 

[D.Farns]

addition to original question......
"Unable to read file" error still occurs when chose to disable macros so VBA
in ThisWorkbook.Open could not be the culprit as mentioned at the end of
original question..

 

[D.Farns]

UPDATE: Problem isolated to when "pivotCache.refresh" is run in another
module, then the SaveAsCopy method is run at some point during the same open
session of the workbook.

so... can open workbook, run SaveAsCopy, save & close and workbook opens fine

If open workbook, run module containing PivotCache.refresh then SaveAsCopy
(in either order), save & close. "Unable to read file" occurs when workbook
opened and the pivot table that was refreshed has been removed by the Excel
Repair process.

module containing PivotCache.refresh has always worked, not interfered with
anything else nor has it been changed. Something appears to be happening to
the pivot table when SaveAsCopy is run with the PivotCache recently
refreshed.

Now I really need some HELP!

 

[Elise]

UPDATE: Problem isolated to when "pivotCache.refresh" is run in another
module, then the SaveAsCopy method is run at some point during the same open
session of the workbook.

so... can open workbook, run SaveAsCopy, save & close and workbook opens fine

If open workbook, run module containing PivotCache.refresh then SaveAsCopy
(in either order), save & close. "Unable to read file" occurs when workbook
opened and the pivot table that was refreshed has been removed by the Excel
Repair process.

module containing PivotCache.refresh has always worked, not interfered with
anything else nor has it been changed. Something appears to be happening to
the pivot table when SaveAsCopy is run with the PivotCache recently
refreshed.

Now I really need some HELP!

 

[Elise]

UPDATE: Problem isolated to when "pivotCache.refresh" is run in another
module, then the SaveAsCopy method is run at some point during the same open
session of the workbook.

so... can open workbook, run SaveAsCopy, save & close and workbook opens fine

If open workbook, run module containing PivotCache.refresh then SaveAsCopy
(in either order), save & close. "Unable to read file" occurs when workbook
opened and the pivot table that was refreshed has been removed by the Excel
Repair process.

module containing PivotCache.refresh has always worked, not interfered with
anything else nor has it been changed. Something appears to be happening to
the pivot table when SaveAsCopy is run with the PivotCache recently
refreshed.

Now I really need some HELP!

I came accross a similar problem just recently in spreadsheets that I have
been using for about 2 years with no problem. Each month I save last months
spreadsheet as the new month and then add the new month's data and pivot
table. Last month it allowed me to add the pivot table and make various
changes to the new spreadsheet but when I closed it and opened it the next
day it came up with the message "unable to read file". Excel then repaired
the file by removing all the pivot tables and replacing them with data only.
I then rebuilt all the pivot tables saved the file closed it and then opened
it again. The error returned. I find now that I can not have even one pivot
table in the file as the error will still occur so now all I can do is
rebuild the pivot table each time I need them and then save them as data
only.
I hope someone has some ideas as to why this is happening. Maybe it is a
memory problem or maybe it is a problem in a recent update. Need Help too.

 

[D.Farns]

- ERROR RESOLVED -

The "Unable to read file" error was being generated by a combination of the
workbook being protected, a certain pivot table property and the SaveCopyAs
method. I still don't understand how the SaveCopyAs method invokes this
situation whereas my pivot tables properties have not changed and the
workbook has always been protected without any issues. Nevertheless it does
and here's how I resolved the error.

~ right click pivot table and choose "Table Options"
~ UNcheck the box under the "Data Options" section labeled "Save Data with
Table Layout", click OK
~ repeat these steps for all pivot tables in the workbook.
~ save and close the workbook

now you should be able to open the workbook (with workbook protection on),
update pivot tables and run SaveAsCopy at will during the same open session
and not receive the "unable to read file" error the next time you open the
workbook.

The other option is to shut off workbook protection, or at least toggle it
off before close so it's off when the workbook is opened again. I chose not
to do this because I absolutely need my workbook protected and if you
unprotect the book in the BeforeClose event and the user cancels when asked
if they want to save changes, then the user remains in the workbook and the
workbook unprotect has already run. No easy way to trigger the protection
back on again. Much easier to shut off the Save Date With Table Layout
property on the pivot tables and leave protection alone.

hope this helps.