Passing Data through a Web Query

I run frequent searches where I try to get information from a web query.  I have to run a web query and then copy and paste the data out of the search result.  Is there a way to pass the data to a web query and directly into a table.

Link to Site: http://tinyurl.com/nkcxprj

Input:
Operator & County
Output:
All Data in 13 Columns & Multiple Pages of the output result

Input Source: MS Access, MS Excel, Other if I am missing an easier option
Output Source:Spreadsheet, CSV, directly MS Access, open to suggestions

I do not even know the proper terminology to get to the right tutorials to learn how to do this.  

I am open to suggestions on how to achieve this.
AdrianMEAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
You can download each page to, say, twenty files if twenty pages.

Then open Access, go to External data, Other, select HTML-file.
Select to link tables from the files.

However, when I do this with A2013, an error stops the import wizard, I don't know why.

/gustav
0
AdrianMEAuthor Commented:
This is similar to what I am trying to do but it doesn't show the VBA code to do it.

https://www.youtube.com/watch?v=Z9N60AIZQNE


This video shows the VBA code but when I try to do the code in Office 2013 it get's hung up
https://www.youtube.com/watch?v=7sZRcaaAVbg

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = Range("zipCode").Row And _
Target.Column = Range("zipCode").Column Then
Dim IE As New InternetExplorer
IE.Visible = True
IE.navigate "http://quickfacts.census.gov/cgi-bin/qfd/lookup?place=" & Range("zipCode").Value
End If
Do
DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE
Dim Doc As HTMLDocument
Set Doc = IE.document

Dim sDD As String
sDD = Trim(Doc.getElementsByTagName("dd")(0).innerText)
sDD = Split(sDD, vbNewLine)(0)
Range(“city”).Value = Split(sDD, ”, “)(0)
Range(“county”).Value = Split(sDD, ”, “)(1)
Range("D6").Value = sDD
MsgBox sDD

End Sub

Open in new window


Gustav,

I tried linking the tables as well but Access was giving me an error message.  I am leaning toward finding a way to get VBA to make the web query for me and export the tables automatically.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Well, the main issue is that the queried site needs to allow for providing search parameters via URL (if using the Web Query feature) or filling out the form by using a InternetExplorer object and getElementByID or other IHTMLDocument methods. The later again makes it more complex, as we have to generate the resulting tables ourself, while a Web Query results in a ready-to-use table (e.g. in Excel).

On another note, if you provide a operator number, you don't need a county anymore - the OP is unique.

To clarify:
You need a part reading OP Numbers from a source,
stuff it into the web site search, and start the search
extract the resulting table into a suitable file format, iterating if there are more pages.

That is nothing to do easily. Maybe scripting solutions using e.g. curl or some regex may help.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
Have you tried to address the site owner? You may very well not be the first to have this demand, and perhaps they have other offerings than browsing the site. At a cost, perhaps, but it would not be simple to automate a solution to download these data.

/gustav
0
AdrianMEAuthor Commented:
This is what I was looking for so that I can break up the problem into parts.
"
To clarify:
You need a part reading OP Numbers from a source,
stuff it into the web site search, and start the search
extract the resulting table into a suitable file format, iterating if there are more pages."
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.