trying to create macro to insert an object where filepath is based on contents of a cell in file

[jrmorton07]

I am very new to code and macros but I have a spreadsheet in excel 2007 that I want to create a macros for that will insert an object (which will be a pdf file) that is located in a saved folder on my pc and will change depending on the contents of the spreadsheet.

For example, If sheet 1 has a formula that the result of the formula is the file path for the attachment I want to add in cell (H1), I want the macro to do a insert object to insert the pdf file that is found in said filepath outlined in cell (h1). Sheet 2, on the other hand, will have a different file path in cell H1 based on the next project name and I need the macro to use the filepath found in sheet 2's H1 to tell the insert object where to pick up the file to insert in sheet 2.

I tried to record and paste the cell in the filepath but it wont allow you to copy and paste since it is a formula. I have to copy and paste the values of the cell and then I can copy and paste into the filepath when inserting an object. If I do the latter, it always looks for that file that was used when I recorded the macro.

Does anyone have a code they can provide that will either allow me to edit my code to change the filepath entered on the insert object or allow me to copy the result of the formula into the insert object filepath?

I thought I could do like I've done with save as when trying to utilize a cells contents as the file path/name and use the below code but that idea doesn't work for inserting an object...

ThisFile = Range("K1").Value
ActiveWorkbook.SaveAs Filename:=ThisFile
ActiveWindow.Close


HELP???

 

[AlphaFrog]

    Dim ws As Worksheet
    Dim strPath As String
    
    Application.ScreenUpdating = False
    
    For Each ws In Worksheets                       'Loop through each worksheet
    
        strPath = ws.Range("H1").Value              'Read path
        If Dir(strPath, vbDirectory) <> "" _
           And strPath <> "" Then                   'Validate path
           
            ws.OLEObjects.Add Filename:= _
                strPath & "\MyPDFfilename.pdf", _
                Link:=False, DisplayAsIcon:=False   'If valid, open pdf file
        End If
    Next ws
    
    Application.ScreenUpdating = True