CODEWITHSUNDEEP
excelvba
Jez
Jez

Reputation: 159

Using the filename of workbook to find image with same filename Excel VBA

I was testing importing an image into a worksheet, which has proved successful, how can I use the filename of my workbook, which I store in a range to then look in a preselected directory for the image with the same filename?

My filename is held in Range - LkupFileName

Sub InsertImage()
    Dim ws As Worksheet
    Dim ImgPath As String
    Dim W As Double, H As Double
    Dim L As Long, T As Long
    Set ws = ThisWorkbook.Sheets("myworksheet")
    '~~> File Location of saved JPG
    ImgPath = "C:\images.jpg"
    With ws
       W = 100  '<~~ Width
       H = 50   '<~~ Height
       L = .Range("H140").Left   '<~~ Left Position for image
       T = .Range("H140").Top    '<~~ Top Position for image
       'Copy & Paste Image code
       With .Pictures.Insert(ImgPath)
          With .ShapeRange
             .LockAspectRatio = msoTrue
             .Width = W
             .Height = H
          End With
          .Left = L
          .Top = T
          .Placement = 1
       End With
    End With
End Sub

Upvotes: 1

Views: 275

Answers (1)

WGS
WGS

Reputation: 14169

Try this one:

Sub InsertImage()
    Dim ws As Worksheet
    Dim ImgPath As String, ImgName As String
    Dim W As Double, H As Double
    Dim L As Long, T As Long
    Set ws = ThisWorkbook.Sheets("myworksheet")
    '~~> File Location of saved JPG
    ImgName = ws.Range("LkupFileName").Value
    ImgPath = "C:\Foo\Bar\" & ImgName & ".jpg" 'Modify accordingly.
    With ws
       W = 100  '<~~ Width
       H = 50   '<~~ Height
       L = .Range("H140").Left   '<~~ Left Position for image
       T = .Range("H140").Top    '<~~ Top Position for image
       'Copy & Paste Image code
       With .Pictures.Insert(ImgPath)
          With .ShapeRange
             .LockAspectRatio = msoTrue
             .Width = W
             .Height = H
          End With
          .Left = L
          .Top = T
          .Placement = 1
       End With
    End With
End Sub

Two things are assumed:

  • By LkupFileName, I'm assuming this is a named range.
  • The image will always be found in the directory you specify.

Let us know if this helps. :)

Upvotes: 3

Related Questions