Embed Multiple PDF's at once

[TheRevSWT]

I searched but couldn't find a relative topic.

Is there an easy way to embed multiple objects at once? I have roughly 60 pdfs I'd like to embed in an excel document, and really don't look forward to doing it manually.

Any help would be appreciated.

 

[John_w]

Try this macro, after changing the matchPDFfiles string to the folder location of the PDF files and wildcard file name.

Public Sub Embed_PDFs()
    
    Dim matchPDFfiles As String
    Dim PDFsFolder As String, PDFfileName As String
    Dim destCell As Range
    
    matchPDFfiles = "C:\path\to\PDFs\*.pdf"   'CHANGE THIS - folder containing the PDFs and wildcard file name
        
    With ActiveSheet
        Set destCell = .Range("B2")
    End With
        
    'Loop through matching PDFs and insert as objects
    
    PDFsFolder = Left(matchPDFfiles, InStrRev(matchPDFfiles, "\"))
    PDFfileName = Dir(matchPDFfiles)
    While PDFfileName <> vbNullString
        destCell.Select
        ActiveSheet.OLEObjects.Add Filename:=PDFsFolder & PDFfileName, Link:=False, DisplayAsIcon:=False
        Set destCell = destCell.Offset(4)
        PDFfileName = Dir()
    Wend
    
End Sub