Export multiple files to PDF

[Hans_]

Hi all,

I am trying to create a macro that will export a few sheets from a few Excel files to 1 PDF file.
Don't know if this is even possible as i cannot find anything on it on the internet.

The below code works fine on 1 or more sheets from 1 workbook.

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _</SPAN>
        Filename:="D:\Test.pdf", _</SPAN>
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _</SPAN>
        IgnorePrintAreas:=False, OpenAfterPublish:=False

What i would ultimately prefer to have is the following.
An Excel file containing the macro and a sheet where in column A i can enter the filepath and filename. In column B i will then enter the sheetname that i would like in the PDF.

So, the macro should loop through the list on that sheet.

Is this possible? (starting with the multiple file to 1 pdf, i can figure out the rest)

Thanks for the help!

Regards,
Hans

 

[ParamRay]

.
.

Please try the following code.

Place the macro in a standard code module in the same workbook that contains your list of files.

Assumptions:
(1) Column A of Sheet1 contains full names (i.e. paths and names) of files; and
(2) Column B of Sheet1 contains the sheet name to be exported into the pdf file.

If in doubt, please read through the code before running it and make necessary adjustments. (There are lots of comments to indicate what's happening.)

Sub ExportAsPDF()

    Dim SSht As Object
    Dim SWBk As Workbook
    Dim DWbk As Workbook
    Dim FNms As Range
    Dim Cell As Range
    Dim DPdf As String
    
    'set range containing paths
    'and names of source workbooks...
    
    On Error Resume Next
    Set FNms = ThisWorkbook.Worksheets("Sheet1").Columns("A").SpecialCells( _
        xlCellTypeConstants, _
        xlTextValues)
    On Error GoTo 0
    
    'exit if nothing found
    If FNms Is Nothing Then Exit Sub
    
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
    
    'add destination workbook
    Set DWbk = Workbooks.Add
    
    'loop through source workbooks
    'and copy desired sheet into
    'destination workbook...
    
    For Each Cell In FNms
        On Error GoTo NextIteration
        Set SWBk = Workbooks.Open(Cell.Value)
        Set SSht = SWBk.Sheets(Cell.Offset(0, 1).Value)
        On Error GoTo 0
        SSht.Copy After:=DWbk.Sheets(DWbk.Sheets.Count)
        SWBk.Close SaveChanges:=False
NextIteration:
        On Error GoTo 0
    Next Cell
    
    'set path and name of
    'destination pdf file
    DPdf = "C:\Users\jsmith\Desktop\Adobe Acrobat Document.pdf"
    
    'export destination
    'workbook as pdf
    DWbk.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=DPdf, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
    
    'close destination workbook
    DWbk.Close SaveChanges:=False
    
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With

End Sub

 

[Hans_]

Works like a charm!!

Thanks for the great help!!