May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.
Sub Macro1() ' ' Macro1 Macro ' Dim IE As Object, obj As Object Dim myState As String Dim r As Integer, c As Integer, t As Integer Dim elemCollection As Object Dim accountno As String 'add the "Microsoft Internet Controls" reference in your VBA Project indirectly Set IE = CreateObject("InternetExplorer.Application") 'more variables for the inputboxes - makes our automation program user friendly 'accountno = InputBox("Enter the Transferring Account No") With IE .Visible = True .navigate ("http://ec.europa.eu/environment/ets/transaction.do") ' we ensure that the web page downloads completely before we fill the form automatically While IE.ReadyState <> 4 DoEvents Wend 'accessing the Transferring account number into the correct field IE.Document.All.Item("originatingAccountNumber").innertext = "1234" ' accessing the button IE.Document.getElementsByName("search").Item.Click ' again ensuring that the web page loads completely before we start scraping data Do While IE.busy: DoEvents: Loop 'Clearing any unnecessary or old data in Sheet1 ThisWorkbook.Sheets("Sheet1").Range("A1:K500").ClearContents '???this is where I get stuck Set elemCollection = IE.Document.getElementsByTagname("????") For t = 0 To (elemCollection.Length - 1) For r = 0 To (elemCollection(t).Rows.Length - 1) For c = 0 To (elemCollection(t).Rows(r).Cells.Length - 1) ThisWorkbook.Worksheets(1).Cells(r + 1, c + 1) = elemCollection(t).Rows(r).Cells(c).innertext Next c Next r Next t End With ' cleaning up memory Set IE = Nothing End Sub
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
|Excel Macro update each line based on another worksheet||2||54|
|ENTER A NUMBER INTO A CELL AND POPULATE OTHER CELLS BASED ON WHAT WAS ENTERED IN EXCEL PART 2||6||46|
|sort time order||10||45|
Join the community of 500,000 technology professionals and ask your questions.