Excel print worksheets to PDF from closed file

Question

I have a main Excel file that contains info for a project. There will always also be another excel file named "Job export" in the same folder as my main file containing drawings - each drawing on separate sheet. Drawings will start from sheet 2 and end at sheet n-1 (currently the code will also print sheet 1 and sheet n).

I have found and modified VBA code that will print these drawings from each sheet into separate PDF files with the name in Cell C5 in each sheet. But this means I have to open the excel file with drawings and copy the code to that workbook just to print the PDFs. I would like to add this code to my main Excel so that it opens the "Job export" excel in background and print drawings into separate PDFs.

Further step would be to save these PDFs to folders according to the client's name in Cell B7 and part number in B11 and onwards (in this case the saved PDF would also be named after the part number).

Is this possible with VBA?

Edit:

I'm stuck with chaning the save location of printed PDFs. I tried to just hard-code in the folder "C:\suvaline" but it won't save them there. As I wrote previously, the save folder should be: C:\suvaline\"Clients name"\"Part number" but it won't even work with the hard-coded one so I haven't even tried to get the clients name from Excel. Is there something I'm missing?

Sub ExportToPDFFromClosed()

Dim ws As Worksheet
Dim wbA As Workbook
Dim strPath
Dim strFile
Dim wb2 As Workbook

i = 11
j = 2

sPath = "C:\suvaline"
Application.ScreenUpdating = False
Set wbA = ActiveWorkbook
strPath = wbA.path
strPath = strPath & "\"
Set wbA = Workbooks.Open(strPath & "Job export pic3")
Set wb2 = ThisWorkbook

For Each ws In Worksheets
ws.Select
nm = wb2.Sheets("Prep+BOM").Cells(i, j).Value _

strFile = nm & ".pdf"
strPathFile = "C:\suvaline" & strFile


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
filename:=strPathFile & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False

 i = i + 1

Next ws
    wbA.Close


End Sub

You are looking for the [Workbooks.Open method](https://learn.microsoft.com/en-us/office/vba/api/excel.workbooks.open) and you might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, Jan 23 '19 at 10:26
Put a Watch on strPathFile and check it's value. I think that you're missing some backslashes. — Frank Ball, Jan 23 '19 at 15:28
@FrankBall Thanks, this helped me easily find the missing backslashes and other mistakes. Now the code works! — Henry R, Jan 25 '19 at 12:33

score 0 · Answer 1 · answered Jan 25 '19 at 12:35

This is the final code that works for me. Definitely not the most elegant:

Sub ExportToPDFFromClosed()

Dim ws As Worksheet
Dim wbA As Workbook
Dim strPath
Dim strFile
Dim wb2 As Workbook
Dim strPath2
Dim saveDest

i = 11
j = 2


Application.ScreenUpdating = False
Set wbA = ActiveWorkbook
strPath = wbA.path
strPath = strPath & "\"
Set wbA = Workbooks.Open(strPath & "Job export pic3")
Set wb2 = ThisWorkbook
strPath2 = wb2.Sheets("Prep+BOM").Range("B7")
saveDest = "C:\suvaline\" & strPath2 & "\"

For Each ws In Worksheets
ws.Select
nm = wb2.Sheets("Prep+BOM").Cells(i, j).Value _

strFile = nm & ".pdf"
strPathFile = saveDest & nm & "\" & strFile


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
filename:=strPathFile & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False

 i = i + 1

Next ws
    wbA.Close


End Sub

Excel print worksheets to PDF from closed file

1 Answers1