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

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

On Aug 16, 11:38*am, Joel wrote:
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- Hide quoted text -

- Show quoted text -

Hi Joel...I had already assigned an URL to my_url earlier in my macro,
I just didn't show that step. My question is, rather than the web
page downloading to the "Destination Range" (A1 in this case), can it
be assigned to a variable instead?..Thanks, Ron

the best way is like this

set MyRange = Range("A1")

Set MyQuery = ActiveSheet.QueryTables.Add( _
Connection:=My_Url, _
Destination:=MyRange)



"ron" wrote:

On Aug 16, 11:38 am, Joel wrote:
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- Hide quoted text -

- Show quoted text -

Hi Joel...I had already assigned an URL to my_url earlier in my macro,
I just didn't show that step. My question is, rather than the web
page downloading to the "Destination Range" (A1 in this case), can it
be assigned to a variable instead?..Thanks, Ron

On Aug 16, 1:32*pm, Joel wrote:
the best way is like this

set MyRange = Range("A1")

Set MyQuery = ActiveSheet.QueryTables.Add( _
* *Connection:=My_Url, _
* *Destination:=MyRange)



"ron" wrote:
On Aug 16, 11:38 am, Joel wrote:
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- Hide quoted text -

- Show quoted text -

Hi Joel...I had already assigned an URL to my_url earlier in my macro,
I just didn't show that step. *My question is, rather than the web
page downloading to the "Destination Range" (A1 in this case), can it
be assigned to a variable instead?..Thanks, Ron- Hide quoted text -

- Show quoted text -

Thanks again for the reply Joel. If I understand your code, it will
paste the content of the "my_url" web page onto the active sheet
starting at the position defined by the variable "MyRange". I don't
want the results of the web query to be pasted into a worksheet.
Rather, I would like the result of the web query to be assigned to a
variable so that I can parse the variable with tools like InStr and
extract the data I need. Is there a way to avoid pasting the results
of a web query into a worksheet and rather assign the results to a
variable?

I know this can be dome using other web VBA approaches such as "GET/
POST" or "Set ie = CreateObject("InternetExplorer.Application")", but
can the web query method be used in this manner and avoid pasting data
into a worksheet?..TIA, Ron

Belwo is code I wrote a few weeks ago. It takes a couple of minutes to run
so let it sit. I put the classname, tagname, innertext, and innerhtml into a
worksheet so you can see what is available. You don't need to write this
data to a worksheet but it is useful when you are debugging. You can also
see the code by going to an Internet explorer and use menu View - Source
which will open a notepad window of the webpage. There is a lot more that
you can do. I can send you my entire code so yo can see what I actually did.


Sub GetDealers()
'Dim PageNumber As Object
CR = Chr(13)
LF = Chr(10)

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True

URL = "http://www.nissanusa.com/apps/dealerlocator"
Request = "?zipCode=07508&tool=Home.Locator"

'get web page
IE.Navigate2 URL & Request
Do While IE.readyState < 4
DoEvents
Loop

'get search button
Set but = IE.document.getElementById("mainSearchButton")
'put distance in listbox on webpage
Set radius = IE.document.getElementById("radius")
radius.Value = "100"


'search again a larger distance
'Select Search button and activate
but.Select
but.Click
Set SearchResults = IE.document.getElementById("searchResults")

On Error Resume Next ' Defer error handling.
Do
Err.Clear
Set PageNumber = IE.document.getElementById("pageNumber")
Pages = PageNumber.Value
DoEvents
Loop While Err.Number < 0
On Error GoTo 0

With Sheets("Sheet1")
RowCount = 1
For Each itm In IE.document.all
.Range("A" & RowCount) = itm.tagname
.Range("B" & RowCount) = itm.classname
.Range("C" & RowCount) = itm.innertext
.Range("D" & RowCount) = itm.innerhtml
RowCount = RowCount + 1
Next itm

End With
End Sub


"ron" wrote:

On Aug 16, 1:32 pm, Joel wrote:
the best way is like this

set MyRange = Range("A1")

Set MyQuery = ActiveSheet.QueryTables.Add( _
Connection:=My_Url, _
Destination:=MyRange)



"ron" wrote:
On Aug 16, 11:38 am, Joel wrote:
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- Hide quoted text -

- Show quoted text -

Hi Joel...I had already assigned an URL to my_url earlier in my macro,
I just didn't show that step. My question is, rather than the web
page downloading to the "Destination Range" (A1 in this case), can it
be assigned to a variable instead?..Thanks, Ron- Hide quoted text -

- Show quoted text -

Thanks again for the reply Joel. If I understand your code, it will
paste the content of the "my_url" web page onto the active sheet
starting at the position defined by the variable "MyRange". I don't
want the results of the web query to be pasted into a worksheet.
Rather, I would like the result of the web query to be assigned to a
variable so that I can parse the variable with tools like InStr and
extract the data I need. Is there a way to avoid pasting the results
of a web query into a worksheet and rather assign the results to a
variable?

I know this can be dome using other web VBA approaches such as "GET/
POST" or "Set ie = CreateObject("InternetExplorer.Application")", but
can the web query method be used in this manner and avoid pasting data
into a worksheet?..TIA, Ron