excel - 用新图像替换现有图像

Question

我制作了一个与房地产相关的工作表，其中包括一些用于房产地址的单元格和一张谷歌地图 View 的房产图片。我希望能够更改工作表中的地址，然后单击图像以使用新地址的 map 刷新。我不知道如何替换图像。

这是我到目前为止所得到的:

Function scrub(s As String)
    scrub = Replace(s, " ", "+")
    scrub = Replace(scrub, ",", "")
End Function

Function GetImageAddress(rng As Range)
    Dim cell As Range
    Dim addressString As String
    addressString = ""
    For Each cell In rng
        If cell.Value <> "" Then
            If addressString <> "" Then
                addressString = addressString & "+" & scrub(cell.Value)
            Else
                addressString = scrub(cell.Value)
            End If
        End If
    Next cell

    Dim urlstart, urlmid, urlend, key As String

    key = "API_KEY" 'you'll need to get your own Google API Key for this to work
    urlstart = "https://maps.googleapis.com/maps/api/staticmap?center="
    urlmid = "&markers=color:0x359BB2%7C"
    urlend = "&zoom=17&size=640x480&scale=3&maptype=hybrid&key=" & key

    GetImageAddress = urlstart & addressString & urlmid & addressString & urlend
End Function

Sub fetchImage()
    Dim rng As Range
    Set rng = ActiveSheet.Range("E10:E12")

    Dim url As String
    url = GetImageAddress(rng)

    Dim myImage As Shape
    Set myImage = ActiveSheet.Shapes("Map")

    'something should go here to replace myImage with a new downloaded picture.

End Sub

Answer 1

使用 .Fill.UserPicture属性(property)

myImage.Fill.UserPicture url

这是一个例子

Option Explicit

Sub Sample()
    Dim url As String
    Dim myImage As Shape

    url = "https://lh4.googleusercontent.com/-X3sAhOMOHzs/AAAAAAAAAAI/AAAAAAAADMM/dTqQjEqFDm4/photo.jpg?sz=32"

    Set myImage = ActiveSheet.Shapes(1)
    myImage.Fill.UserPicture url
End Sub

将使用您的 stackoverflow DP 填充形状。



备注 :如果原始形状是插入的图片，这将不起作用。插入一个像矩形这样的新形状，然后这将起作用。