PDA

View Full Version : Using Excel 07 for complex web queries?



gurs
08-19-2008, 05:55 PM
I'm hoping to get some feedback on whether I can accomplish my goals using Excel 2007 and some VBA code. I'm using Excel 2007 on a Vista box.

My goal is to have Excel query several web sites multiple times to check for pricing information, and then report the results in a spreadsheet. So, for example, if I am going to rent a car and have 2 discount codes for each of Avis, Hertz and Budget, I would like to enter my rental details in a spreadsheet (in a fashion that each of those sites would recognize, of course), then have my VBA script go out and populate the rental request form on each site, and report the resulting prices for each discount code back into the Excel workbook.

I looked at the built-in Excel tool to get external data from the web, but the problem is that for this project you can't build all of the variables into a URL and then submit the URL. You need a tool that will can enter data into the fields on a web page and that can tolerate interstitial pages (since there are usually several screens from initial data entry to final price quote).

Can Excel/VBA do this? If not, any ideas as to what tool I should use? Thanks for the help.

TomSchreiner
08-19-2008, 08:57 PM
You are correct. You cannot do this using the built in web query alone and I would not bother with it at all in this case. By way of VBA, you can do anything that the user can do. Typically. :) The only challenging exceptions I have come across on occasion are automating the uploading or downloading of files. I can only speak for Internet Explorer. If you are using a different browser than I don't know what to tell you.

To educate yourself, search for Internet Explorer automation and MSHTML. MSHTML is a large library composed of various objects that provide access to each element and each element's properties, methods, and events

If you want specific help, you will need to provide minimum details such as the URL's, relative fields, and information that needs to be returned. If you are unable to provide the URL's because of security reasons, I can only help you in a general sense.

gurs
08-20-2008, 09:43 AM
To educate yourself, search for Internet Explorer automation and MSHTML.
Thanks for the lead, Tom. I did some searching and found an ocean of information, in which I am now drowning. If you could get me started with some examples of similar code or point me in the right direction, would be much appreciated.


If you want specific help, you will need to provide minimum details such as the URL's, relative fields, and information that needs to be returned.

Okay, I?ll give it a shot. Not sure how much detail you?re looking for, but I can always provide more (and you can always ignore the excess!) Let's use Avis as an example. The URL of the home page is (I had to obfuscate the links since VBAExpress won?t let new members post links, so I used HTTPDUBDUBDUB and HTTPSDUBDUBDUB instead of the usual URL lead-ins):

HTTPDUBDUBDUB.avis.com/AvisWeb/home/AvisHome

On this page, you need to populate the following fields:
Pick Up Month (drop down list)
Pick Up Day (drop down list)
Pick Up Hour (drop down list)
Pick Up Minute (drop down list)
Pick Up AM/PM (radio buttons)
Return Month (drop down list)
Return Day (drop down list)
Return Hour (drop down list)
Return Minute (drop down list)
Return AM/PM (radio buttons)
Airport/Location Code
Airports/All Locations (radio buttons)
Return to different location (check box)
Wizard Number
Last Name
Arriving Airline (drop down list)
Flight Number

After populating those fields, you need to click the ?Continue To Next Step? button. If you checked the ?return to different location? box, you are taken to the following URL:

HTTPSDUBDUBDUB.avis.com/AvisWeb/reservation/ReservationsDispatcher?FORM_NAME=RES_FORM&CT0=US&PE1=CA&WV0=US&PQ1=CA&OK0=US&CA0=US&NE0=US&AZ0=US&SD0=US&IL0=US&ME0=US&WI0=US&VI2=AU&FL0=US&ACTION=SHOP&ND0=US&SC0=US&NJ0=US&MD0=US&YT1=CA&RI0=US&NV0=US&AL0=US&AR0=US&VT0=US&DE0=US&ID0=US&NC0=US&UT0=US&TN0=US&WA2=AU&WA0=US&MI0=US&VA0=US&MO0=US&AK0=US&TA2=AU&WY0=US&NB1=CA&OH0=US&SA2=AU&ON1=CA&NH0=US&MB1=CA&HI0=US&NT2=AU&NT1=CA&MN0=US&PA0=US&MT0=US&DC0=US&MA0=US&BC1=CA&IN0=US&NM0=US&LA0=US&NS2=AU&CO0=US&NS1=CA&AC2=AU&TX0=US&NY0=US&MS0=US&QL2=AU&IA0=US&RESERVATION_CTX=RESERVATION_CTX_HOME&KS0=US&NF1=CA&KY0=US&OR0=US&SK1=CA&GA0=US&AB1=CA&ORI_Pub=AvisHomePage&ORI_SESSION_LANG=en&PICKUP_MONTH=7&PICKUP_DAY=21&RETURN_MONTH=7&RETURN_DAY=22&PICKUP_HOUR=9&PICKUP_MINUTE=0&PICKUP_AM_PM=AM&RETURN_HOUR=9&RETURN_MINUTE=0&RETURN_AM_PM=AM&PICKUP_LOCATION_CODE=CH1&CITY_NAME=&STATE=&COUNTRY_CODE=US&LOCATION_TYPE=ALL&RENTAL_ONEWAY=true&WIZARD_NUMBER=&LAST_NAME=&AIRLINE_CODE=&FLIGHT_NUMBER=&TARGET.%252Freservation%252FReservationsInitializer%253FACTION%253DHOME%252 6.x=100&TARGET.%252Freservation%252FReservationsInitializer%253FACTION%253DHOME%252 6.y=8

There you must enter another Airport/Location Code and then click the Continue button. This takes you to the following URL (and this is where you go directly if you didn?t select ?return to different location?):

HTTPSDUBDUBDUB.avis.com/AvisWeb/coreservices/Dispatcher

On this page, you need to populate the following:
Check Lowest/Check Corporate (radio buttons)
AWD
Rate Code
Coupon
GPS (check box)
Fuel (check box)
Child Seat (check box)
Infant Seat (drop down list)
Safety Seat (drop down list)
Booster (drop down list)
Country of residence (drop down list)
ARC/IATA
Club Red

You then click the Continue button and are taken to a new page, but the URL does not change. Here you click the radio button next to the car type you want. Some car types are listed but dimmed with no radio button due to lack of availability. After selecting a car and clicking Continue, you are taken to another page (again, same URL). This page contains the information that I?d like to gather. The data items displayed that I?d like to paste into an Excel table are as follows:

Number of Days
Included Miles
Base Rate
Surcharges
Total Tax
Subtotal
LDW cost
PAI cost
PEP cost
ALI cost
Rate Type:
AWD
Rate Code
Coupon #

The way I envisioned this working was as follows (although I?m open to easier/better methods). I?d have an Excel workbook which has several tabs. The first tab is where I enter the information for the basic rental fields. The second tab is where I keep a static list of my various discount codes, coupons codes, acceptable car types, etc. The third tab is where the table would be that accept the results of the numerous web queries that the script would run. So if I had 10 different discount codes, 3 coupons and 4 acceptable car types, the script would have to run 120 different queries and record the results of each in the data table.

Of course, there are probably 5 rental car companies that I?d like to do the same thing for, entering all the results in the same data table. I realize that each company will have its own syntax and data field requirements, so the basic rental information would probably have to be entered several different ways by me in the Excel workbook.

So is that enough information to give you an idea of what I?m trying to accomplish? Hope it wasn?t too much. Thanks again for your help!

TomSchreiner
08-22-2008, 08:29 AM
Gurs.

"Some car types are listed but dimmed with no radio button due to lack of availability."

How do you plan on dealing with this? Can you predetermine the available car types? Or is the query supposed to return a list of available car types? Please expound upon this a bit...

gurs
08-22-2008, 09:27 AM
Gurs.

"Some car types are listed but dimmed with no radio button due to lack of availability."

How do you plan on dealing with this? Can you predetermine the available car types? Or is the query supposed to return a list of available car types? Please expound upon this a bit...
I was thinking that I would specify a few car types up front without knowing whether or not they are available, and if a given car type was unavailable, there just wouldn't be a quote for that car type in the results table. But you raise a great point - clearly the script would need some kind of logic to deal with a car being unavailable. In fact, a car type may be available for one discount code and not another.

TomSchreiner
08-22-2008, 09:36 AM
Well... Since I have no idea about the results of varying combinations of arguments that I am unable to provide in order to test - such as discount codes, ect, you will need to provide me with some logic.

For example, the code could loop through each available car type and provide mutiple returns juxtaposed against your other arguments.

Or you could provide a prefered list of car types and only return results if a cartype is available.

Which or what other???

gurs
08-22-2008, 09:44 AM
I would probably start with a given discount code, and a given coupon code. Assuming there were 4 acceptable car types specified in search criteria, I would initiate the search and enter the basic parameters (dates, times, locations, etc.), then enter the discount and coupon, then check if the first car type is available. If it is, get the rate details. If it's not, see if the second car type is available, etc.

Also, one other thing that I should have mentioned in my original description. Once the basic parameters of the search are entered, the web site will allow you to go back and change the discount code and/or coupon in use and then continue on to selecting car types. So when you have finished checking the rates for a given discount/coupon, you don't have to start over, you just have to modify the discount/coupon and then select car types again.

Hope that makes sense, and I hope it answers your question. If not, let me know. Thanks.

TomSchreiner
08-22-2008, 10:14 AM
Yes. Another question. When the origin or destination is not an airport code, but City, State, Country, Airport or All locations, you are taken to another URL to select a rental location. How do your intend on dealing with this?

gurs
08-23-2008, 05:00 PM
I thought about that, and just figured that I would have to visit each site first and get the proper code for the rental location in question.

TomSchreiner
08-23-2008, 05:26 PM
"I thought about that, and just figured that I would have to visit each site first and get the proper code for the rental location in question."

Well... This bit would have been nice to know! :) If you can get the location codes - the fields for City, State/Province, Country, and the radio selection for All locations or airport locations become obsolete. Here is what I have so far. I made it as verbose as possible. Hopefully you can work with it to completion...

See attached.

Even though this is not completed, this is a good learning example for anybody needing an example of basic IE automation.

gurs
08-24-2008, 06:51 AM
That was quick! Thanks so much for the help. Hope you won't mind a few (dozen) follow-up questions once I start digging through your attachment! Thanks again.