Saving worksheets without Auto_Open

  • Thread starter Thread starter Bowbender
  • Start date Start date
B

Bowbender

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
 
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
 
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").CodeModule
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
 
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 said:
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").CodeModule
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
Click to expand...
 
Back
Top