Data extraction from website

nianchi111

Board Regular
Joined
Aug 24, 2007
Messages
184
Dear friends,

macro code:

Sub Gooney_Goo_Goo()
Dim ie As Object
Dim i As Long
Set ie = CreateObject("InternetExplorer.Application")
With ie
.Visible = True 'Just for our test
.navigate "http://baselcatalog.messe.ch/mch/abo_sec_hall.asp?hall=1"
Do While .ReadyState <> 4: DoEvents: Loop
Do While .busy: DoEvents: Loop

With .document
For i = 0 To .links.Length - 1
If InStrB(1, .links(i).innerText, Auguste Reymond S.A.) Then
ie.navigate .links(i).href
Do While ie.ReadyState <> 4: DoEvents: Loop
Do While ie.busy: DoEvents: Loop
Exit For
End If
Next i
End With

End With
ActiveSheet.Cells(rrow, 2) = Split(ie.document.getElementById("contentdetail").innerText, vbCrLf)(0)
If ie.document.getElementById("contact").innerText <> "" Then
ActiveSheet.Cells(1, 3) = Split(ie.document.getElementById("contact").innerText, vbCrLf)(0)
ActiveSheet.Cells(1, 4) = Split(ie.document.getElementById("contact").innerText, vbCrLf)(1)
ActiveSheet.Cells(1, 5) = Split(ie.document.getElementById("contact").innerText, vbCrLf)(2)
ActiveSheet.Cells(1, 6) = Split(ie.document.getElementById("contact").innerText, vbCrLf)(3)
ActiveSheet.Cells(1, 7) = Split(ie.document.getElementById("contact").innerText, vbCrLf)(4)
ActiveSheet.Cells(1, 8) = Split(ie.document.getElementById("contact").innerText, vbCrLf)(5)
ActiveSheet.Cells(1, 9) = Split(ie.document.getElementById("contact").innerText, vbCrLf)(6)
End If
End If
ie.Quit
Loop
End Sub

I'm getting an error @ ActiveSheet.Cells(1, 9) = Split(ie.document.getElementById("contact").innerText, vbCrLf)(6).

Since there is no data in this field, how to pass this if there no data available.
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,419
Split once to get an array of lines, then loop through them, something like this (untested and uncompiled):
Code:
If ie.document.getElementById("contact").innerText <> "" Then
  Dim lines as variant, i as integer
  lines = Split(ie.document.getElementById("contact").innerText, vbCrLf)
  For i = Lbound(lines) to Ubound(lines)
    ActiveSheet.Cells(1, i+3) = lines(i)
  Next
End If
 

Watch MrExcel Video

Forum statistics

Threads
1,108,959
Messages
5,525,901
Members
409,671
Latest member
nasseralateek

This Week's Hot Topics

Top