none
Excel VBA: Automation error- Remote procedure call failed

    Question

  • As part of an academic research work, 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.

    • 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.

    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.

    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.

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

    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.

    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.

    Monday, July 1, 2013 5:49 PM

All replies

  • What happens if you don't quit IE each time and re-use the same IE instance?

    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Monday, July 1, 2013 9:34 PM
  • If I don't quit IE also, I am getting the same error. When I had this problem, I manually used to open the task manager and kill all the IE applications. So, instead of doing like that, I use the command,

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

    However, I am quitting the IE and killing the IE application using command and then only am using the subsequent google search. I am not sure why I am getting this error.

    <<EDIT>>

    I am running windows 7 and 32 bit version of excel 2010 (Professional plus 2010). I am using IE 9. Is it related to some version of excel or improper binding in references? I have the reference set to Microsoft excel 14.0 objects library. I am not able to trace out the exact error. I am going in a loop again and again and it seems the error happens no matter what changes I do. I believe that there must be some resolution, but not able to figure out.

    • Edited by Ramesh_UTA Monday, July 1, 2013 10:59 PM Versions of excel and IE used
    Monday, July 1, 2013 10:56 PM