Automatically insert pdf into spreadsheet based on filename or date entered into cell

[roadiemoose]

Trying to figure out a simple way to insert a pdf into a spreadsheet based on either the filename of the pdf and excel files or a date entered into a cell in the spreadsheet.

I have a payroll worksheet and i have a cell that contains the week ending date. I then scan paystubs and name them utilizing the week ending date. The paystub PDF's are stored in a folder called 'Paystubs'(genius right?)

So when I fill out my payroll spreadsheet and enter the week ending date, on page two of the spreadsheet, I'd like to be able to see the PDF with the corresponding paystub for that pay period.

I'm seeing how to link the PDF manually entering the information to the PDF file, but I haven't been able to automate the process.

Any tips on what to look into? Not looking for the entire process, but perhaps a formula or code to investigate and figure out from there.

Thanks in advance!

B

 

[jolivanes]

Can you use this to continue with?
You'll have to select the file for now but it should be a start.
It uses just a single cell (Cells(12, 4))so make sure to either change the row height and column width for that cell or change it to a range.

Sub Import_PDF()
Dim vFile As Variant
Dim a As Double, b As Double, PicCell As Range
Set PicCell = Cells(12, 4)     '+ ActiveSheet.OLEObjects.Count)
a = PicCell.Height / PicCell.Width
vFile = Application.GetOpenFilename("All Files,*.pdf", Title:=" Find file to insert")
If LCase(vFile) = "false" Then Exit Sub

ActiveSheet.OLEObjects.Add Filename:=vFile, Link:=False, DisplayAsIcon:=False

    With ActiveSheet.Shapes(ActiveSheet.Shapes.Count)
    b = .Height / .Width
            If b < a Then
            .Left = PicCell.Left
            .Width = PicCell.Width
            .Top = PicCell.Top + (PicCell.Height - .Height) / 2
                Else
            .Top = PicCell.Top
            .Height = PicCell.Height
            .Left = PicCell.Left + (PicCell.Width - .Width) / 2
        End If

    End With
   
End Sub

 

[roadiemoose]

Thank you. I was AFK until today. I'm going to take a look at it now.

Thanks again!

Can you use this to continue with?
You'll have to select the file for now but it should be a start.
It uses just a single cell (Cells(12, 4))so make sure to either change the row height and column width for that cell or change it to a range.

Sub Import_PDF()
Dim vFile As Variant
Dim a As Double, b As Double, PicCell As Range
Set PicCell = Cells(12, 4)     '+ ActiveSheet.OLEObjects.Count)
a = PicCell.Height / PicCell.Width
vFile = Application.GetOpenFilename("All Files,*.pdf", Title:=" Find file to insert")
If LCase(vFile) = "false" Then Exit Sub

ActiveSheet.OLEObjects.Add Filename:=vFile, Link:=False, DisplayAsIcon:=False

    With ActiveSheet.Shapes(ActiveSheet.Shapes.Count)
    b = .Height / .Width
            If b < a Then
            .Left = PicCell.Left
            .Width = PicCell.Width
            .Top = PicCell.Top + (PicCell.Height - .Height) / 2
                Else
            .Top = PicCell.Top
            .Height = PicCell.Height
            .Left = PicCell.Left + (PicCell.Width - .Width) / 2
        End If

    End With
  
End Sub