creating VBA Code

[Sanbiz94]

Hello Excel Lovers!

I am Very new to excel vba i been trying to the hang of programming since i can work in excel with no problem. I have been asked by my company if i can create a code that will oppn Interent Explorer and capture data from one site. And i have the basic code but i need mixe it all together in order to get it working, every time i try to run it but an error message keeps popping up. Some tell me whats wrong with code and how can i fix it. PLease!!



Option Compare Text

Private Sub Worksheet_Change(ByVal Target As Range)
Dim appIE As Object
Dim rs As Recordset
Dim webaddress As String

Set rs = CurrentDb.OpenRecordset("Tables")
rs.MoveLast
rs.MoveFirst

DoCmd.SetWarnings False

On Error Resume Next

Set appIE = GetObject("InternetExplorer.Application")

appIE.Visible = True

Do Until rs.EOF
If IsNull(rs!orgwebsite) Then
webaddress = rs!link
appIE.navigate webaddress

Do Until appIE.ReadyState = 4
DoEvents
Loop


numTDs = appIE.Document.getelementsbytagname("td").Length
If numTDs = 77 Then
Title = appIE.Document.getelementsbytagname("td").Item(36).innertext
facility = appIE.Document.getelementsbytagname("td").Item(38).innertext
orgprofile = appIE.Document.getelementsbytagname("a").Item(31).href
orgwebsite = appIE.Document.getelementsbytagname("a").Item(33).href
mailaddress1 = appIE.Document.getelementsbytagname("td").Item(40).innertext
jobid = appIE.Document.getelementsbytagname("td").Item(43).innertext
acceptj1s = appIE.Document.getelementsbytagname("td").Item(45).innertext
loanpractice = appIE.Document.getelementsbytagname("td").Item(47).innertext
contact = appIE.Document.getelementsbytagname("td").Item(50).innertext
typeofrec = appIE.Document.getelementsbytagname("td").Item(52).innertext
phone = appIE.Document.getelementsbytagname("td").Item(53).innertext
fax = appIE.Document.getelementsbytagname("td").Item(54).innertext
searchfirm = appIE.Document.getelementsbytagname("td").Item(55).innertext
End If

If numTDs = 78 Then
Title = appIE.Document.getelementsbytagname("td").Item(36).innertext
facility = appIE.Document.getelementsbytagname("td").Item(38).innertext
orgprofile = appIE.Document.getelementsbytagname("a").Item(31).href
orgwebsite = appIE.Document.getelementsbytagname("a").Item(33).href
mailaddress1 = appIE.Document.getelementsbytagname("td").Item(40).innertext
mailaddress2 = appIE.Document.getelementsbytagname("td").Item(41).innertext
mailaddress3 = appIE.Document.getelementsbytagname("td").Item(42).innertext
jobid = appIE.Document.getelementsbytagname("td").Item(44).innertext
acceptj1s = appIE.Document.getelementsbytagname("td").Item(46).innertext
loanpractice = appIE.Document.getelementsbytagname("td").Item(48).innertext
contact = appIE.Document.getelementsbytagname("td").Item(51).innertext
typeofrec = appIE.Document.getelementsbytagname("td").Item(53).innertext
phone = appIE.Document.getelementsbytagname("td").Item(54).innertext
fax = appIE.Document.getelementsbytagname("td").Item(55).innertext
searchfirm = appIE.Document.getelementsbytagname("td").Item(56).innertext

End If

numAs = appIE.Document.getelementsbytagname("a").Length

' Do Until x > numAs
' test = appIE.Document.getelementsbytagname("a").Item(x).href
' Debug.Print x
' Debug.Print test
' x = x + 1
' Loop




Debug.Print jobid


rs.Edit
rs!title2 = Title
rs!facility = facility
rs!mailaddress1 = mailaddress1
rs!mailaddress2 = mailaddress2
rs!mailaddress3 = mailaddress3
rs!jobid = jobid
rs!acceptj1s = acceptj1s
rs!loanpractice = loanpractice
rs!contact = contact
rs!typeofrec = typeofrec
rs!phone = phone
rs!fax = fax
rs!searchfirm = searchfirm
rs!orgprofile = orgprofile
rs!orgwebsite = orgwebsite




End If

rs.MoveNext
Loop

rs.Close

Set rs = CurrentDb.OpenRecordset("tblSearch_Firms", dbOpenDynaset)

rs.MoveLast
rs.MoveFirst

DoCmd.SetWarnings False

On Error Resume Next

Do Until rs.EOF
'If IsNull(rs!contact) Then
webaddress = rs!orgprofile
appIE.navigate webaddress

Do Until appIE.ReadyState = 4
DoEvents
Loop
rs.Edit
rs!Specialties = ListDiv
rs!contact = contact
rs.Update

rs.MoveNext

Loop

DoCmd.SetWarnings True
appIE.Quit
rs.Close
Set ie = Nothing
Set rs = Nothing


End Sub