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/