Adding file name and path to to footer of all workbooks in a folder.

M

Muxer

Hello,

Can anyone show me some examples of how to add the file name and path of
workbook to the footer. I would like to do this to all *.xls files in a
folder.

Since at times there is going to be hundreds of files, is it fastest
and/or easier to do this via an add-in, macro or from VB6?

Regards,
LRR
 
F

Frank Kabel

Hi
for one file: put the following code in your workbook module (not in a
standard
module):

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
For Each wkSht In Me.Worksheets
With wkSht.PageSetup
.CenterFooter = Me.FullName
End With
Next wkSht
End Sub
 
R

Ron de Bruin

Look here

http://www.j-walk.com/ss/excel/tips/tip22.htm

You can run this macro to change all files in the folder C:\Data

Sub test()
Dim mybook As Workbook
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
MyPath = "C:\Data"
ChDrive MyPath
ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
Do While FNames <> ""
Set mybook = Workbooks.Open(FNames)

For Each sht In mybook.Sheets
sht.PageSetup.LeftFooter = mybook.FullName
Next sht

mybook.Close True
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub
 
Top