| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disassociate Macros when saving under different name
I have a macro which removes some sheets from a workbook, removes shapes
which run macros and saves the file under a new file name. The macro runs and appears to do what it should but when I try to open the newly saved file I see the front sheet but get an error message saying Excel.exe has generated errors and will be closed by Windows. I wondered if the solution would be to remove all the macros in This Workbook (that's the only location) before the file save as process in case the Auto_Open is causing a glitch. If so, how do I do this? I'm running Office 2000 on Windows 2000. I've put the code below to help make it clearer. I'd appreciate any help with this as I've been stuck for hours. Thanks a lot in anticipation. Keith ---------- ActiveWindow.DisplayWorkbookTabs = True Application.DisplayAlerts = False Sheets("Menu").Select ActiveWindow.SelectedSheets.Delete Sheets("Workings").Select ActiveWindow.SelectedSheets.Delete Application.DisplayAlerts = True Sheets("Month by Month Analysis").Select ActiveSheet.Shapes("AutoShape 1").Select Selection.Cut Sheets("Branch Analysis").Select ActiveSheet.Shapes("AutoShape 1").Select Selection.Cut Sheets("Signup Report").Select ActiveSheet.Shapes("AutoShape 1").Select Selection.Cut Sheets("Banked Report").Select ActiveSheet.Shapes("AutoShape 1").Select Selection.Cut Sheets("Incentives").Select ActiveSheet.Shapes("AutoShape 1").Select Selection.Cut Sheets("Data").Select ActiveSheet.Shapes("AutoShape 12").Select Selection.Cut Sheets("Month by Month Analysis").Select Range("A1").Select ThisWorkbook.SaveAs "c:\#Reports\Reports\Reports " & Format(Date, "dd-mm-yyyy") End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Disassociate Macros when saving under different name
If you think it maybe be you're auto_open macro causing trouble, put code in
it to exit the sub if certain sheets or shapes are missing. For instance if sheet1. runs code but is removed with your save macro, on auto_open check for sheet1 If it is not there, exit sub "nospaminlich" wrote: I have a macro which removes some sheets from a workbook, removes shapes which run macros and saves the file under a new file name. The macro runs and appears to do what it should but when I try to open the newly saved file I see the front sheet but get an error message saying Excel.exe has generated errors and will be closed by Windows. I wondered if the solution would be to remove all the macros in This Workbook (that's the only location) before the file save as process in case the Auto_Open is causing a glitch. If so, how do I do this? I'm running Office 2000 on Windows 2000. I've put the code below to help make it clearer. I'd appreciate any help with this as I've been stuck for hours. Thanks a lot in anticipation. Keith ---------- ActiveWindow.DisplayWorkbookTabs = True Application.DisplayAlerts = False Sheets("Menu").Select ActiveWindow.SelectedSheets.Delete Sheets("Workings").Select ActiveWindow.SelectedSheets.Delete Application.DisplayAlerts = True Sheets("Month by Month Analysis").Select ActiveSheet.Shapes("AutoShape 1").Select Selection.Cut Sheets("Branch Analysis").Select ActiveSheet.Shapes("AutoShape 1").Select Selection.Cut Sheets("Signup Report").Select ActiveSheet.Shapes("AutoShape 1").Select Selection.Cut Sheets("Banked Report").Select ActiveSheet.Shapes("AutoShape 1").Select Selection.Cut Sheets("Incentives").Select ActiveSheet.Shapes("AutoShape 1").Select Selection.Cut Sheets("Data").Select ActiveSheet.Shapes("AutoShape 12").Select Selection.Cut Sheets("Month by Month Analysis").Select Range("A1").Select ThisWorkbook.SaveAs "c:\#Reports\Reports\Reports " & Format(Date, "dd-mm-yyyy") End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Disassociate Macros when saving under different name
You can open excel in save mode. Go to the run command under the start menu
and type excel.exe /s and then try to open your files. You can also hold down the shift key while you open the file to disable macros (or change the security setting so you are prompted to disable macros). If you still have problems with the files, then removing the macro is probably not going to rectify you problem. -- Regards, Tom Ogilvy "nospaminlich" wrote in message ... I have a macro which removes some sheets from a workbook, removes shapes which run macros and saves the file under a new file name. The macro runs and appears to do what it should but when I try to open the newly saved file I see the front sheet but get an error message saying Excel.exe has generated errors and will be closed by Windows. I wondered if the solution would be to remove all the macros in This Workbook (that's the only location) before the file save as process in case the Auto_Open is causing a glitch. If so, how do I do this? I'm running Office 2000 on Windows 2000. I've put the code below to help make it clearer. I'd appreciate any help with this as I've been stuck for hours. Thanks a lot in anticipation. Keith ---------- ActiveWindow.DisplayWorkbookTabs = True Application.DisplayAlerts = False Sheets("Menu").Select ActiveWindow.SelectedSheets.Delete Sheets("Workings").Select ActiveWindow.SelectedSheets.Delete Application.DisplayAlerts = True Sheets("Month by Month Analysis").Select ActiveSheet.Shapes("AutoShape 1").Select Selection.Cut Sheets("Branch Analysis").Select ActiveSheet.Shapes("AutoShape 1").Select Selection.Cut Sheets("Signup Report").Select ActiveSheet.Shapes("AutoShape 1").Select Selection.Cut Sheets("Banked Report").Select ActiveSheet.Shapes("AutoShape 1").Select Selection.Cut Sheets("Incentives").Select ActiveSheet.Shapes("AutoShape 1").Select Selection.Cut Sheets("Data").Select ActiveSheet.Shapes("AutoShape 12").Select Selection.Cut Sheets("Month by Month Analysis").Select Range("A1").Select ThisWorkbook.SaveAs "c:\#Reports\Reports\Reports " & Format(Date, "dd-mm-yyyy") End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Disassociate Macros when saving under different name
hi,
have a look at this site. it helped me. http://www.cpearson.com/excel/vbe.htm be careful not to delete the macro that is deleted all the macros. -----Original Message----- I have a macro which removes some sheets from a workbook, removes shapes which run macros and saves the file under a new file name. The macro runs and appears to do what it should but when I try to open the newly saved file I see the front sheet but get an error message saying Excel.exe has generated errors and will be closed by Windows. I wondered if the solution would be to remove all the macros in This Workbook (that's the only location) before the file save as process in case the Auto_Open is causing a glitch. If so, how do I do this? I'm running Office 2000 on Windows 2000. I've put the code below to help make it clearer. I'd appreciate any help with this as I've been stuck for hours. Thanks a lot in anticipation. Keith ---------- ActiveWindow.DisplayWorkbookTabs = True Application.DisplayAlerts = False Sheets("Menu").Select ActiveWindow.SelectedSheets.Delete Sheets("Workings").Select ActiveWindow.SelectedSheets.Delete Application.DisplayAlerts = True Sheets("Month by Month Analysis").Select ActiveSheet.Shapes("AutoShape 1").Select Selection.Cut Sheets("Branch Analysis").Select ActiveSheet.Shapes("AutoShape 1").Select Selection.Cut Sheets("Signup Report").Select ActiveSheet.Shapes("AutoShape 1").Select Selection.Cut Sheets("Banked Report").Select ActiveSheet.Shapes("AutoShape 1").Select Selection.Cut Sheets("Incentives").Select ActiveSheet.Shapes("AutoShape 1").Select Selection.Cut Sheets("Data").Select ActiveSheet.Shapes("AutoShape 12").Select Selection.Cut Sheets("Month by Month Analysis").Select Range("A1").Select ThisWorkbook.SaveAs "c:\#Reports\Reports\Reports " & Format(Date, "dd-mm-yyyy") End Sub . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Disassociate Macros when saving under different name
Thanks a lot for the replies.
I've at least now proved that by disabling macros the file still won't open. If I maually save the file without without removing shapes and sheets it's fine but that leaves users able to run macros in the copy version which I don't want. Any ideas what it could be in the code I posted above that is causing the saved file to corrupt? Is there a more reliable way of writing the code? Thanks again Keith |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Disassociate Macros when saving under different name
Possibly put the code in an addin. This separates the code from the
workbook. -- Regards, Tom Ogilvy "nospaminlich" wrote in message ... Thanks a lot for the replies. I've at least now proved that by disabling macros the file still won't open. If I maually save the file without without removing shapes and sheets it's fine but that leaves users able to run macros in the copy version which I don't want. Any ideas what it could be in the code I posted above that is causing the saved file to corrupt? Is there a more reliable way of writing the code? Thanks again Keith |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Saving Macros | Excel Discussion (Misc queries) | |||
| Disassociate existing macro from control key and execute by botton | Excel Discussion (Misc queries) | |||
| Saving Macros | Excel Discussion (Misc queries) | |||
| weird saving of a document with macros resulting with macros being transfered to the copy | Excel Programming | |||
| Saving Macros | Excel Worksheet Functions | |||
Linear Mode
