代码通过F5或F8工作一次/两次,但是会产生多个错误 [英] Code that works once/ twice either by F5 or F8 but then gets multiple errors
问题描述
为了修复以下代码,我尝试将其拆分成较小的部分。所以,我有以下代码让我在Sheet1中疯狂了几个小时:
In order to fix the following code, I tried to split it up into a smaller part. So, I have the following code that drives me crazy for hours in Sheet1:
Sub Scrapping_Data()
Dim IE As Object, EURUSD1 As String, EURUSD2 As String
Application.ScreenUpdating = False
Range("A:B").Clear
Set IE = CreateObject("internetexplorer.application")
With IE
.Navigate "http://uk.investing.com/currencies/streaming-forex-rates-majors"
.Visible = False
End With
Do
DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE
Set FOREX = IE.document.getElementById("pair_1")
EURUSD1 = FOREX.Cells(1).innerHTML
EURUSD2 = FOREX.Cells(2).innerHTML
IE.Quit
Set IE = Nothing
Range("A1").Value = EURUSD1
Range("B1").Value = EURUSD2
End Sub
我第一次运行它,它工作正常。但是当我第二次运行它时,错误发生了运行时错误'91'。所以我点击了 F8 ,但是没有任何事情发生,代码工作正常,我检查Sheet1有 Cells(1,1)
和细胞(1,2)
。我再次运行它,这次发生运行时错误'13'的错误。再次,我点击了 F8 ,但没有任何事情代码工作正常。当我继续运行代码时,仍然出现错误,点击 F8 没有帮助找到问题。我的代码有什么问题?如何解决它?
I run it for the first time and it worked fine. But when I run it for the second time, the error the run-time error '91' occurred. So I clicked F8, but nothing happened the code worked fine and I checked Sheet1 there were values in Cells(1,1)
and Cells(1,2)
. I then run it again and the error the run-time error '13' occurred this time. Again I clicked F8, but nothing happened the code worked fine. When I kept running the code, the errors still occurred and clicking F8 didn't help to find the problem. What is wrong with my code? How to fix it?
我也不在这里也是,我的笔记本电脑每次运行代码变得越来越慢,我必须手动重新启动很多次
What I don't get it here too is my laptop is getting slow every time I run the code and I have to manually restart it many times.
推荐答案
以下要求您进入VBE的工具►参考和地点 Microsoft HTML对象库和 Microsoft XML v6.0 旁边的复选框。
The following requires that you go into the VBE's Tools ► References and place checkmarks beside Microsoft HTML Object library and Microsoft XML v6.0.
这是 xmlhttprewuest 相当于Internet Explorer对象web抓取到相同的URL。
This is an xmlhttprewuest equivalent of an Internet Explorer object web scrape to the same URL.
Option Explicit
Sub tournamentFixtures()
'declare the objects with early binding
Dim htmlBDY As New HTMLDocument, xmlHTTP As New MSXML2.XMLHTTP60
'declare the regular variables
Dim sURL As String, ws As Worksheet
'set a var object to the destination worksheet
Set ws = Worksheets("Sheet1")
'assign the URL to a string var
sURL = "http://uk.investing.com/currencies/streaming-forex-rates-majors"
'isolate all commands to the MSXML2.XMLHTTP60 object
With xmlHTTP
'initiate the URL
.Open "GET", sURL, False
'set hidden header information
.setRequestHeader "User-Agent", "XMLHTTP/1.0"
'get the page data
.send
'safety check to make sure we got the web page's data
If .Status <> 200 Then GoTo bm_safe_Exit
'if here you got the page data - copy it to the local var
htmlBDY.body.innerHTML = .responseText
End With
'localize all commands to the page data
With htmlBDY
'check if the element ID exists
If Not .getElementById("pair_1") Is Nothing Then
'it exists - get the data directly to the worksheet
With .getElementById("pair_1")
ws.Range("A1") = .Cells(1).innerText
ws.Range("B1") = .Cells(2).innerText
End With
Else
'it doesn't exist - bad page data
MsgBox "there is no 'pair_1' on this page"
End If
End With
bm_safe_Exit:
'clean up all of the objects that were instantiated
Set htmlBDY = Nothing: Set xmlHTTP = Nothing: Set ws = Nothing
End Sub
我已经评论过几乎每一行,所以你可以跟随发生了什么。这可能需要一些调整。我跑了〜40次,失败了一次,但这可能是我自己的互联网连接。考虑这个起点,您可以进行自己的研究来实现您的目标。如果您继续遇到此新代码的问题,请不要将其粘贴到另一个问题中,并提出为什么它不工作,而无需进行某些研究并自行尝试解决方案。 StackOverflow 是专业和爱好者程序员的网站。
I have commented virtually every line so you can follow what is happening. This may need some tweaking. I ran it ~40 times and it failed once but that could have been my own Internet connection. Consider this a starting point where you can do your own research to accomplish your goals. If you continue to have problems with this new code, please do not paste this into another question and ask why it doesn't work without doing some research and attempting a solution yourself. StackOverflow is a site for professional and enthusiast programmers.
我放弃了提供网络抓取问题的解决方案,因为页面技术变化太快,无法保持外设。你必须参与立即的改变,才能够迅速回应他们,而我自己的利益就在别的地方。我回应了这个请求,因为你实际提供了要测试的URL(有些人问问题实际上是重要的 - 去图),我认为var的静态调光将有所帮助。 >
I gave up trying to offer solutions to web scraping problems because page technology changes too fast to keep up on a peripheral level. You have to be involved in the immediate changes to be able to respond to them quickly and my own interests lie elsewhere. I responded to this request because you actually supplied the URL to test against (something few people asking questions actually think is important - go figure) and I thought the static dimming of the var would help.
这篇关于代码通过F5或F8工作一次/两次,但是会产生多个错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!