PDA

View Full Version : [SOLVED] Remove Header / Footer from files in folder



branston
12-30-2019, 12:26 PM
Hi

I am trying to loop through some pdf files in a folder to remove the incorrect header / footer information that was placed there. Ideally I would like whatever is there to be replaced by the correct information (but haven't worked out how to replace the info. yet)

When I run the code below (thanks to Hans for the help), the pdf files can no longer be opened and I get the message "the file cannot be opened ... it may be corrupt" etc.

Can anyone help?



Sub RemoveHeaderFooter()
Dim strFolder As String
Dim strFile As String
Dim wbk As Workbook
Dim wsh As Worksheet
' Prompt for a folder
With Application.FileDialog(4) ' msoFileDialogFolderPicker
If .Show Then
strFolder = .SelectedItems(1)
If Right(strFolder, 1) <> "\" Then
strFolder = strFolder & "\"
End If
Else
MsgBox "You haven't specified a folder!", vbExclamation
Exit Sub
End If
End With
Application.ScreenUpdating = False
' Get the first filename
strFile = Dir(strFolder & "*.pdf*")
' Loop
Do While strFile <> ""
' Open workbook
Set wbk = Workbooks.Open(strFolder & strFile)
' Clear headers and footers
For Each wsh In wbk.Worksheets
With wsh.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
End With
Next wsh
' Save and close workbook
wbk.Close SaveChanges:=True
' Get next filename
strFile = Dir
Loop
Application.ScreenUpdating = True
End Sub

gmayor
12-30-2019, 10:00 PM
If you add the line End to stop the process after the first file is opened, the problem becomes immediately obvious.
Set wbk = Workbooks.Open(strFolder & strFile)
EndExcel is not compatible with PDF format. You cannot open a PDF file in Excel in order to edit it.

branston
12-31-2019, 09:07 AM
Thanks gmayor. Back to the drawing board.