VBA / Excel / HTML / Java

[Joseph]

Hello, I am new here and have to say this forum is immaculate. I am a bit of a forum junky from way back. After following 10 links from random sites, I wound up here and am quite pleased, looking forward to contributing. Kudos to the staff.

Anyways, I am in need of some help. I am currently trying to query a website, that contains tables that are filled by java. I can do everything, except extract the tables as I have zero experience in java. Would anyone here have experience with that, who would be willing to provide assistance?

Joe

[HansV]

Welcome to Eileen's Lounge!

We do have some web programming experts, but since it's either New Year's Eve or New Year's Day around the world, with a weekend following, you might not get a reply immediately.

[Jezza]

Hi Joe
Can I just clarify your requirement please? When you say you are "trying to query a website, that contains tables that are filled by java" are you saying that the tables have java applets in them or data derived from javascript?

I am sorry for the question as I am unsure of your previous experience and the confusing java and javascript are a common problem.

A link to the page (if it is in the public domain) would be useful to explain your issue.

[jscher2000]

I am currently trying to query a website, that contains tables that are filled by java. I can do everything, except extract the tables as I have zero experience in java. Would anyone here have experience with that, who would be willing to provide assistance?

I'm assuming that the area you want to access is not recognized as a table when you go through the process of creating a Web Query. Is that correct?

If it is a genuine HTML table but it is dynamically generated, you may have to automate IE to access the table.

If the data is presented within a Java applet (plugin or ActiveX control), then it also may be difficult to extract those contents using VBA. I searched a bit on the web and found some old threads that might be relevant, but it's hard to say:

extract data from java applet - Application Forum at ObjectMix.com
Interfacing with applet - Application Forum at ObjectMix.com

[Joseph]

I am pretty sure the website utilizes javascript. I am able to use the 'web query' function to import the data within the table to Excel.

I am using the IE object currently, to log in, navigate and use the selected index's. However the tables will not import, nor can I effectively select the "Export" button. The website is not public or I would post. I have attached the source and if needed can supply a temp. log in for the website.

*Edit - I can not upload the source, the site is suspect that the file is a possible attack vector....I really don't think anyone want's me to paste it to the post.

Uploaded source to box.net.

http://www.box.net/shared/iuhyyhr0m4" onclick="window.open(this.href);return false;

[jscher2000]

I am using the IE object currently, to log in, navigate and use the selected index's. However the tables will not import, nor can I effectively select the "Export" button.

I'm not sure about "import" and "export." When I've accessed a page by automating IE I was working with the page as HTML, and plumbing its DOM (document object model). If you have IE8, there is a Developer tool on the Tools menu that will let you obtain further information about the generated table. In particular, if it has an ID attribute, you can use that to access it from your VBA code.

These old threads give an example of fishing through a particularly complex table structure to get at the data you might want. I'm sure yours isn't as bad. (However, I didn't look closely at the saved source because in most cases the saved source does not contain the new data you want to get at.)

Screen scraper (Access 2003 SP1)
Screen scraper cont... (IE6)