Hi,
Every month, I create many reports in excel for external use. I need to print all those files in PDF using a vba code. I am using the code below. But I need to be able to automate the save as ( same name as excel file).
Can anyone assist? Thanks.
Sub PrintWorkbooksintoPDF()
PrintAllWorkbooksInFolder "C:\Users\username\Desktop\Production", "*.xlsx"
End Sub
Sub PrintAllWorkbooksInFolder(TargetFolder As String, FileFilter As String)
Dim fn As String
Dim wb As Workbook
Application.ScreenUpdating = False
TargetFolder = "C:\Users\username\Desktop\Production"
If Right(TargetFolder, 1) <> Application.PathSeparator Then
TargetFolder = TargetFolder & Application.PathSeparator
End If
If FileFilter = "" Then FileFilter = "*.xls"
fn = Dir(TargetFolder & FileFilter)
While Len(fn) > 0
If fn <> ThisWorkbook.Name Then
Application.StatusBar = "Printing " & fn & "..."
Workbooks.Open TargetFolder & fn, UpdateLinks:=False
ActiveWorkbook.PrintOut Preview:=False, ActivePrinter:="Adobe PDF", PrintToFile:=True, Collate:=True, PrToFileName:="", IgnorePrintAreas:=True
Application.SendKeys "~", True
ActiveWorkbook.Close False
End If
fn = Dir
Wend
Application.StatusBar = False
End Sub