Workbooks in a folder to PDF

[Aggie2014]

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

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

 

[John_w]

wbktoExport = Workbooks.Open(strWorkbook)

A common mistake - Dir returns just the file name, without the folder path. You also need to Set the Workbook object:

Set wbktoExport = Workbooks.Open(wbkLocation & strWorkbook)

 

[Aggie2014]

That worked perfectly. Thanks!

Another issue I'm running into is that on some of the workbooks there are links and when they're opened, Excel alerts the "We can't update some of the links in the workbook right now" message. Is there a way to set a parameter within the VBA to continue automatically?

 

[John_w]

Try inserting Application.DisplayAlerts = False before the loop and Application.DisplayAlerts = True after it.

 

[Aggie2014]

Perfection. Really appreciate the help! This will save us hours of work..