vba mysql 自动化错误,VBA - Excel - 自动化错误未指定错误

So I ran into a slight stumbling block and hopefully here someone can help me. In short, I need to visit a string of webpages (the list of the names on each page are already input, that code works fine). As my code visits each page, I need to pull back information. Unfortunately, there's a problem - it can't even make it through the "A" list before I get "Automation Error Unspecified Error" and it's never at the same spot.

I've tried the "normal" steps to fix this. I've installed the VB 6 Controls and I've unregistered and re-registered mscomctl.ocx, and including On Error Resume Next (which doesn't do anything).

It usually reaches over 100 cases before it dies (randomly as I said earlier). And AFTER the error pops up, when I try to re-run it (with or without changes) and it errors on the first one. If I restart my computer it will let me try again (for whatever reason) but it still doesn't finish.

Is the code too complex and I need to reduce it? I can probably find a way to make it only run for each letter at a time (run all A's, then do B's, etc) but I still can't even get it to complete the letter A.

I noticed in another thread someone had suggested instead of using IE to swap to xmlhttp - is that a fix for this? Is the problem that this script is too long? What exactly am I doing wrong here?

Sub Lookup()

Range("AI1").Value = "Unique ID"

Range("AJ1").Value = "Name"

Range("AK1").Value = "Birth Year"

Range("AL1").Value = "Title"

Range("AM1").Value = "State"

Range("AN1").Value = "Position"

Range("AO1").Value = "Country"

Range("AP1").Value = "Appointed"

Range("AQ1").Value = "Credentials"

Range("AR1").Value = "Terminations"

Dim i As Integer

For i = 1 To 26

If i = 24 Then

Range("X:X").End(xlUp).Select

ActiveCell.Value = ""

Else

Dim ic As String

ic = LCase(ConvertToLetter(i))

Range(ic & "5000").End(xlUp).Select

Dim J As Integer

J = ActiveCell.Row

Dim k As Integer

For k = 2 To J

Range(ic & k).Select

Dim Lookup As String

Lookup = ActiveCell.Value

Dim IE As Variant

Set IE = CreateObject("InternetExplorer.Application")

IE.Visible = False

IE.navigate "http://history.state.gov/departmenthistory/people/" & Lookup

Do

DoEvents

Loop Until IE.readyState = READYSTATE_COMPLETE

Dim Doc As HTMLDocument

Set Doc = IE.document

Dim Italics As Integer

Italics = 0

Dim EachA As Integer

For EachA = 64 To 100

Dim Position As String

Position = Doc.getElementsByTagName("a")(EachA).innerText

If Position = "Home" Then

Exit For

Else

Dim NameBY As String

NameBY = Doc.getElementsByTagName("h2")(1).innerText

Dim TitleST As String

TitleST = Doc.getElementsByTagName("p")(1).innerText

Range("AJ" & "90000").End(xlUp).Offset(1, 0).Select

ActiveCell.Value = NameBY

TitleState = Split(TitleST, vbLf)

ActiveCell.Offset(0, 2).Value = TitleState(0)

On Error GoTo 1037

ActiveCell.Offset(0, 3).Value = TitleState(1)

On Error GoTo 1037

1037

ActiveCell.Offset(0, 4).Select

ActiveCell.Value = Position

Dim EachLi As Integer

EachLi = EachA - 1

If Doc.getElementsByTagName("li").Item(EachLi + Italics).innerHTML Like "*" Then

Italics = Italics + 1

Else

End If

Dim JobList As String

JobList = Doc.getElementsByTagName("li")(EachLi + Italics).innerText

Dim Job() As String

Job() = Split(JobList, vbLf)

Dim JCount As Integer

For JCount = LBound(Job) To UBound(Job)

ActiveCell.Offset(0, 1).Select

ActiveCell.Value = Job(JCount)

Next JCount

End If

Next EachA

Next k

End If

Next i

End Sub

解决方案

One thing I notice is that you're continually creating new IE objects inside the loop, and you're never destroying them or setting to Nothing. It's pointless, expensive, and possibly a source of error to be creating 100+ instances of IE.

I think it will probably help to create a single instance of IE initially, and then use that same object inside the loop to navigate the desired URLs.

So instead of this:

Dim IE As Variant

Set IE = CreateObject("InternetExplorer.Application")

Do this:

Dim IE as Object

If IE Is Nothing Then Set IE = CreateObject("InternetExplorer.Application")