Excel VBA：自动化错误 - 远程过程调用失败

Excel VBA: Automation error- Remote procedure call failed

提问by Ramesh

I am extracting certain data from a website. I have to do this task for at least million rows from a table. I am using excel VBA to connect with MySQL.

我正在从网站中提取某些数据。我必须为表中的至少一百万行执行此任务。我正在使用 excel VBA 连接 MySQL。

• Using MySQL to connect with excel VBA, I am getting first name, last name of an author from a table.
• For the first name, last name of an author, I am appending Linkedin to the search query and I am searching in Google.
• From the search results, I am opening the first search result page in the HTML format and extracting some information.
• I put back some of the extracted information into MySQL tables.

• 使用 MySQL 与 excel VBA 连接，我从表中获取作者的名字和姓氏。
• 对于作者的名字和姓氏，我将 Linkedin 附加到搜索查询中，并在 Google 中进行搜索。
• 从搜索结果中，我以HTML格式打开第一个搜索结果页面并提取一些信息。
• 我将一些提取的信息放回 MySQL 表中。

Everything works fine as per the above steps. However, if I try to do it for more than 10 rows I get the following error.

按照上述步骤一切正常。但是，如果我尝试对超过 10 行执行此操作，则会出现以下错误。

Automation error the remote procedure call failed and did not execute

远程过程调用失败且未执行的自动化错误

I realize it is something to do with the opening/closing of IE. In my program, I have the following code.

我意识到这与 IE 的打开/关闭有关。在我的程序中，我有以下代码。

To create a new IE application, I have defined as below.

要创建一个新的 IE 应用程序，我定义如下。

Set ie = New InternetExplorer
Set RegEx = New RegExp
Dim iedoc As Object
ie.Navigate "http://www.google.com/search?hl=en&q=" & FirstName & "+" & LastName &  
"+linkedin&meta="
Do Until ie.ReadyState = READYSTATE_COMPLETE
Loop
MyStr = ie.Document.body.innerText
Set RegMatch = RegEx.Execute(MyStr)

After extracting the data for one author, I have the below piece of code at the end.

为一位作者提取数据后，我最后得到了以下代码。

ie.Quit
Set RegEx = Nothing
Set ie = Nothing
Dim strBatchName As String
strBatchName = "F:\command.bat"
Shell strBatchName

The command.bat has the following code.

command.bat 具有以下代码。

taskkill.exe /F /IM iexplore.exe /T

It works perfectly fine if I have lesser than 10 rows in my table. However, for more number of rows I do get the above mentioned error.

如果我的表中少于 10 行，它工作得很好。但是，对于更多行数，我确实收到了上述错误。

回答by John Muggins

I would be more tempted to use the same instance of ie to get all authors data, looping through them like in the example below. Also, what is your batch file doing with the data? I've never found the need for batch files to help with vba executions. Are you using them to write text files? You can accomplish that with vba also.

我更愿意使用 ie 的同一个实例来获取所有作者数据，像下面的例子一样循环遍历它们。另外，您的批处理文件对数据做了什么？我从来没有发现需要批处理文件来帮助执行 vba。您是否使用它们来编写文本文件？你也可以用 vba 来完成。

Dim ie As Object, lastRowAuthors as long, i as long, strBatchName As String

lastRowAuthors = sheets("Authors").Cells(Rows.Count, 1).End(xlUp).row
Set ie = New InternetExplorer


for i = 1 to lastRowAuthors

    ie.Navigate "http://www.google.com/search?hl=en&q=" & sheets("Authors").range("A" & i).value  & "+" & sheets("Authors").range("B" & i).value &  
    "+linkedin&meta="
    Do Until ie.ReadyState = READYSTATE_COMPLETE
    Loop


    Set RegEx = New RegExp

    MyStr = ie.Document.body.innerText
    Set RegMatch = RegEx.Execute(MyStr)

'*****************************************************************
'   Use your REGEX commands to extract data
'*****************************************************************
    strBatchName = "F:\command.bat"
    Shell strBatchName
next i

ie.Quit
Set RegEx = Nothing
Set ie = Nothing

end sub

回答by Seb

I had a similar issue and the solution I found for this is :

我有一个类似的问题，我为此找到的解决方案是：

1. Use the version as suggested by John Muggins where you only quit and set Internet to nothing after the end of your loop

2. Add a pause between each ne search such as a second to make sure it can go through each records and search for them with no problems. In order to do this I suggest to add either Application.Wait Now + 1before the ie.Navigateline, either add an extra sub to do this operation

   Sub WaitFor(PauseInSeconds As Long)
     Dim SngSec As Long
     SngSec = Timer + PauseInSeconds
     Do While Timer < SngSec
     DoEvents
   Loop
   

   End Sub

3. Extra : If you have as you said "millions of row" you should definitely add an error handler which retrieves the eventual rows where the code crashed so that you don't have to make sure that it runs properly all the way. Do this with an On Error Resume Next. Then you can either check those rows manually either store their index in an array and go through each of them a second time.

1. 使用 John Muggins 建议的版本，您只需在循环结束后退出并将 Internet 设置为空

2. 在每个 ne 搜索之间添加一个暂停，例如一秒钟，以确保它可以遍历每个记录并毫无问题地搜索它们。为了做到这一点，我建议Application.Wait Now + 1在该ie.Navigate行之前添加，或者添加一个额外的子来执行此操作

   Sub WaitFor(PauseInSeconds As Long)
     Dim SngSec As Long
     SngSec = Timer + PauseInSeconds
     Do While Timer < SngSec
     DoEvents
   Loop
   

   结束子

3. 额外：如果你有你所说的“数百万行”，你绝对应该添加一个错误处理程序来检索代码崩溃的最终行，这样你就不必确保它一直正常运行。使用On Error Resume Next. 然后，您可以手动检查这些行，也可以将它们的索引存储在数组中并再次遍历它们。