Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving worksheets without Auto_Open
Here is the deal...
I have a worksheet (a.xls) that when opened, pulls in certain dat using the Auto_Open macro. I then want to save that worksheet as b.xl without Auto_Open macro. Is there a way to do this? Any help would really be appreciated. I'v been beating my head against the monitor try to figure it out. Thanks, Bowbende -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving worksheets without Auto_Open
Hi
you may put some code in the workbook_beforesave event to check if the Auto_Open macro exists and if yes delete this procedure. See: http://www.cpearson.com/excel/vbe.htm -- Regards Frank Kabel Frankfurt, Germany Here is the deal... I have a worksheet (a.xls) that when opened, pulls in certain data using the Auto_Open macro. I then want to save that worksheet as b.xls without Auto_Open macro. Is there a way to do this? Any help would really be appreciated. I've been beating my head against the monitor try to figure it out. Thanks, Bowbender --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving worksheets without Auto_Open
If I can delete the macro Auto_Open after it executes that would b
great. I just don't know how to go about doing it. I tried to inser this... "Sub DeleteModule() Dim VBComp As VBComponent Set VBComp = ThisWorkbook.VBProject.VBComponents("Auto_Open") ThisWorkbook.VBProject.VBComponents.Remove VBComp End Sub" But recieved an error on line two. Something about user defined. I obviously am not very good at this stuff and would appreciate an help. My code is below. I am sure it is bloated and full of useless thing but I hope it helps. Thanks Sub Auto_Open() ' ' ' ' ' ChDir "C:\" Workbooks.Open Filename:="C:\Header.csv" Range("A1:E2").Select Selection.Copy ActiveWindow.Close Range("A8").Select ActiveSheet.Paste Workbooks.Open Filename:="C:\case.csv" Range("A1:C100").Select Selection.Copy ActiveWindow.WindowState = xlMinimized Range("A14").Select ActiveSheet.Paste ActiveWindow.WindowState = xlMinimized Windows("case.csv").Activate ActiveWindow.WindowState = xlNormal Application.CutCopyMode = False Application.CommandBars("Task Pane").Visible = False ActiveWindow.Close ActiveWindow.WindowState = xlMaximized Range("A14:D14").Select Selection.Font.Bold = True With Selection.Font .Name = "Arial" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Range("A8:E8").Select Selection.Font.Bold = True With Selection.Font .Name = "Arial" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("D:D").EntireColumn.AutoFit Columns("E:E").EntireColumn.AutoFit Range("E14").Select ActiveSheet.Pictures.Insert("C:\image.png").Select ActiveWindow.SmallScroll Down:=14 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 5 Selection.ShapeRange.ScaleWidth 0.35, msoFalse msoScaleFromTopLeft Selection.ShapeRange.ScaleHeight 0.35, msoFalse msoScaleFromTopLeft ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 ActiveWindow.SmallScroll Down:=-7 Selection.ShapeRange.ScaleHeight 1.24, msoFalse msoScaleFromTopLeft Selection.ShapeRange.ScaleWidth 1.14, msoFalse msoScaleFromTopLeft Selection.ShapeRange.ScaleHeight 1.09, msoFalse msoScaleFromTopLeft ActiveWindow.ScrollRow = 9 ActiveWindow.ScrollRow = 10 ActiveWindow.ScrollRow = 11 ActiveWindow.ScrollRow = 12 ActiveWindow.ScrollRow = 13 Selection.ShapeRange.ScaleWidth 1.21, msoFalse msoScaleFromBottomRight Selection.ShapeRange.ScaleHeight 1.21, msoFalse msoScaleFromTopLeft Selection.ShapeRange.ScaleWidth 0.93, msoFalse msoScaleFromBottomRight Selection.ShapeRange.ScaleHeight 0.93, msoFalse msoScaleFromTopLeft Range("D32").Select ActiveSheet.Pictures.Insert("C:\chart.wmf").Select ActiveWindow.SmallScroll Down:=14 Selection.ShapeRange.ScaleHeight 0.69, msoFalse msoScaleFromTopLeft Selection.ShapeRange.ScaleWidth 1.1, msoFalse, msoScaleFromTopLeft Selection.ShapeRange.ScaleWidth 1.01, msoFalse msoScaleFromTopLeft Selection.ShapeRange.ScaleWidth 0.93, msoFalse msoScaleFromBottomRight Selection.ShapeRange.ScaleHeight 1.03, msoFalse msoScaleFromTopLeft Selection.ShapeRange.ScaleHeight 1.06, msoFalse msoScaleFromBottomRight Selection.ShapeRange.ScaleWidth 0.86, msoFalse msoScaleFromTopLeft Selection.ShapeRange.ScaleHeight 0.88, msoFalse msoScaleFromTopLeft End Su -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving worksheets without Auto_Open
In VBA, go to the Tools menu, choose References, and select
"Microsoft Visual Basic For Application Extensibility Library". Then, use code like the following: Dim StartLine As Long Dim LineCount As Long Dim CodeMod As VBIDE.CodeModule Set CodeMod = ThisWorkbook.VBProject.VBComponents("Module1").Cod eModule StartLine = CodeMod.ProcBodyLine("Auto_Open", vbext_pk_Proc) LineCount = CodeMod.ProcCountLines("Auto_Open", vbext_pk_Proc) CodeMod.DeleteLines StartLine, LineCount Change the "Module1" to the name of the code module which contains the Auto_Open macro. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Bowbender " wrote in message ... If I can delete the macro Auto_Open after it executes that would be great. I just don't know how to go about doing it. I tried to insert this... "Sub DeleteModule() Dim VBComp As VBComponent Set VBComp = ThisWorkbook.VBProject.VBComponents("Auto_Open") ThisWorkbook.VBProject.VBComponents.Remove VBComp End Sub" But recieved an error on line two. Something about user defined. I obviously am not very good at this stuff and would appreciate any help. My code is below. I am sure it is bloated and full of useless things but I hope it helps. Thanks Sub Auto_Open() ' ' ' ' ' ChDir "C:\" Workbooks.Open Filename:="C:\Header.csv" Range("A1:E2").Select Selection.Copy ActiveWindow.Close Range("A8").Select ActiveSheet.Paste Workbooks.Open Filename:="C:\case.csv" Range("A1:C100").Select Selection.Copy ActiveWindow.WindowState = xlMinimized Range("A14").Select ActiveSheet.Paste ActiveWindow.WindowState = xlMinimized Windows("case.csv").Activate ActiveWindow.WindowState = xlNormal Application.CutCopyMode = False Application.CommandBars("Task Pane").Visible = False ActiveWindow.Close ActiveWindow.WindowState = xlMaximized Range("A14:D14").Select Selection.Font.Bold = True With Selection.Font Name = "Arial" Size = 10 Strikethrough = False Superscript = False Subscript = False OutlineFont = False Shadow = False Underline = xlUnderlineStyleNone ColorIndex = xlAutomatic End With Range("A8:E8").Select Selection.Font.Bold = True With Selection.Font Name = "Arial" Size = 10 Strikethrough = False Superscript = False Subscript = False OutlineFont = False Shadow = False Underline = xlUnderlineStyleNone ColorIndex = xlAutomatic End With Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Columns("C:C").EntireColumn.AutoFit Columns("D:D").EntireColumn.AutoFit Columns("E:E").EntireColumn.AutoFit Range("E14").Select ActiveSheet.Pictures.Insert("C:\image.png").Select ActiveWindow.SmallScroll Down:=14 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 5 Selection.ShapeRange.ScaleWidth 0.35, msoFalse, msoScaleFromTopLeft Selection.ShapeRange.ScaleHeight 0.35, msoFalse, msoScaleFromTopLeft ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 ActiveWindow.SmallScroll Down:=-7 Selection.ShapeRange.ScaleHeight 1.24, msoFalse, msoScaleFromTopLeft Selection.ShapeRange.ScaleWidth 1.14, msoFalse, msoScaleFromTopLeft Selection.ShapeRange.ScaleHeight 1.09, msoFalse, msoScaleFromTopLeft ActiveWindow.ScrollRow = 9 ActiveWindow.ScrollRow = 10 ActiveWindow.ScrollRow = 11 ActiveWindow.ScrollRow = 12 ActiveWindow.ScrollRow = 13 Selection.ShapeRange.ScaleWidth 1.21, msoFalse, msoScaleFromBottomRight Selection.ShapeRange.ScaleHeight 1.21, msoFalse, msoScaleFromTopLeft Selection.ShapeRange.ScaleWidth 0.93, msoFalse, msoScaleFromBottomRight Selection.ShapeRange.ScaleHeight 0.93, msoFalse, msoScaleFromTopLeft Range("D32").Select ActiveSheet.Pictures.Insert("C:\chart.wmf").Select ActiveWindow.SmallScroll Down:=14 Selection.ShapeRange.ScaleHeight 0.69, msoFalse, msoScaleFromTopLeft Selection.ShapeRange.ScaleWidth 1.1, msoFalse, msoScaleFromTopLeft Selection.ShapeRange.ScaleWidth 1.01, msoFalse, msoScaleFromTopLeft Selection.ShapeRange.ScaleWidth 0.93, msoFalse, msoScaleFromBottomRight Selection.ShapeRange.ScaleHeight 1.03, msoFalse, msoScaleFromTopLeft Selection.ShapeRange.ScaleHeight 1.06, msoFalse, msoScaleFromBottomRight Selection.ShapeRange.ScaleWidth 0.86, msoFalse, msoScaleFromTopLeft Selection.ShapeRange.ScaleHeight 0.88, msoFalse, msoScaleFromTopLeft End Sub --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving worksheets without Auto_Open
Chip, your the man!!!
Thanks for helping this newbie out. You have saved me many hours o pain I am sure. Thanks again, Bowbender Chip Pearson wrote: [b]In VBA, go to the Tools menu, choose References, and select "Microsoft Visual Basic For Application Extensibility Library". Then, use code like the following: Dim StartLine As Long Dim LineCount As Long Dim CodeMod As VBIDE.CodeModule Set CodeMod = ThisWorkbook.VBProject.VBComponents("Module1").Cod eModule StartLine = CodeMod.ProcBodyLine("Auto_Open", vbext_pk_Proc) LineCount = CodeMod.ProcCountLines("Auto_Open", vbext_pk_Proc) CodeMod.DeleteLines StartLine, LineCount Change the "Module1" to the name of the code module which contains the Auto_Open macro. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Saving a File of worksheets | New Users to Excel | |||
saving training worksheets | Excel Discussion (Misc queries) | |||
Saving Multiple Worksheets | Excel Discussion (Misc queries) | |||
Saving worksheets using a Macro | Excel Discussion (Misc queries) | |||
Saving worksheets | Excel Worksheet Functions |