Internet Session "Timing Out" using Excel

[makavity]

I'm trying to verify pricing on our web site with what our records show, and am using Excel to open the page and then use VB to extract the data.

The odd thing is as I access our webpage from Excel, I receive info that the "Session Has Timed Out", however when I copy the same exact web address into the Internet Explorer Address bar, the site opens perfectly.

Is there a way around this?

An example public site is:

http://www.discounttire.com/dtcs/fi...MIHHC&tp=Passenger/Performance&rcc=&tc=MMIHHC

 

[CautionMP]

makavity,

and am using Excel to open the page and then use VB to extract the data

How? A work around will depend on the method your using to mine the data.

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[makavity]

I'm using

Workbooks.Open Range("A1").Value

then using excel to find the data and compare to the existing sheet.

I've noticed that even when I have the Internet Address in a cell in Excel and I launch the hyperlink, I get the same "Page has Expired" however, I copy that exact address into the address bar in Internet Explorer and it opens correctly

 

[makavity]

I also receive the "Page has Expired" using the command:

.Navigate Range("A1")

Sometimes it works for the first location I select, however when I change to a different city, I get the same "Page has Expired" message.

I have deleted any cookies I receive when I get to the page and everything. Can't tell how it would work differently in Excel vs Internet Explorer.

 

[CautionMP]

makavity,
I suspect that the issue is the time it takes for the page to be returned to Excel. You might look at using a WebQuery since the tolerance for page to update may be longer since it is designed to work with data being returned from the internet.

Then you can reference a [tt]QueryTable[/tt] in your existing workbook to change the location then parse the data and move to the destination workbook.

Hope this helps,
CMP



[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[makavity]

I have found that it doesn't matter whether it is a web query or using the "Navigate" command, it still does not work.

If you click on the following link:

http://www.discounttire.com/dtcs/fi...MIHHI&tp=Passenger/Performance&rcc=&tc=MMIHHI

Then in Internet Explorer, change the text AFHINT to another location, such as AZFINT (2 different towns in Arizona) - the site returns a "We've got a problem" web site.

I'm wondering if it is a cookie or something I can delete, as I can go in through the menu screens to change locations and it comes up correctly and I don't see the site using a temporary id or anything.

I have also found that if I create a cell in Excel, and using Find/Replace changing the location from one place to another, the site still returns the first location...

If there a FAQ on using VDB in excel to actually navigate through Internet Explorer and being able to CLICK on links that I would know would be located somewhere on the page?

 

[CautionMP]

makavity,
Your approaching this from a totally different direction than anything I have ever used before so I won't be much help, sorry.

If your problem is caused by a cookie take a look at [tt]DeleteCookies()[/tt] in faq707-6400 : WebQuery for web sites that require a log in, it shows one method for deleting a cookie.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[stevexff]

I ran a wget on the site. What comes back looks like it comes from a Java/Struts webserver, and it's full of JavaScript. Every link on the page drives some action controller with a jsessionid parameter, the value of which is embedded in the page. So it's not a cookie. I suspect that this is what's causing your problem.

The good news is that all the significant data on the page should come from your database, so if you need to verify it then screen-scraping is NOT the way to go. Just go to the database instead.

If on the other hand, you are doing market research on competitor pricing, (which is a perfectly reasonable thing to do, as they don't have a robots.txt file on their webserver) then you may be out of luck...

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[stevexff]

This also explains why it sometimes works the first time - the webserver sets you up a new session, because you don't have one or the old one has expired. Then when you try to switch locations without supplying the jsessionid, it complains. Maybe if you can scrape the jsessionid value, you can paste it into the url query string to use on the next HTTP request? It's a long shot, but it might work...

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[makavity]

stevexff - thanks for the very helpful information. I have not tired scraping the jsessionid value before.

I have been able to figure out what the jsessionid is, but wouldn't know what to do with it (I'm trying something in excel outside my comfort zone, but learning alot from trying to solve the problem).

Not sure how to add it into the next HTTP request, since the addresses don't include it from what I've seen. Anyone know of info on doing this?

 

[stevexff]

I think you can just paste it in with the other parameters on the url, like your example above...

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]