Web Query with a Twist

[ron]

I have a macro that used to go to the Travelocity home page and
download fares to cities of interest. The standard piece of code that
accomplished this follows:

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.travelocity.com/", _
Destination:=Range("A1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With

Travelocity has changed their web page design, with the new design
there is a link on the home page that I now need to click in order to
load the page with the relevant data. I thought I could still use the
above code to download the page then find the cell with the relevant
url and then download that page and parse for my data. But alas, the
above code no longer downloads anything to Excel (although when I
direct my browser to this url, I do see the Travelocity home page).

I then tried the following approach:

Set ie = CreateObject("InternetExplorer.Application")

With ie
.Visible = True
.Navigate "http://www.travelocity.com"

' Loop until the page is fully loaded
Do Until Not .Busy
DoEvents
Loop

ie.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DONTPROMPTUSER
ie.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT

It successfully opens a browser window to the Travelocity home page,
but I get the following error message:

Method 'ExecWB' of object 'IWebBrowser2' failed

when the ie.ExecWB OLECMDID_SELECTALL... line of code tries to run.
Can someone help get me pointed in the right direction? Why do both
of these approaches fail? Separately, is there a way to identify the
link of interest on the home page and navigate there before I download
anything to Excel?..TIA, Ron

 

[Don Guillett]

You would have to goto the site>get the url that does what you
want>incorporate that into your macro>see what codes make the changes and
modify your url to suit.
ie xxxdx"&[b2]&"
where be contains the city or ??