PDA

View Full Version : VBA Web query newbie problem



NYCworker86
01-16-2009, 11:01 AM
Hello,

I'm a beginner to VBA, so this might be a stupid/easy question. I'm trying to run a web query on a list of locations, and get back GPS coordinates. Here is my code that I have been using with Excel 07.


Sub Web_Query()
Dim WAIT As Double
For m = 1 To 6
AddressString = Cells(m, 1)
RangeString = "C" & m
ConnectString = "URL;http://maps.google.com/maps/geo?q=" & AddressString & "&output=csv&sensor=false"
WAIT = Timer
While Timer < WAIT + 2
DoEvents
Wend
With ActiveSheet.QueryTables.Add(Connection:=ConnectString, Destination:=Range(RangeString))
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
End With
Next m
End Sub


The wait is there because Google blocks you if requests come in too fast.

Also, I know the web address says "htp". (Corrected by MD) I had to take out the t because it wouldn't let me post a web address if I haven't made 5 posts yet!

I have this data repeating in column A for testing.
1600+Pennsylvania+Ave+Washington+D.C.
252+E+12th+St+New+York+New+York

Problem: The first time I run the code, I get a 400 error right away. If I run it again immediately, I get 1 set of coordinates returned in cell C1, then the 400 error. The next time I get 2 sets in C1 and C2, and so on.

What am I missing?!

Bob Phillips
01-16-2009, 11:13 AM
I just ran it and it ran straight through. Nothing in C3:C6 because no locations in A3:A6, but that apart ...

NYCworker86
01-16-2009, 03:01 PM
I tried opening a completely new workbook and starting from scratch, copy and pasting from my post, and I still have the same problem.

Could it be a setting on my machine causing this?

victoruno
04-17-2009, 06:08 PM
Don't know that is crazy.... But it could be that it is just going to fast... put a longer wait in there, yeah it gets long but your data is automatic.