Accessing a Database through VBA

[Saladsamurai]

Hey folks,

I am sure that VBA in Excel 2003 is not the most efficient way to do this, but I wanted to know if it can be done using it. Let me explain what I am trying to accomplish:

1) There is a Database of properties of different gases and fluids located HERE. (http://webbook.nist.gov/chemistry/fluid/)

2) As you can see, there are several inputs that the user has to specify as seen in the image below:

http://i12.photobucket.com/albums/a220/saladsamurai/Screenshot2010-08-29at52255PM.png

3) After hitting "continue" in the image above we come to this screen:

http://i12.photobucket.com/albums/a220/saladsamurai/Screenshot2010-08-29at53002PM.png

where we again have to give inputs.

4) If we fill in the the inputs and unclick the Java option in "3" above, we get a Table that looks like the following:

http://i12.photobucket.com/albums/a220/saladsamurai/Screenshot2010-08-29at53118PM.png



So, here is my question: Is there any way to automate this process? I have no idea what I am getting myself into here. But it would be very useful to me to be able to do this. I would like to use VBA in Excel. But if it is not possible and IS possible in another language, I might be willing to learn (depending on cost necessary).

Essentially, I would like to be able to set up a bunch of inputs in a spreadsheet and then run a routine that opens the website, feeds the necessary inputs from my spreadsheet to the site's forms in steps "2" and "3" above. Then I would like it to somehow copy and paste the resulting data in step "4" to a spreadsheet.

I do not expect anyone here to write this code for me. I want to learn how this might be done. But I would like some advice on what kinds of things I need to learn in order to accomplish this.

Any thoughts?

[Saladsamurai]

Ok. How about this question. I can use the following to get to a website from VBA:


Sub WebPage()

Const Hyper As String = "http://www.vbaexpress.com/"
ThisWorkbook.FollowHyperlink Address:=Hyper ', NewWindow:=True

End Sub



Now is there a way to get at the source code from here and copy it?

[p45cal]

have a look here: http://finance.groups.yahoo.com/group/smf_addin/ , Randy Harmelink's financial addin gets data from the web in a variety of ways. His code is open.

[Saladsamurai]

Hi p45scal. I am not sure how to get at the addin. Do I need to be a member of something?

Also, if I can just get at the source code, I should be all set. I can fill out the required inputs that are required by the website via the URL. So now it is just a matter of viewing and copying the source code.

Thanks.

[Shred Dude]

That website uses a straight forward URL nomenclature. There's no need to automate IE and fill in the forms on separate pages. Just build the URL, and then go straight to the page with the data.

For example the final URL on one query is:


http://webbook.nist.gov/cgi/fluid.cgi?T=50+to+1000&PLow=0&PHigh=10000&PInc=500&Digits=5&ID=C1333740&Action=Load&ID=C1333740&TUnit=C&PUnit=bar&DUnit=g%2Fml&HUnit=kJ%2Fkg&WUnit=m%2Fs&VisUnit=Pa*s&STUnit=lb%2Fin&Type=IsoTherm&RefState=DEF&Action=Page

Looks like the data you want always comes up in the second table on the page.

[p45cal]

Yes, click Join this Group, and join. No spam, no fuss. Randy H. is an individual who's very helpful, responsive and generous in giving his code out. I haven't looked at his code in detail but it uses techniques which can be adapted.
There are instructions, files etc. on the site.


Hi p45scal. I am not sure how to get at the addin. Do I need to be a member of something?

Also, if I can just get at the source code, I should be all set. I can fill out the required inputs that are required by the website via the URL. So now it is just a matter of viewing and copying the source code.

Thanks.

[Saladsamurai]

That website uses a straight forward URL nomenclature. There's no need to automate IE and fill in the forms on separate pages. Just build the URL, and then go straight to the page with the data.

For example the final URL on one query is:


http://webbook.nist.gov/cgi/fluid.cgi?T=50+to+1000&PLow=0&PHigh=10000&PInc=500&Digits=5&ID=C1333740&Action=Load&ID=C1333740&TUnit=C&PUnit=bar&DUnit=g%2Fml&HUnit=kJ%2Fkg&WUnit=m%2Fs&VisUnit=Pa*s&STUnit=lb%2Fin&Type=IsoTherm&RefState=DEF&Action=Page

Looks like the data you want always comes up in the second table on the page.

Right, I understand that it is URL-based. But how can I copy the resultant data? *That* is the part I really need help automating. How can I get VBA to copy the table so that I can have it pasted to my workbook?






Yes, click Join this Group, and join. No spam, no fuss. Randy H. is an individual who's very helpful, responsive and generous in giving his code out. I haven't looked at his code in detail but it uses techniques which can be adapted.
There are instructions, files etc. on the site.

Ok. Thanks for clarifying p4scal! I will check it out.

[Shred Dude]

Once you have access to the HTML source, you just have to identify the appropriate table, and then read it's contents. The innertext property of each cell will give you the text that is in the table.

Attached is a workbook that mocks up your scenario. Yellow highlighted cells are set up for your inputs. Click the Get Data button to retrieve the table.

The code includes two methods for achieving the task.

EDIT: Workbook uses Early Binding. Will need to create a Reference to the "Microsoft HTML Object Library" to use the HTML Document method in the code.