Error 1004 -- QT.Refresh BackgroundQuery:=False

johnnapoletano

New Member
Joined
Jul 27, 2006
Messages
7
I've been trying to figure out why Dynamic Web Query this code doesn't work 100% of the time. I know I'm not the only one having this problem, but I have yet to see an answer that Works on the internet. I'm trying to grab Geocodes using both a Free and Paid service, they both fail. Here's a sample address query, basically the code is based off of http://www.mrexcel.com/tip072.shtml and the book code too.

Sample Query you can pop into your browser:
http://rpc.geocoder.us/service/csv?address=310+W+Lake+St,+Elmhurst,+Illinois+60126

Error 1004 Occurs on Refresh Property:
QT.Refresh BackgroundQuery:=False

I put in extra Clears and Deletes thinking the queries and names where bunching up or something. Save after 5 was a test to see if that was an issue per Microsoft help (didn't help). Message box and timer to slow down the code, theres a 15 second throttle on the free service. Web or Text who cares? I would just like all the connections to connect and data to import. When it runs, everything else works fine, just the Error 1004. Here's the full code:

Sub Web_Query()

Dim WQ As Worksheet
Dim WD As Worksheet
Set WQ = Worksheets("Query")
Set WD = Worksheets("Database")

Dim QT As QueryTable
WQ.Activate
Columns("D:AZ").Select
Selection.ClearContents
WQ.Cells(1, 1).Select
For Each MyName In ActiveSheet.Names
MyName.Delete
Next MyName
OutCol = 8
OutRow = 1
FinalRow = WQ.Cells(65536, 1).End(xlUp).Row
For i = 2 To FinalRow
ConnectString = "URL;" & WQ.Cells(i, 2).Value
MsgBox "Query Row: " & i & ") " & WQ.Cells(i, 2).Value
Application.StatusBar = i

' Save after 5 queries
If i Mod 5 = 0 Then
ThisWorkbook.Save
End If

MyName = "Query" & i

' Define a new Web Query
Set QT = WQ.QueryTables.Add(Connection:=ConnectString, _
Destination:=WQ.Cells(OutRow, OutCol))
With QT
.Name = MyName
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = False
.WebSingleBlockTextImport = True
.WebDisableDateRecognition = True
.WebDisableRedirections = True
End With

' Refresh the Query
Application.Wait Now + TimeValue("00:00:16")
QT.Refresh BackgroundQuery:=False
'Change from a live query to values
WQ.Cells(OutRow, OutCol).Value = WQ.Cells(OutRow, OutCol).Value
For Each QT In WQ.QueryTables
QT.Delete
Next QT

' Copy to Database
WD.Cells(i, 1).Value = WQ.Cells(i, 1).Value
WD.Cells(i, 3).Value = WQ.Cells(i, 2).Value
WD.Cells(i, 2).Value = WQ.Cells(OutRow, OutCol).Value

Next i

End Sub


Please help or direct me to the "Bugs" section of the
http://www.mrexcel.com/tip072.shtml and the book. I'm using Excel 2003.

Thanks.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
I tried your code with 10 iterations of the query you posted, having commented out the Name deletion, MsgBox, Save and Wait sections. Admittedly it was rather slow, but I didn't get any errors.
 
Upvote 0
I tried your code with 10 iterations of the query you posted, having commented out the Name deletion, MsgBox, Save and Wait sections. Admittedly it was rather slow, but I didn't get any errors.

Hi Andrew, I was hoping someone would get it to work for them. I'm starting to think that my IP is being flagged and blocked by both Google Maps API and Geocoder.us. I tried it with a delay, but nothing is working now. They give you the Key, then take away your Car :)

I've posted on the Google Maps API message board to see if they comment. Until then, I'm thinking of putting some Javascript on one of my sites and just copy/pasting addresses into an input box as a new tool. If anyone else has struggled with logistics on this please advise...found a better Geocode source for example, that lets you work query fast with VBA.

Thanks again Andrew.
 
Upvote 0
Does anyone know if a "buffer" or "connection" needs to be closed...or be reconnected...status check...when creating loops of queries?

I tried a new service ibegin.com and it worked for the first 47 queries, now I'm down to 1 then bust. The error keeps coming back to the QT.Refresh.

Any ideas on how to create a Message Box or something that will flush out the problem?

I am working from Thailand on 4 Mbps DSL, there is a little lag time and possible line drops here and there, could there be an issue, hangs, anything?

Here's 100 addresses if someone wants to test and report back. Thanks.

50+Stimson+Ave,+Providence,+Rhode+Island+02906
615+Jefferson+Blvd,+Warwick,+Rhode+Island+02886
461+E+Main+St,+Spartanburg,+South+Carolina
4796+Lambs+Rd,+North+Charleston,+South+Carolina+29418
1410+Colonial+Life+Blvd+WEST,+Columbia,+South+Carolina+29210
3027+E+Palmetto+St,+Florence,+South+Carolina+29506
810+Dutch+Square+Blvd,+Columbia,+South+Carolina+29210
712+Richland+St,+Columbia,+South+Carolina+29201
712+Richland+St,+Columbia,+South+Carolina+29201
3500+S+Sheldon+Ln+Ste+103,+Sioux+Falls,+South+Dakota+57105
209+10th+Ave+S,+Nashville,+Tennessee+37203
114+Sherway+Rd,+Knoxville,+Tennessee+37922
572+E+Shelby+Dr,+Memphis,+Tennessee+38109
3818+Cleghorn,+Nashville,+Tennessee+37215
7000+Lee+Hwy,+Chattanooga,+Tennessee+37421
3119+Bristol+Hwy,+Johnson+City,+Tennessee+37601
5103+Kingston+Pke,+Knoxville,+Tennessee+37919
1366+Harrow+Ln,+Memphis,+Tennessee+38114
1114+17th+Ave+S,+Nashville,+Tennessee+37212
1040+Murfreesboro+Pke,+Nashville,+Tennessee+37217
600+S+Perkins+Rd,+Memphis,+Tennessee+38117
326+Chestnut+St,+Abilene,+TX+79602
1112+E+Copeland,+Arlington,+TX+76011
50+Briar+Hollow+Ln,+Houston,+TX+77027
15303+Dallas+Pkwy,+Addison,+TX+75001
301+Congress+Ave,+Austin,+TX+78701
777+Main+St,+Fort+Worth,+TX+76102
3050+Post+Oak+Blvd,+Houston,+TX+77056
1001+Main+St,+Lubbock,+TX+79401
9901+Ih+10+W,+San+Antonio,+TX+78230
6044+Gateway+Blvd+E,+El+Paso,+TX+79905
1527+S+Cooper+St,+Arlington,+TX+76010
9390+Research+Blvd,+Austin,+TX+78759
5316+W+Highway+290,+Austin,+TX+78735
1714+Fortview+Rd,+Austin,+TX+78704
15443+Knoll+Trail+Dr,+Dallas,+TX+75248
8140+N+Mo+Pac+Expy,+Austin,+TX+78759
70+NE+Loop+410,+San+Antonio,+TX+78216
9+Village+Cir,+Dallas,+TX+75201
9+Village+Cir,+Roanoke,+TX+76262
8007+Galley+St,+Frisco,+TX+75035
2661+Washington+Blvd,+Ogden,+Utah+84401
po+box+216,+Colchester,+Vermont+05446
240+North+St,+Bennington,+Vermont+05201
139+Main+St,+Brattleboro,+Vermont+05301
522+S+Independence+Blvd,+Virginia+Beach,+Virginia+23452
1512+Brendle+Ct,+Virginia+Beach,+Virginia+23464
18+Hopton+Ct,+Sterling,+Virginia+20165
3225+Kinross+Cir,+Herndon,+Virginia+20171
5660+Indian+River+Rd,+Virginia+Beach,+Virginia+23464
520+van+Buren+St,+Herndon,+Virginia+20170
301+Princeton+Blvd,+Alexandria,+Virginia+22314
7700+Leesburg+Pike,+Falls+Church,+Virginia+22043
6201+Leesburg+Pike,+Falls+Church,+Virginia+22044
5671+Selma+Dr,+Virginia+Beach,+Virginia+23455
464+Investors+Pl+Ste+204G,+Virginia+Beach,+Virginia+23452
1512+Brendle+Ct,+Virginia+Beach,+Virginia+23464
8601+Westwood,+Vienna,+Virginia+22182
526+Dartmoor+Dr,+Newport+News,+Virginia+23608
8230+Old+Courthouse+Rd,+Vienna,+Virginia+22182
570+Kempsville+Rd,+Virginia+Beach,+Virginia+23464
732+Thimble+Shoals,+Newport+News,+Virginia+23606
4050+Innslake+Dr,+Glen+Allen,+Virginia+23060
8201+Greensboro+Dr,+Mc+Lean,+Virginia+22102
706+Church+St,+Lynchburg,+Virginia+24504
120+W+Bute+St,+Norfolk,+Virginia+23510
3600+W+Broad+St,+Richmond,+Virginia+23230
225+Franklin+Rd+SW,+Roanoke,+Virginia+24011
700+E+Franklin+St,+Richmond,+Virginia+23219
2025+Killarney,+Virginia+Beach,+Virginia+23455
7700+Leesburg+Pike,+Falls+Church,+Virginia+22043
3140+Chaparral+Dr,+Roanoke,+Virginia+24018
1734+Connecticut+Ave+NW,+Washington,+Washing+DC+20009
828+11th+St+NE,+Washington,+Washing+DC+20002
1275+K+St+NW,+Washington,+Washing+DC+20005
1001+Connecticut+Ave+NW,+Washington,+Washing+DC+20036
2025+I+St+NW,+Washington,+Washing+DC+20006
1717+K+St+NW,+Washington,+Washing+DC+20036
7700+Leesburg+Pike+Falls+Church,+Washington,+DC+20001
27825+NE+141st+Pl,+Duvall,+Washington+98019
4739+University+Way,+Seattle,+Washington+98105
8423+E+Bull+Pine+Ln,+Spokane,+Washington+99217
1210+N+Argonne+Rd+Ste+A,+Spokane,+Washington+99212
14040+NE+8th+St+Ste+212,+Bellevue,+Washington+98007
12356+Northup+Way,+Bellevue,+Washington+98005
11405+SE+290th+Pl,+Auburn,+Washington+98092
555+116th+Ave+NE,+Bellevue,+Washington+98004
501+N+36th+St+#+255,+Seattle,+Washington+98103
1654+E+Rowan+Ave,+Spokane,+Washington+99207
94+Pike+St,+Seattle,+Washington+98101
1210+N+Argonne,+Spokane,+Washington+99212
2800+Western+Ave,+Seattle,+Washington+98121
12301+NE+10th+Pl,+Bellevue,+Washington+98005
10900+NE+8th+St,+Bellevue,+Washington+98004
1210+N+Argonne,+Spokane,+Washington+99212
6036+Seward+Park,+Seattle,+Washington+98118
157+Yesler+Way,+Seattle,+Washington+98104
12021+Northup+Way,+Bellevue,+Washington+98005
1107+Vine+St,+Wheeling,+West+Virginia+26003
4818+Rosemar+Rd,+Parkersburg,+West+Virginia+26104
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top