我经常使用一个网站来生成电子表格。网站上唯一的项目是“开始日期”字段、“结束日期”字段和“开始日期”按钮。在输入日期范围并单击"Go“下载.cfm文件后,单击”打开与excel一起打开“,excel警告该文件具有不同的扩展名,并验证该文件是否已损坏,并单击”打开“,我拥有所需的数据,并根据需要从该文件中生成一个宏。我想让这些步骤自动化:
Go to website
Change Start Date
Change End Date
Click Go
Click Open file
Agree to open different extension我以前使用过的从网站获取数据的宏只复制和粘贴特定url上可见的数据,如下所示。我操作输入电子表格上的url来操作数据。
Dim addWS As Worksheet
Set addWS = Sheets.Add(Before:=Sheets("Input"))
addWS.Name = "Website Data"
Dim myurl As String
myurl = Worksheets("Input").Range("G4")
With Worksheets("Website Data").QueryTables.Add(Connection:= _
"URL;" & myurl, _
Destination:=Range("A3"))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With谢谢。
发布于 2013-10-01 17:41:31
下面的代码适用于我。您将不得不更改"startDate“和"endDate”,这取决于特定网站如何命名输入框。
Sub test_fetch()
Dim IE As Object
Dim objElement As Object
Dim objCollection As Object
Dim i As Long
Dim Doc As Object, lastrow As Long, tblTR As Object
Set IE = CreateObject("InternetExplorer.application")
IE.Visible = True
IE.navigate "http://your_website"
Do While IE.Busy
Application.Wait DateAdd("s", 1, Now)
Loop
Application.StatusBar = "Fetching Website Data. Please wait..."
Set objCollection = IE.document.getElementsByTagName("input")
i = 0
While i < objCollection.Length
If objCollection(i).Name = "startDate" Then
' Set text for start date
objCollection(i).Value = "09/15/2013"
ElseIf objCollection(i).Name = "endDate" Then
' Set text for end date
objCollection(i).Value = "09/21/2013"
Else
If objCollection(i).Type = "submit" And _
objCollection(i).Name = "" Then
' "Search" button is found
Set objElement = objCollection(i)
End If
End If
i = i + 1
Wend
objElement.Click ' click button to search
End Subhttps://stackoverflow.com/questions/18989745
复制相似问题