Workbooks in a folder to PDF

Aggie2014

New Member
Joined
Dec 26, 2019
Messages
35
Office Version
  1. 2016
Platform
  1. Windows
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



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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,466
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:
VBA Code:
Set wbktoExport = Workbooks.Open(wbkLocation & strWorkbook)
 

Aggie2014

New Member
Joined
Dec 26, 2019
Messages
35
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,466
Try inserting Application.DisplayAlerts = False before the loop and Application.DisplayAlerts = True after it.
 

Aggie2014

New Member
Joined
Dec 26, 2019
Messages
35
Office Version
  1. 2016
Platform
  1. Windows
Perfection. Really appreciate the help! This will save us hours of work..
 

Watch MrExcel Video

Forum statistics

Threads
1,114,314
Messages
5,547,154
Members
410,775
Latest member
alal1030
Top