对于网络抓取项目,我计划通过特定端口发送请求。为此,我必须使用 Microsoft Visual Studio,因为 Excel VBA 控制台不支持此类功能。
但是,相应的网络链接应直接来自 Microsoft Excel 表格。此外,理想情况下,网络抓取的结果应该输入到同一个 Excel 表格中。有什么方法可以将 Visual Studio 与特定工作表链接以达到这种目的?
到目前为止,我已经找到了有关使用 Excel 的 VSTO(Visual Studio Tools for Office)加载项的响应,但无法真正找到应用此类加载项工具的方法。请在下面找到我正在使用的代码,该代码在 Visual Studio 中运行良好,但缺少指向 Excel 的链接,如上所述。
Imports System.Net
Class Client
Inherits WebClient
Const username As String = "[USERNAME]"
Const password As String = "[PASSWORD]"
Const port = 22225
Public session_id As String = New Random().Next().ToString()
Public Sub New(Optional country As String = Nothing)
Me.Proxy = New WebProxy("[PROXY]", port)
Dim login = username &
If(country IsNot Nothing, "-country-" & country, "") &
"-session-" & session_id
Me.Proxy.Credentials = New NetworkCredential(login, password)
End Sub
Protected Overrides Function GetWebRequest(address As Uri) As WebRequest
Dim request = MyBase.GetWebRequest(address)
request.ConnectionGroupName = session_id
Return request
End Function
End Class
Module TermFinder
Sub Main()
Console.WriteLine("Performing request(s)")
Dim session As New Client()
Dim x%, sheet As Variant
For x = ActiveSheet.Index + 1 To Sheets.Count
Set sheet = Sheets(x)
Next
Dim url As String, lastRow As Long
Dim XMLHTTP As Object, html As Object, objResultDiv As Object, objH3 As Object, link As Object
Dim start_time As Date
Dim end_time As Date
Dim var As String
Dim var1 As Object
lastRow = Range("A" & Rows.Count).End(xlUp).Row
Dim cookie As String
Dim result_cookie As String
start_time = Time
Debug.Print "start_time:" & start_time
For i = 8 To 772
url = "[Links to be covered]"
Set XMLHTTP = CreateObject("MSXML2.serverXMLHTTP")
XMLHTTP.Open "GET", url, False
XMLHTTP.setRequestHeader "Content-Type", "text/xml"
XMLHTTP.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1; rv:25.0) Gecko/20100101 Firefox/25.0"
XMLHTTP.send
Set html = CreateObject("htmlfile")
html.body.innerHTML = XMLHTTP.responseText
Set objResultDiv = html.getElementById("rso")
Set var1 = html.getElementById("resultStats")
If Not var1 Is Nothing Then
Cells(i, 7).Value = var1.innerText
Else : Cells(i, 7).Value = "0 results"
End If
DoEvents
Next
end_time = Time
Debug.Print "end_time:" & end_time
End Sub
End Module
安装 Office/SharePoint 功能作为 Visual Studio 程序的一部分。
在文件菜单上,指向新建,然后单击项目。
在模板窗格中,展开 Visual Basic,然后展开 Office/SharePoint。
在展开的 Office/SharePoint 节点下,选择 Office 加载项节点。
在项目模板列表中,选择 Excel 插件或打开 Excel 工作簿。
=> 这样,您应该能够在 Visual Studio 中使用完整的 Excel 工作簿。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句