Insert a pdf into a worksheet, based on a cell's value

[ROBINSON_US]

I've conquered some pretty crazy stuff with vba but this one is a doozy... Not sure if this can even be done with vba, but, I need to insert a pdf file into my worksheet, based upon a cell's value - which is the corresponding pdf file name. I've attached a test sheet image; xl2bb seemed to screw up the layout and format. In cells A3, A7, A11 & A15, I have an invoice number. Below each of those cells, is another, with the text "Total Price Diff Charge". Right under that cell, I need to insert the pdf file that corresponds to the value in the cell above it.

I need to mention that I have a bunch of these sheets, all with 1500 or more entries. The layout is consistent, and doesn't change. So, the invoice number will always start with A3, and continue to A7, A11, A15, (every 4 rows, etc.), until the end of the worksheet.

Any insight would be considered a life saver!
Thanks

 

[John_w]

Try this macro on a copy of your workbook, changing the PDFfolder string. It inserts PDF files in the active sheet and could be easily modified for multiple sheets.

Public Sub Insert_PDFs()

    Dim PDFfolder As String
    Dim PDFobj As OLEObject
    Dim r As Long
    
    PDFfolder = "C:\path\to\PDFs\"
    If Right(PDFfolder, 1) <> "\" Then PDFfolder = PDFfolder & "\"
    
    With ActiveSheet
        r = 3
        While Not IsEmpty(.Cells(r, "A").Value)
            If Dir(PDFfolder & .Cells(r, "A").Value & ".pdf") <> vbNullString Then
                Set PDFobj = .OLEObjects.Add(fileName:=PDFfolder & .Cells(r, "A").Value & ".pdf", Link:=False, DisplayAsIcon:=False, Left:=.Cells(r + 2, "A").Left + 2, Top:=.Cells(r + 2, "A").Top + 2)
            End If
            r = r + 4
        Wend
    End With
    
End Sub

 

[ROBINSON_US]

This works great, but crap. I think I should've clarified better: The pdf needs to be inserted right after that row with the "price diff" text in it. So, what it would look like would be the excel data, the pdf, then the excel data, another pdf, etc. This might be too much to accomplish via vba, but thought I'd ask.

 

[John_w]

The pdf needs to be inserted right after that row with the "price diff" text in it

What is the code doing then?

Top:=.Cells(r + 2, "A").Top + 2 positions each PDF 2 rows below each invoice number row.

 

[ROBINSON_US]

What is the code doing then?

Top:=.Cells(r + 2, "A").Top + 2 positions each PDF 2 rows below each invoice number row.

It was just sticking the pdf into the worksheet... That was my fault though, I should've been more clear as to where the pdf needed to be. Thank you