vba 出错时转到：多次
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20957649/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
On Error Goto: Multiple times
提问by Fabio Rebelo
I'm using a macro to get infos from a website:
我正在使用宏从网站获取信息：
Sub get_prices()
Dim IE As Object
Dim URL As String
Dim cell As Range
Dim rng As Range
Set rng = Range("A2", Range("A1048576").End(xlUp))
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
For Each cell In rng.Cells
comeco:
On Error GoTo pau:
URL = Cells(1, 2) & cell.Text & "?utm_source=teste" & Rnd
IE.Navigate (URL)
Do
DoEvents
Loop Until IE.READYSTATE = 4
Application.Wait (Now + TimeValue("00:00:05"))
Cells(cell.Row, 2) = Mid(IE.Document.GetElementById("ctl00_Conteudo_ctl01_precoPorValue").innertext, 3)
Cells(cell.Row, 2).Formula = _
WorksheetFunction.Substitute(WorksheetFunction.Substitute(Cells(cell.Row, 2), ".", ""), ",", ".")
Next
pau:
IE.Quit
Set IE = Nothing
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
GoTo comeco:
End Sub
The problem is that from time to time, my Excel kind of "loses the IE object", and I get the error message "THE OBJECT IS REQUERIED" in the following line:
Cells(cell.Row, 2) = Mid(IE.Document.GetElementById("ctl00_Conteudo_ctl01_precoPorValue").innertext, 3)
问题是，我的 Excel 有时会“丢失 IE 对象”，并且在以下行中收到错误消息“THE OBJECT IS REQUERIED”：
Cells(cell.Row, 2) = Mid(IE.Document.GetElementById("ctl00_Conteudo_ctl01_precoPorValue").innertext, 3)
Normally, I just debug the code, and make a new line, like:
Set IE = CreateObject("InternetExplorer.Application")and then I resume the macro.
通常，我只是调试代码，并创建一个新行，例如：
Set IE = CreateObject("InternetExplorer.Application")然后我恢复宏。
Bud I'd like to make something more robust, something that everytime an error occurs, it closes the IE and then reopen it.
Bud 我想做一些更健壮的东西，每次发生错误时，它都会关闭 IE，然后重新打开它。
I'm trying to use
on error gotoBut it only works the first time a error occurs, if it happens again it wont go to the error section.
我正在尝试使用
on error goto但它仅在第一次发生错误时有效，如果再次发生，则不会转到错误部分。
Can anyone help me?
谁能帮我？
回答by Blackhawk
If you are trying to add error handling to catch the missing InternetExplorer instance, try the following. Add On Error statements to your code just before and just after the offending line.
如果您尝试添加错误处理以捕获丢失的 InternetExplorer 实例，请尝试以下操作。在违规行之前和之后将 On Error 语句添加到您的代码中。
On Error Goto IE_missing 'This changes error handling from the default popup error message to instead direct VBA to jump to the IE_missing label and try the code there.
Cells(cell.Row, 2) = Mid(IE.Document.GetElementById("ctl00_Conteudo_ctl01_precoPorValue").innertext, 3)
On Error Goto 0 'This resets the error handling back to the default so you don't accidentally catch errors in other parts of the code
And then place a section at the end of your code like so:
然后在代码的末尾放置一个部分，如下所示：
Exit Sub 'This keeps the rest of the Sub's code from accidentally running down into the error section.
IE_missing: 'When the specific line above errors, VBA directs execution here
Set IE = CreateObject("InternetExplorer.Application")
Resume 'Now that the IE instance has been set again, retry the offending line of code
回答by Richard Morgan
I'm not sure why Excel "kind of loses the IE object", so I hesitate to give this answer.
我不确定为什么 Excel“有点丢失 IE 对象”，所以我犹豫要不要给出这个答案。
Why not add a check before the call to make sure the IE object is not null:
为什么不在调用前添加检查以确保 IE 对象不为空：
If IE Is Nothing Then
Set IE = CreateObject("InternetExplorer.Application")
End If
Cells(kit.Row, kit.Column + i) = Mid(IE.Document.GetElementById("ctl00_Conteudo_ctl01_precoPorValue").innertext, 3)
回答by Sorceri
you lose the IE window when you call navigate. I overcame this by calling navigate then passing the url I was looking for to the function below and calling it like this
当您调用导航时，您会丢失 IE 窗口。我通过调用导航然后将我正在寻找的 url 传递给下面的函数并像这样调用它来克服了这个问题
'this is where you create your URL
URL = Cells(1, kit.Column + i) & kit.Text & "?utm_source=teste" & Rnd
'navigate to the page
IE.Navigate (URL)
'get the page we just went to
Set IE = GetWebPage(URL)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Desc: The Function gets the Internet Explorer window that has the current
' URL from the sURL Parameter. The Function Timesout after 30 seconds
'Input parameters:
'String sURL - The URL to look for
'Output parameters:
'InternetExplorer ie - the Internet Explorer window holding the webpage
'Result: returns the the Internet Explorer window holding the webpage
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function GetWebPage(sUrl As String) As InternetExplorer
Dim winShell As Shell
Dim dt As Date
dt = DateAdd("s", 300, DateTime.Now)
Dim ie As InternetExplorer
Do While dt > DateTime.Now
Set winShell = New Shell
'loop through the windows and check the internet explorer windows
For Each ie In winShell.Windows
If ie.LocationURL = sUrl Then
ie.Visible = True
ie.Silent = True
Set GetWebPage = ie
Do While ie.Busy
DoEvents
Loop
Exit Do
Set winShell = Nothing
End If
Next ie
Set winShell = Nothing
DoEvents
Loop
End Function
回答by sedat
Mid(IE.Document.GetElementById("ctl00_Conteudo_ctl01_precoPorValue").innertext, 3 , xx)
Mid(IE.Document.GetElementById("ctl00_Conteudo_ctl01_precoPorValue").innertext, 3 , xx)
there must be xx number for mid
中间必须有 xx 号
