Hi All,
I'm working on a small process improvement project at work and am running into a wall. The goal is to execute a macro that will take the first sheet of each workbook within a folder and print them as PDF files with the same filename as the original file, with the different extension.
I found some starting code on Stackoverflow that I adjusted for my needs, but when I run it, it gives me an error that it cannot find the first Excel file in the directory, even though the files are there. It's find the name of the file, but for some reason isn't able to capture it and open it.
I posted the code below. Any guidance would be appreciated!
Option Explicit
I'm working on a small process improvement project at work and am running into a wall. The goal is to execute a macro that will take the first sheet of each workbook within a folder and print them as PDF files with the same filename as the original file, with the different extension.
I found some starting code on Stackoverflow that I adjusted for my needs, but when I run it, it gives me an error that it cannot find the first Excel file in the directory, even though the files are there. It's find the name of the file, but for some reason isn't able to capture it and open it.
I posted the code below. Any guidance would be appreciated!
Option Explicit
VBA Code:
Option Explicit
Sub Excel_files_to_PDF()
Dim strWorkbook As String
Dim wbktoExport As Workbook
Dim wbkLocation As String
wbkLocation = "C:\Users\userName\Desktop\New folder\"
strWorkbook = Dir(wbkLocation & "*.xls*")
Do While Len(strWorkbook) > 0
wbktoExport = Workbooks.Open(strWorkbook)
Call Save_to_PDF(wbktoExport)
strWorkbook = Dir
wbktoExport.Close False
Loop
End Sub
Sub Save_to_PDF(ByRef wbk As Workbook)
Dim strTargetPDFLocation As String
strTargetPDFLocation = "C:\Users\userName\Desktop\New folder\"
wbk.Sheets(1).Select
wbk.Sheets(1).Activate
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strTargetPDFLocation & wbk.Name & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub