时间戳

首页 > 解决方案 > 如何通过在单元格中输入序列号将图片插入到 Excel 文件中？

excel - 如何通过在单元格中输入序列号将图片插入到 Excel 文件中？

问题描述

我正在尝试通过在单元格中输入序列号将图片插入 Excel 文件。

我在尝试插入图片时遇到语法错误。具体在哪里说.Shapes.AddPicture。

Sub picture_insert()
Dim picBild As Picture
Dim blnAvailable As Boolean
Dim link As String
Dim Pattern As String
Dim Serial As String
Dim t As String
Dim P1 As String
Dim P2 As String
link = "\\chimera\home\hillerbr\My Documents\Index project\"
Pattern = Range("A14")
Serial = Range("B14")
t = ".jpg"
P1 = Range("C14")
P2 = Range("D14")
With Worksheets("Data Breakdown")
   
    For Each picBild In .Pictures
        If picBild.Name = "280.1" Then
            'The picture already exists
            blnVorhanden = True
            Exit For
        End If
    Next picBild
    'only execute if picture does not yet exist
    If blnVorhanden = False Then
        With .Shapes.AddPicture Filename := link & Pattern & Serial & P1 & t
            .Name = Range("C14")
            .ShapeRange.LockAspectRatio = msoFalse
            .Width = 450
            .Height = 500
            .Left = Worksheets("Data Breakdown").Range("A10").Left
            .Top = Worksheets("Data Breakdown").Range("G20").Top
        End With
       With .Shapes.AddPicture Filename := link & Pattern & Serial & P1 & t
            .Name = Range("D14")
            .ShapeRange.LockAspectRatio = msoFalse
            .Width = 450
            .Height = 500
            .Left = Worksheets("Data Breakdown").Range("E10").Left
            .Top = Worksheets("Data Breakdown").Range("G20").Top
        End With
    End If
    
End With
   End Sub

Sub Image_Remove()
Dim picBild As Picture
With Worksheets("Data Breakdown")
    
    For Each picBild In .Pictures
        If picBild.Name = Range("C14") Then
            picBild.Delete
            Exit For
        End If
    Next picBild
     For Each picBild In .Pictures
        If picBild.Name = Range("D14") Then
            picBild.Delete
            Exit For
        End If
    Next picBild
End With

End Sub

标签： excelvba

解决方案

提供您的变量指向一个有效的图像，我发现下面的代码有效。

Sub Test()

    Dim sht As Worksheet
    Set sht = Worksheets("Data Breakdown")
    
    With sht
        With .Shapes.AddPicture(Filename:=link & Pattern & Serial & P1 & t, _
                                LinkToFile:=True, SaveWithDocument:=True, _
                                Left:=.Range("A10").Left, Top:=.Range("G20").Top, Width:=450, Height:=500)
                                  
            .Name = "ABC"
            .LockAspectRatio = True
        
        End With
    End With
    
End Sub

AddPicture的帮助页面显示有 7 个必需参数。

推荐阅读