Extracting data from a web page using VBA code

Question

I am wrting a visual basic code to automatically extract some weather data. The following is the source code of the web page.

<tr><td>
<table align="center" width="100%" summary="table used for formatting"><tr>
<td align="center"><b>Example:</b></td>
<td align="center" width="40%">Latitude 33.5<br>Longitude -80.75</td>
<td align="center">OR</td>
<td align="center" width="40%">Latitude 33 30<br>Longitude -80 45</td>
</tr></table></td></tr>
<tr><td><table width="100%" summary="table used for formatting"><tr>
<td><b><label for="lat">Latitude? </label>
<input type="text" name="lat" id="lat" size="12" value=""></b></td>
<td width="30%">South:&nbsp;&nbsp; -90 to 0</td>
<td width="30%">North:&nbsp;&nbsp; 0 to 90</td>
</tr><tr>
<td><b><label for="lon">Longitude? </label>
<input type="text" name="lon" id="lon" size="12" value=""></b></td>
<td width="30%">West:&nbsp;&nbsp; -180 to 0</td>
<td width="30%">East:&nbsp;&nbsp; 0 to 180</td>
</tr></table></td></tr>
<tr><td><table align="center" summary="table used for formatting"><tr>
<td><b> <input type="submit" name="submit" value="Submit"> </b></td>
<td><b> <input type="submit" name="submit" value=" Reset "> </b></td>
<td><i> This form is "Reset" if the input is out of range. </i></td>
</tr></table>
</td></tr></table>
</form>

I am getting an error (Object variable or With block variable not set). Could anybody help me on this code? Thank you very much in advance. Here is what I have written:

Sub extractSolData()
Dim IE As Object
Dim latitude As String, longitude As String


Set IE = CreateObject("InternetExplorer.Application")

latitude = InputBox("Enter Latitude of the location")
longitude = InputBox("Enter Longitude of the location")

With IE
.Visible = True
.navigate ("https://eosweb.larc.nasa.gov/cgi-bin/sse/[email protected]")

While IE.readyState <> 4
DoEvents
Wend

IE.document.getElementsByName(“lat”).Item(0).Value = latitude
IE.document.getElementsByName(“lon”).Item.innertext = longitude


IE.document.getElementsByName("submit").Item.Value = "Submit"

Do While .Busy Or _
.readyState <> 4
DoEvents
Loop
End With

Set IE = Nothing

End Sub

You need to indicate exactly where you're getting the error. getElementsByName always returns a collection (even when there's only a single match in the document) so whenever you use it you need to address a single element in that collection. — Tim Williams
– Tim Williams, Commented Apr 16, 2015 at 16:05

Jeanno · Accepted Answer · 2015-04-16 18:55:59Z

1

The problem is with the quotation marks you are using in

IE.document.getElementsByName(“lat”).Item(0).Value = latitude
IE.document.getElementsByName(“lon”).Item.innertext = longitude

These are not real quotes, I bet you copied this from a website and somehow the quotations marks got messed up. They need to look like

IE.document.getElementsByName("lat").Item(0).Value = latitude
IE.document.getElementsByName("lon").Item.innertext = longitude

answered Apr 16, 2015 at 18:55

Jeanno

2,8894 gold badges26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1671860 Over a year ago

Hi Jeanno! Thanks for the catch brother!! It worked. Frankly I don't remember copying them from any website but I used an earlier code from a Notepad file. Perhaps these came from there. Thanks a lot once again.

QHarr · Accepted Answer · 2018-06-22 14:52:34Z

1

You could have shortened all of that to the following by concatenating the lat on long into URL string:

Option Explicit
Public Sub nota_ong()
    Dim latitude As String, longitude As String

    latitude = InputBox("Enter Latitude of the location")
    longitude = InputBox("Enter Longitude of the location")

    If latitude = vbNullString Or longitude = vbNullString Then Exit Sub

    With CreateObject("internetexplorer.application")
        .Visible = True
        .navigate "https://eosweb.larc.nasa.gov/cgi-bin/sse/grid.cgi?email=skip%40larc.nasa.gov&step=1&lat=" & LATITUDE & "&lon=" & LONGITUDE & "&submit=Submit"
        While .Busy Or .readyState < 4: DoEvents: Wend
        'Do stuff with new page
        'Quit '< Uncomment this later
    End With
End Sub

edited Jun 22, 2018 at 14:52

answered Jun 18, 2018 at 18:28

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Collectives™ on Stack Overflow

Extracting data from a web page using VBA code

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related