Assign Web Query Result to a Variable
 

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

Assign Web Query Result to a Variable
 

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

Assign Web Query Result to a Variable
 

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

Assign Web Query Result to a Variable
 

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

Assign Web Query Result to a Variable
 

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

Assign Web Query Result to a Variable
 

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

Assign Web Query Result to a Variable
 

On Aug 16, 2:39*pm, Joel wrote:
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- Hide quoted text -

- Show quoted text -

Thanks for the reply Joel. As I mentioned in my last post, I
understand that one can use the GET/POST or set IE methods to capture
web page information and assign that information to a variable, rather
than paste it to a worksheet. I am specifically asking if the same
can be done with the web query method...Thanks, Ron

Assign Web Query Result to a Variable
 

I hop this helps. Your code is looking for tables which are really tag items
called Table. If you go to menu on Internet Explorer and look at your
webpage under View - Source. You will see the tables as the following

<Table This is the start of a table

</Table This is the end of the table.


You can find the table with this

Set MyTable = IE.Document.getelementsbytagname("Table")

then yo can get the tables like this

for each itm in MyTable.all
innertext = itm.innertext
innerhtml = itm.innerhtml
class = itm.classname
tab = itm.tagname
next itm

Somtimes children will work

for each itm in MyTable.children
innertext = itm.innertext
innerhtml = itm.innerhtml
class = itm.classname
tab = itm.tagname
next itm

If you manually do a WebQuery to the webpage you are looking at the tables
will be the yellow item that you can check.


"ron" wrote:

On Aug 16, 2:39 pm, Joel wrote:
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- Hide quoted text -

- Show quoted text -

Thanks for the reply Joel. As I mentioned in my last post, I
understand that one can use the GET/POST or set IE methods to capture
web page information and assign that information to a variable, rather
than paste it to a worksheet. I am specifically asking if the same
can be done with the web query method...Thanks, Ron