Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate adding code
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. -- Marsh |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate adding code
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 -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate adding code
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? -- Marsh Andoni wrote: 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 Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate adding code
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 --- Regards, Norman "Marshall Barton" wrote in message ... 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? -- Marsh Andoni wrote: 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 Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automate adding code
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. -- Marsh Norman Jones wrote: 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 --- Regards, Norman "Marshall Barton" wrote in message .. . 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? -- Marsh Andoni wrote: 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 Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automate adding sheets | Excel Discussion (Misc queries) | |||
Excel---how do i automate adding a suffix to text? | Excel Discussion (Misc queries) | |||
use VB code IF to automate filling in 11 columns | Excel Discussion (Misc queries) | |||
Automate this code | Excel Programming | |||
Way to automate adding pictures in a series in Excel? | Excel Programming |