我该如何在使用 VBA
和 Excel 2007
的情况下从资源中获取 og:image
呢?
例如,这个 URL:
https://www.bbc.com/reel/video/p08jgfdg/the-truth-about-christopher-columbus
我该如何在使用 VBA
和 Excel 2007
的情况下从资源中获取 og:image
呢?
例如,这个 URL:
https://www.bbc.com/reel/video/p08jgfdg/the-truth-about-christopher-columbus
如果您使用的是较新版本的Excel,可以尝试以下方法:
Sub GetImageFromHead()
Dim MyUrl As String
MyUrl = "https://www.bbc.com/reel/video/p08jgfdg/the-truth-about-christopher-columbus"
'Required library reference: Microsoft XML v6.0
Dim HttpRequest As MSXML2.XMLHTTP60
Set HttpRequest = New MSXML2.XMLHTTP60
HttpRequest.Open "GET", MyUrl, False
HttpRequest.Send
Dim HtmlDoc As Object
Set HtmlDoc = CreateObject("htmlfile")
HtmlDoc.Write HttpRequest.responseText
'This next line makes sure that the JavaScript on the page gets processed before continuing execution of the code.
DoEvents
'Required library reference: Microsoft HTML Object
Dim MetaCollection As MSHTML.IHTMLElementCollection
Set MetaCollection = HtmlDoc.getElementsByTagName("meta")
Dim HtmlElement As MSHTML.IHTMLElement
For Each HtmlElement In MetaCollection
If HtmlElement.getAttribute("property") = "og:image" Then
ActiveSheet.Pictures.Insert (HtmlElement.getAttribute("content"))
End If
Next
End Sub
但是,由于你的问题涉及Excel 2007,因此你需要像这样定义HttpRequest
:
'Required library reference: Microsoft XML v3.0 or v5.0
Dim HttpRequest As MSXML2.XMLHTTP
Set HttpRequest = New MSXML2.XMLHTTP
如果你想要一个只返回URL字符串而不是将其用于插入活动工作表中的图像的函数,你可以轻松地编辑Sub过程,使其成为一个接受MyUrl参数并返回字符串的函数(例如:这样)。
HttpRequest.Send
处中断...无论如何...这是否可能成为一个函数,以便我可以从任何单元格获取MyUrl
,就像这样:=GetImageFromHead(A3)
? - bpyMyUrl
作为参数来实现,但你想让函数返回什么呢?作为字符串的图像URL? - DecimalTurn