Automate adding code

[Marshall Barton]

I have a situation where I need to add a BeforePrint event
procedure in hundreds of Excel2000 workbooks.

(From a KB article:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.LeftFooter = ThisWorkbook.FullName
End Sub
)

I need some pointers into help so I can find the
objects/methods that I can use to automate inserting those
lines of code into each .xls file. In Access, it's the
Module object and its InsertLines method, but I can't seem
to find the analogous items in Excel.

 

[Andoni]

This may look a primitive way, but it will work!


Sub TryThis()
Dim Filename As Variant
Dim X As Long
Dim Wkb As Workbook
Dim Sht As Object
'Gets the File name
Filename = Application.GetOpenFilename( _
FileFilter:="Excel Files (*.Xls),*.Xls", _
Title:="Chose your Excel File/s!", _
MultiSelect:=True)

Application.ScreenUpdating = False
'Exits if you cancel
If Not IsArray(Filename) Then
MsgBox "No File/s where selected"
Exit Sub
End If
For X = LBound(Filename) To UBound(Filename)
Set Wkb = Workbooks.Open(Filename:=Filename(X))
For Each Sht In ActiveWorkbook.Sheets
Sht.PageSetup.LeftHeader = ThisWorkbook.FullName
Next Sht
Wkb.Save
Wkb.Close
Next
End Su

 

[Marshall Barton]

Thanks for the idea, I guess I hadn't thought of setting
every sheet's property, I'll have to check to see if that's
acceptable. It might not be because it would require all
future sheets to be set manually. Hmmm, It would have to
be done manually for new workbooks anyway . . . thinking ...

Since you didn't mention anything about automating the code
insertion process, should I conclude that Excel doesn't
provide a mechanism to do that?

 

[Norman Jones]

Hi Marshall,

Since you didn't mention anything about automating the code
insertion process, should I conclude that Excel doesn't
provide a mechanism to do that?

Go to Chip Pearson's web site and see:

http://www.cpearson.com/excel/vbe.htm

 

[Marshall Barton]

Thanks for the link Norman, Chip's article is outstanding.

His reminder to use the Extensibility library was the real
key, but what a relief that his article made the Help file
unnecessary.

The code insertion stuff is now working and all I have to do
is figure out if that's really the way I want to pursue
this.