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

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/

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

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/

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