Open file based on Cell Information

[Randy N]

Hi All,
I am new to Macros and am having an issue trying to insert a jpg in to my workbook. I named my files based on information that I gather in my workbook. I sell steel and it has a Certification that is a jpg. I want to open the file and print it based on the Grade Size and Heat as well as the day I received it. I narrowed down the macro to just the inserting jpg part.

Sub PullCert()
'
'This sub opens cert jpg based on Sheet "Table" Cell "AE6'"
Dim FN As String
FN = Worksheets("Table").Range("AE6").Value

'Here is the value of Cell AE6 - C-20813640 1038-295-02 09-29-22.jpg
'Insert worksheet for picture ("Summary" is another sheet in my workbook)
Sheets("Summary").Select
Sheets.Add

'Open jpg File name, based on value of cell value in Worksheet "Table" cell "AE6"
'I defined FN above using FN =
ActiveSheet.Pictures.Insert("Z:\SCANCERTS\FN").Select
End Sub

For some reason, it will not insert the jpg using the filepath with FN.
If I copy the value in AE6, and paste in where the FN is in the macro above... ("Z:\SCANCERTS\FN").Select
it works. I think the problem lays in naming the FN or in the ("Z:\SCANCERTS\FN").Select command.

I have been trying to figure this out for hours! Can someone help me? Would really appreciate it! I know it is something really easy...but I can't see it!

Thanks,
Randy

 

[Candyman8019]

You need your FN variable outside of the quotes. Try replacing this:
ActiveSheet.Pictures.Insert("Z:\SCANCERTS\FN").Select

with this:
ActiveSheet.Pictures.Insert("Z:\SCANCERTS\" & FN).Select

 

[Randy N]

Hi Candyman8019,
Thanks for your input but that didn't work! I tried changing the Filepath as well to below. Still did not work!
Any other suggestions?
Thanks,
Randy

Sub PullCert()
'
'This sub opens cert jpg based on Sheet "Table" Cell "AE6'"
Dim FN As String
Dim FilePath as String

FN = Worksheets("Table").Range("AE6").Value
FilePath = "Z:\SCANCERTS\"

'Here is the value of Cell AE6 - C-20813640 1038-295-02 09-29-22.jpg
'Insert worksheet for picture ("Summary" is another sheet in my workbook)
Sheets("Summary").Select
Sheets.Add

'Open jpg File name, based on value of cell value in Worksheet "Table" cell "AE6"
'I defined FN above using FN =
ActiveSheet.Pictures.Insert(FilePath & FN).Select
End Sub

 

[Candyman8019]

Okay, try this. I've tested it on my pc and it works:

Sub PullCert()
'
'This sub opens cert jpg based on Sheet "Table" Cell "AE6'"
Dim FN As String
FN = Worksheets("Table").Range("AE6").Value

'Here is the value of Cell AE6 - C-20813640 1038-295-02 09-29-22.jpg
'Insert worksheet for picture ("Summary" is another sheet in my workbook)
Sheets.Add
activesheet.name = FN

'Open jpg File name, based on value of cell value in Worksheet "Table" cell "AE6"
'I defined FN above using FN =
ActiveSheet.Pictures.Insert("Z:\SCANCERTS\" & FN)
End Sub