"macroplay" wrote in message
...
I must have something wrong. Using the following test code, the first
query.add actually gets the web data. The second query("Fx") gives a debug
error "subscript out of range". If I replace the "Fx" with 1 then no data
is
retrived from the web. The ".refresh false" gives a "object required"
debug
error.

The subscript out of range error is probably the result of having
created multiple query tables. Choose Insert/Name/Define with Sheet1 active
and you'll probably see names like Fx_1, Fx_2, etc. Try copying the code
into a brand new workbook and try again.

If you have to create the same query table more than once you first need
to delete its associated range name, delete the query table itself from VBA
with QueryTables(x).Delete, then save the workbook, close it and re-open it.
Otherwise Excel won't let you use the same unique name again. Unfortunately,
it doesn't throw an error when you try, it just creates a different name by
appending an underscore and a number to the name you're trying to use. This
makes it a a very tricky bug.

The second problem is caused by syntax errors in your With statement.
There should be no line continuation character on the first line and the
Refresh method needs a dot operator in front of it. The corrected code looks
like this:

With ActiveWorkbook.Worksheets("Sheet1").QueryTables("F x")
.Connection = "URL;" & URL
.Refresh False
End With

In a brand new workbook with the above fix your code runs fine for me.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"macroplay" wrote in message
...
I must have something wrong. Using the following test code, the first
query.add actually gets the web data. The second query("Fx") gives a debug
error "subscript out of range". If I replace the "Fx" with 1 then no data
is
retrived from the web. The ".refresh false" gives a "object required"
debug
error.

============ code

Dim URL As String

URL = "http://www.federalreserve.gov/releases/h10/Hist/dat00_ca.txt"
With ActiveWorkbook.Worksheets("Sheet1").QueryTables.Ad d( _
Connection:="URL;" & URL, Destination:=Worksheets("Sheet1").Cells(1,
1))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
.Name = "Fx"
End With

MsgBox "continue"

Cells.Select
Selection.ClearContents
Range("a1").Select

URL = "http://www.federalreserve.gov/releases/h10/Hist/dat00_ca.txt"
With ActiveWorkbook.Worksheets("Sheet1").QueryTables("F x") _
.Connection = "URL;" & URL
' refresh false
End With

===============================