Assign Web Query Result to a Variable

[ron]

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

 

[Joel]

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]

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






- 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

 

[Joel]

the best way is like this

set MyRange = Range("A1")

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

 

[ron]

the best way is like this

set MyRange = Range("A1")

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






- 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

 

[Joel]

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]

Belwo is code I wrote a few weeks ago.  It takes a couple of minutes torun
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







- 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

 

[Joel]

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.