Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data files are corrupt after saving from VBA - Excel 2000
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data files are corrupt after saving from VBA - Excel 2000
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 wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data files are corrupt after saving from VBA - Excel 2000
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 wrote in message ... 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 wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data files are corrupt after saving from VBA - Excel 2000
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 on recreating it before it's so bad that you can't open it. Raymond Allan wrote: 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 wrote in message ... 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 wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data files are corrupt after saving from VBA - Excel 2000
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 wrote in message ... 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 on recreating it before it's so bad that you can't open it. Raymond Allan wrote: 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 wrote in message ... 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 wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data files are corrupt after saving from VBA - Excel 2000
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 wrote: 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 wrote in message ... 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 on recreating it before it's so bad that you can't open it. Raymond Allan wrote: 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 wrote in message ... 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 wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data files are corrupt after saving from VBA - Excel 2000
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 wrote in message ... 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 wrote: 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 wrote in message ... 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 on recreating it before it's so bad that you can't open it. Raymond Allan wrote: 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 wrote in message ... 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 wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data files are corrupt after saving from VBA - Excel 2000
Glad you got it working.
Raymond Allan wrote: 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 wrote in message ... 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 wrote: 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 wrote in message ... 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 on recreating it before it's so bad that you can't open it. Raymond Allan wrote: 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 wrote in message ... 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 wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Programmatically determining corrupt excel files | Excel Discussion (Misc queries) | |||
Keep saving corrupt Excel files | Excel Discussion (Misc queries) | |||
corrupt Excel 2000/2002 files | Excel Discussion (Misc queries) | |||
VBA for Excel 2000 file is corrupt | Excel Discussion (Misc queries) | |||
publishing html using excel - problem with files becoming corrupt | Excel Programming |