使用工作簿的文件名查找具有相同文件名的图像 Excel VBA

Question

我正在测试将图像导入工作表，事实证明这是成功的，如何使用我存储在范围内的工作簿的文件名，然后在预选目录中查找具有相同文件名的图像？

我的文件名保存在范围 - Lkup文件名

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

Answer 1

试试这个：

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:FooBar" & 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

假设两件事：

• 通过LkupFileName，我假设这是一个命名范围。
• 该映像将始终位于您指定的目录中。

让我们知道这是否有帮助。 :)