Download table from web page
In Access 2010 using VBA I have this procedure to pull a table from website page and download table to access database.
Public Function GetPage(ByVal URL As String) As String
Dim oXMLHttpRequest As New MSXML2.XMLHTTP
oXMLHttpRequest.Open "GET", URL, False
oXMLHttpRequest.send
GetPage = oXMLHttpRequest.responseTe
End Function
Private Sub cmdPullDataFromWSiteTable_
Dim rawHtml As String, tableChunk As String, tempFile As String
Dim tmpAt As Long, tableStart As Long, tableEnd As Long
rawHtml = GetPage("http://www.exampleSite.aspx")
' Search forward until we're just before the table we want
tmpAt = InStr(1, rawHtml, "Something close to table start tag - example")
' Get the index of the start of the opening <table> tag
tableStart = InStr(tmpAt, rawHtml, "<table")
' Get the index of the end of the closing </table> tag
tmpAt = InStr(tableStart, rawHtml, "</table")
tableEnd = InStr(tmpAt, rawHtml, ">")
' Extract the table
tableChunk = Mid(rawHtml, tableStart, tableEnd - tableStart +1)
' Use native VBA file I/O
tempFile = "F:\tempTable.Html"
Open tempFile For Output As #1
Write #1, tableChunk
Close #1
' Import the file to a table
DoCmd.TransferText acImportHTML, , "TestTable", tempFile, True
' Delete the temp file
Kill tempFile
End Sub
This works on site where I could see tags for particular table.
I have problem with next site, site where I can see table in browser IE but when I open source view I can not find table tags in source code.
I need to download terminal rack prices table with all those locations. I do not need legend.
http://www.shell.ca/en/products-services/shell-for-drivers/pricing/app-rack-pricing.html#vanity-e5ecc261fa2a0cdf4b70caad979a7511
Is it possible to use same steps (procedure or similar) as I did on site where I can see particular table tags in source code???
Public Function GetPage(ByVal URL As String) As String
Dim oXMLHttpRequest As New MSXML2.XMLHTTP
oXMLHttpRequest.Open "GET", URL, False
oXMLHttpRequest.send
GetPage = oXMLHttpRequest.responseTe
End Function
Private Sub cmdPullDataFromWSiteTable_
Dim rawHtml As String, tableChunk As String, tempFile As String
Dim tmpAt As Long, tableStart As Long, tableEnd As Long
rawHtml = GetPage("http://www.exampleSite.aspx")
' Search forward until we're just before the table we want
tmpAt = InStr(1, rawHtml, "Something close to table start tag - example")
' Get the index of the start of the opening <table> tag
tableStart = InStr(tmpAt, rawHtml, "<table")
' Get the index of the end of the closing </table> tag
tmpAt = InStr(tableStart, rawHtml, "</table")
tableEnd = InStr(tmpAt, rawHtml, ">")
' Extract the table
tableChunk = Mid(rawHtml, tableStart, tableEnd - tableStart +1)
' Use native VBA file I/O
tempFile = "F:\tempTable.Html"
Open tempFile For Output As #1
Write #1, tableChunk
Close #1
' Import the file to a table
DoCmd.TransferText acImportHTML, , "TestTable", tempFile, True
' Delete the temp file
Kill tempFile
End Sub
This works on site where I could see tags for particular table.
I have problem with next site, site where I can see table in browser IE but when I open source view I can not find table tags in source code.
I need to download terminal rack prices table with all those locations. I do not need legend.
http://www.shell.ca/en/products-services/shell-for-drivers/pricing/app-rack-pricing.html#vanity-e5ecc261fa2a0cdf4b70caad979a7511
Is it possible to use same steps (procedure or similar) as I did on site where I can see particular table tags in source code???
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Since there is a download CSV link, it isn't clear how their terms and conditions apply.
Taras,
...as I am reading this, ...it is not even clear if there is a "Download CSV' link...?
...did I miss something?
In any event, downloading a csv file may be allowed because the site has control over what is actually being downloaded,
...but using code to "scrape" an entire table (without the site's permission, knowledge or consent,) may not be allowed. (as Scott mentioned...)
What we need to know here is if you are "allowed" to do this, ...by expressed permission of the site.
If so, then typically the site will typically provide a more elegant way of getting the data...
(VPN, FTP, custom Export system, ...etc)
The fact that your code works on one site, ...but not on another, points to perhaps the other site not wanting this info to be available/downloaded...
The code you are using there may be interpreted as "Hacking", ...and as such, we at this site are not allowed to respond to such questions...
JeffCoachman
JeffCoachman
...as I am reading this, ...it is not even clear if there is a "Download CSV' link...?
...did I miss something?
In any event, downloading a csv file may be allowed because the site has control over what is actually being downloaded,
...but using code to "scrape" an entire table (without the site's permission, knowledge or consent,) may not be allowed. (as Scott mentioned...)
What we need to know here is if you are "allowed" to do this, ...by expressed permission of the site.
If so, then typically the site will typically provide a more elegant way of getting the data...
(VPN, FTP, custom Export system, ...etc)
The fact that your code works on one site, ...but not on another, points to perhaps the other site not wanting this info to be available/downloaded...
The code you are using there may be interpreted as "Hacking", ...and as such, we at this site are not allowed to respond to such questions...
JeffCoachman
JeffCoachman
@Jeff
The download CSV button is inside the frame in the middle of the page. It is scrollable content and easy to miss.
The download CSV button is inside the frame in the middle of the page. It is scrollable content and easy to miss.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your help I am allowed to donwnload csv file and I will use it. I did not mean to ask for help to do something that is not permited.And Jeff have point in considering that code is not visible,must be reason for it.
ASKER
I was thinking about it. How to do it from VBA in this particular case.?