Try this


My_Url = "URL;http://www.msn.com"

Set MyQuery = ActiveSheet.QueryTables.Add( _
Connection:=My_Url, _
Destination:=Range("A1"))

With MyQuery
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With


or this

My_Url = "http://www.msn.com"

Set MyQuery = ActiveSheet.QueryTables.Add( _
Connection:="URL;" & My_Url, _
Destination:=Range("A1"))

With MyQuery
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With


"ron" wrote:

I'm running the following web query and it works fine. As written it
pastes the web page onto the active sheet starting at cell A1. Is
there a way to bypass pasting the web page to the worksheet and
instead assign the web page HTML or text to a variable?..TIA, ron

' Connect to the site
With ActiveSheet.QueryTables.Add(Connection:= my_url,
Destination:=Range("A1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With