Web Query Issue

[DharmaandGreg]

Hello and thanks for taking the time to read this request for help. I've been trying to familiarize myself with MS Excel's Web Query function (MS Excel 2007) in order to collect statistics from a baseball game.

Here is this particular page I'd like to start pulling certain data from: http://www.mmobaseball.com/stats.aspx

I can get the first page of data easily, the first 50 or so players and their stats, but I cannot get anything from the other pages (which are accessed by the 'next button') nor can I get the defensive stats of any player (accessed by clicking the button labeled "defensive stats"). When I look at the page source I see that regardless of the page I'm viewing, excel only pulls data from the original page.

How can I collect the data from these other pages? Is there more information needed to assist me? Thanks in advance

 

[teylyn]

cross post

 

[DharmaandGreg]

I've cross posted this same question on another board. http://www.excelforum.com/excel-general/712202-web-query.html#post2223640, there has been no answer yet but I will update if there is.

 

[xenou]

The data you see in the page is updated dynamically while you interact with the page - so, unlike "traditional" web pages, it cannot be accessed simply by its URL (web address) - i.e., the content is created only when you actually click the "next" button and so on, which is why Excel can't "see" it.

In other words - I'm stumped - I don't know of any easy ways to do this. It's something we will see more of, though - I'll post again if I find any tutorials on this.

ξ

 

[Legacy 98055]

If your browser can render it, you can get it into Excel. You just cannot use Excel's web query in this case. Because each website and each webpage are unique, you have to code specifically for each. What browser are you using? If not Internet Explorer, throw a userform into your project, right click on the toolbox and select additional controls... See if the Micsrosoft Web Browser is listed there. Let me know exactly what you need and notify your cross post that you have found a solution so somebody else does not spend time on this.

 

[DharmaandGreg]

I appreciate the response and will inform the crosspost. Also I am using Firefox to answer your question.

I have done the steps you've listed and can create a userform with Microsoft Web Brower. However, I do not know what to do with it. It looks as if I will need to use the userform to interact with the webpage, is that correct? And to do so I will need to familiarize myself with VBA as well as the code of the webpage? Is this the right train of thinking? Again, I really appreciate the help.

 

[Legacy 98055]

Firefox does not provide the object models that IE provides. That's why I asked if you had the webrowser control which is in essence, Internet Explorer. What information do you need from the site? Define your queries and I'll help you with an example. It's not that difficult but all of the details of your queries have to be known.

 

[detlion1643]

I'm working on something pretty much identical to your situation. I am navigating to the first url, then viewing the source code and putting that into excel on sheet2. i then "trim" down the source code to display only the urls for all the pages.
Start navigating each of the urls from sheet 2 and pulling the data you need, looping to the next url or cell from sheet 2.

 

[Legacy 98055]

Detlion, that's not going to work on this page. It is a table that is dynamically loaded and the controls have event detection to dissuade automation which is why I took it up because I am stubborn like that.

Of course I don't know what data the OP is looking for...

 

[DharmaandGreg]

I misunderstood you about the firefox/IE bit but it makes sense now.

So the data I'm trying to get (from http://www.mmobaseball.com/stats.aspx) is: all the stats, both defensive and offensive. That means all the players names as well as their ABs, R, H, etc. (the top row of the table) on the offensive page and on the defensive page the same thing, that top row which is G GS INN TC PO, etc.

Beyond that there are drop down list which can have an impact on the page. I'm trying to get these stats for every region and at every level. These are the only two drop down lists on the page which change the set of players, the other two (team and position) just sort within the list.

Is this what you asking from me, Tom? I think I may be being obtuse and missing what it is you actually need! Thanks for the continued help