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?!
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?!