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..
> 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.
[quoted text clipped - 22 lines]
> thanks
> Dfarns
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!

Signature
D.Farns
> addition to original question......
> "Unable to read file" error still occurs when chose to disable macros so VBA
[quoted text clipped - 27 lines]
> > thanks
> > Dfarns
Elise - 27 Nov 2006 23:29 GMT
> 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
[quoted text clipped - 45 lines]
> > > thanks
> > > Dfarns
Elise - 27 Nov 2006 23:45 GMT
> 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
[quoted text clipped - 45 lines]
> > > thanks
> > > Dfarns
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 - 28 Nov 2006 14:42 GMT
- 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.

Signature
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
[quoted text clipped - 60 lines]
> 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.