Data files are corrupt after saving from VBA - Excel 2000

[Raymond Allan]

Hi,

I am using Excel 2000

I have a routine which goes through around 20 sheets and moves each
sheet to a new workbook and then saves it, closes the workbook and
then deletes the sheet from the master workbook, this code works fine
but each resultant file crashes Excel when the user or I try to open
it.

I have changed settings in the options to save only as Excel 2000,
Excel 97 etc. but still the data files are corrupt

Using the same code in Excel 97 does not corrupt any of the saved
files, is this a bug in Excel 2000 !!

I even added a routine to check that the files were fully saved before
processing the next file.

Below is a snippet of the code I use

ActiveWorkbook.SaveAs filename:=ThisFolder & fName,
FileFormat:=xlNormal

'' DoEvents

'' File_Check

'' If fSavedOk = 0 Then File_Check

ActiveWorkbook.Close

'' ActiveWorkbook.Close SaveChanges:=false

Windows(MasterBook).Activate

Thanks
Raymond Allan

 

[Dave Peterson]

First, take a look at:
.SaveAsCopy
in VBA's help--don't destroy your 20 worksheets of work. You can always use
windows explorer to delete the old one and rename the new one if things went ok.

Just a guess about the corruption...Each version of excel seems to react
differently to various stages of corruption. Maybe the workbooks are just on
the cusp and xl97 is more forgiving (even though usually newer versions are more
forgiving than older versions).

I think I'd create a brand new workbook with 20 test worksheets and see if the
resulting files were corrupt. If no, maybe it's time to recreate the
troublesome workbook.

If yes, then I don't have a guess.

 

[Raymond Allan]

Hi Dave,

Thanks for your reply

Using the .SaveAsCopy does work, well kind of, providing I do the
following

From my master book, I have to move the sheet to a new workbook and
NOT save it then close the master book (there are no links in this
book and even if I manually create it, I still have to do the same)

Run the code which saves all the files

I then have to manually close all the open workbooks, if I use code to
close all the open books the files which were saved by the code WILL
NOT open in Excel

Must be a weird bug in Excel 2000

At least this is a kind of work around

Raymond

 

[Dave Peterson]

Did you try your existing code with a brand new test workbook?

If it works with that, I would be slightly concerned (well, greatly concerned)
that it was the original workbook that was causing the errors.

_If_ that original workbook is really the problem, you may want to start working

 

[Raymond Allan]

It was a brand new workbook that I created

I tried this out on a few other machines with Excel 2000 with the same
results.

My original code works fine on machines with Excel 97 and Excel XP

Strangest thing of all is I have similar programs that extracts data
into a seperate sheet, moves the sheet to a new workbook, saves it,
then e-mails it out to suppliers then closes the workbook, ALL
workbooks open fine in any version of Excel, my code is exactly the
same with the exception of the mailing routine

Thanks for taking an interest in this as i have been pulling my hair
out for days

Raymond

 

[Dave Peterson]

I always thought that newer versions of excel were able to deal with problem
workbooks better--until I read some posts that suggested trying to open a
workbook that xl2002 couldn't open in xl2k.

I guess that each version of excel is sensitive to problem workbooks in its own
way.

I'm not sure what the problem is (or what the solution is), but I think I'd keep
looking--for what I'm not sure??????

 

[Raymond Allan]

Thanks for looking at this Dave, I have gave up and got my admin to
re-install Excel 97 on my machine (where there is no problem whatsover
with the code as is the same on Excel Xp), as this is an integral part
of what i do in my job

Raymond

 

[Dave Peterson]

Glad you got it working.

Thanks for looking at this Dave, I have gave up and got my admin to
re-install Excel 97 on my machine (where there is no problem whatsover
with the code as is the same on Excel Xp), as this is an integral part
of what i do in my job

Raymond