Set tRow = tSection.getElementsByTagName("tr") Set tBody = hTable.getElementsByTagName("tbody") Set headers = hTable.getElementsByTagName("th") R = startRow: If ws Is Nothing Then Set ws = ActiveSheetĭim headers As Object, header As Object, columnCounter As Long Public Sub WriteTable(ByVal hTable As HTMLTable, Optional ByVal startRow As Long = 1, Optional ByVal ws As Worksheet)ĭim tSection As Object, tRow As Object, tCell As Object, tr As Object, td As Object, r As Long, C As Long, tBody As Object WriteTable hTable, 1, ThisWorkbook.Worksheets("Sheet1") setRequestHeader "If-Modified-Since", "Sat, 00:00:00 GMT" 'to deal with potential caching As we only want data from columns 1 and 8, a Select Case statement is used specify what is written out to the sheet.ĭim html As HTMLDocument, hTable As HTMLTable ' References > Microsoft HTML Object Library In the helper sub, WriteTable, we loop the columns ( td tags) and then the table rows ( tr tags), and finally traverse the length of each table row, table cell by table cell. In the below code, the table is grabbed by its id cr1. Note that javascript rendered/dynamically added content will not be retrieved as there is no javascript engine running (which there is in a browser). The server response can be read into an HTMLDocument and the process of grabbing the table continued from there. It's a fast method for retrieving data that doesn't require opening a browser. XHR is an API in the form of an object whose methods transfer dataīetween a web browser and a web server. Let's park the local bus browser opening, and fly with an XMLHTTP GET request (XHR GET for short). Other methods were mentioned so let us please acknowledge that, at the time of writing, we are in the 21st century. Because I have learned how to program thanks to this community, and I like to think that you and other beginners might use my input to discover the beautiful world of programming. So I hope that this answer, which is just a "study input" and not at all the best/most complete solution, can be a support for next user having your same problem. The reason why I'm answering a question which is not showing too much research effort is just that I see it asked several times and, back to the time when I learned how to do this, I remember that I would have liked having some better support to get started with. I'd just like to point you out that this is not how StackOverflow works: here you post questions about specific coding problems, but you should make your own search first. Well, now you have your value and you can do whatever you want with it: put it into a cell ( Range("A1").Value = myValue), or into a label of a form ( Me.label1.Text = myValue). Once we have our value, which is now stored into the myValue variable, we can just close the IE browser and releasing the memory by setting it to Nothing: appIE.Quit
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |