Excel VBA IE - How can I change position of Header on website?

Question

I wrote a script(macro), which downloads a file but some headers are not in order, they suppose to be in order as a default but I want to choose this order. I can drag and drop HTML elements in Developer Tools, but I want to do How can I change position of HTML Elements with its columns? Also, I added HTML output example

--- MY Code ---
Option Explicit
Dim HTMLDoc As HTMLDocument
Dim login As InternetExplorer

Sub Login_Website()

Dim oHTML_Element As IHTMLElement
Dim login_URL As String
On Error GoTo Err_Clear
' This is login URL to website
login_URL = "Website URL"
Set login = New InternetExplorer ' The object for the login
' Timeout set for the web browser
login.Silent = True
login.timeout = 60

' This parts making Visible or not_Visible
login.Visible = True

Do
' Wait till the Browser is loaded
Loop Until login.readyState = READYSTATE_COMPLETE

Set HTMLDoc = login.document
HTMLDoc.all.os_username.Value = "username"
HTMLDoc.all.os_password.Value = "password"
HTMLDoc.all.Item("login").Click

' To submit Login
For Each oHTML_Element In HTMLDoc.getElementsByTagName("login")
If oHTML_Element.Type = "submit" Then oHTML_Element.Click: Exit For
Next

Do Until login.readyState = READYSTATE_COMPLETE: DoEvents: Loop

--- END : My Code ---
<issuetable-web-componet resolved="">
<table id="issuetable">
<thead>
<tr class="rowHeader">
<th class="colHeaderLink sortable headerrow-issuekey" data-id="issuekey"     
rel="issuekey:ASC">
  <span title="Sort By Key">Key</span>
</th>
<th class="colHeaderLink sortable headerrow-summary"  data-id="summary"     
rel="summary:ASC">
 <span title="Sort By Summary">Summary</span>
 </th>
<th class="colHeaderLink sortable headerrow-status"  data-id="status" 
 rel="status:DESC">
 <span title="Sort By Status">Status</span>
 </th>
<th class="colHeaderLink headerrow-actions"></th>
</tr>
</thead>
</table>
</issuetable-web-component>
------------------------

This is view of labels on the website. 

Current output: on website
| Issuekey | Summary | Status|
    2           3        4
    1           2        3

what I want : Columns and header change same time
| Summary  |Status | Issuekey|
    3         4          2      
    2         3          1
Header with columns position must change as well

--- HTML part should change like this
<issuetable-web-componet resolved="">
<table id="issuetable">
<thead>
<tr class="rowHeader">
<th class="colHeaderLink sortable headerrow-summary"  data-id="summary"     
rel="summary:ASC">
 <span title="Sort By Summary">Summary</span>
 </th>
<th class="colHeaderLink sortable headerrow-status"  data-id="status" 
 rel="status:DESC">
 <span title="Sort By Status">Status</span>
 </th>
<th class="colHeaderLink sortable headerrow-issuekey" data-id="issuekey"     
rel="issuekey:ASC">
  <span title="Sort By Key">Key</span>
</th>
<th class="colHeaderLink headerrow-actions"></th>
</tr>
</thead>
</table>
</issuetable-web-component>

Is it just the headers? Not swopping the content underneath. Are the headers known in advance? Can you include the part of the html as well? — QHarr, Jul 13 '19 at 10:31
Must all the html change as well? Or just what people see on the page? — QHarr, Jul 13 '19 at 14:15

QHarr · Accepted Answer · 2019-07-13T21:19:09.080

1

Here is a way using replaceChild method to "move" 2 to 1 then appendChild 1 at end of table. I use an example webpage. I originally wrote as javascript but it doesn't seem to like the appendChild when running via .execScript. I'm doing the same column re-ordering you want.

Note: Unless you have access to the source you are not actually changing the page in a permanent way. You could always save the changes to an offline document for other purposes.

In your case you would switch url and also css selector would be

cssSelector = "#issuetable tr:nth-of-type(" & r & ") " & tag

And this:

For Each tr In .getElementById("customers").getElementsByTagName("tr")

Would be:

For Each tr In .getElementById("issuetable").getElementsByTagName("tr")

Example code:

Option Explicit

Public Sub SwopColumns()
    'replaceChild(newChild, oldChild)
    Dim ie As InternetExplorer
    Set ie = New InternetExplorer
    With ie
        .Visible = True
        .Navigate2 "https://www.w3schools.com/html/html_tables.asp"

        While .Busy Or .readyState < 4: DoEvents: Wend

        Dim tr As Object, r As Long, tag As String, html As HTMLDocument
        Dim cssSelector As String, oldChild As HTMLTableCell, parent As HTMLTableRow

        Set html = .document   
        With html             
            For Each tr In .getElementById("customers").getElementsByTagName("tr")
                r = r + 1
                tag = IIf(r = 1, "th", "td")
                cssSelector = "#customers tr:nth-of-type(" & r & ") " & tag
                Set oldChild = .querySelector(cssSelector)
                Set parent = oldChild.ParentNode
                parent.replaceChild parent.getElementsByTagName(tag)(1), oldChild
                parent.appendChild oldChild
            Next
        End With
        Stop '<== delete me later
        .Quit
    End With
End Sub

edited Jul 13 '19 at 21:19

answered Jul 13 '19 at 10:43

QHarr

83,427
12
54
101

This code only worked with changing Header position, columns didnt change with Headers. I try to understand your explanation for the column. How will I change column with header position? – Nobody Jul 13 '19 at 11:07
Added a more detailed example. – QHarr Jul 13 '19 at 11:15
I am trying to merge two code at the moment, but last code is it working at excel? because when i see the activesheet, i am not using the sheet, just pulling data from website. – Nobody Jul 13 '19 at 11:24
? It is pulling from IE into the Activesheet in the order you want for accessing columns. – QHarr Jul 13 '19 at 11:25
Sorry, i will just clarify it, I will just swap html order. when i swap html title order so the headers will change as well. at your first code, it only swapping the headers but the columns also swap under the same header. – Nobody Jul 13 '19 at 11:28
You want to swop the entire html or just the values? – QHarr Jul 13 '19 at 11:30
I also added in main code how HTML should be, because I just want to swap columns position – Nobody Jul 13 '19 at 11:38
I am not able see link https://pastebin.com/rKma6bDU i try to connect with different network, the page is not possible to load – Nobody Jul 13 '19 at 11:47

Excel VBA IE - How can I change position of Header on website?

1 Answers1