Vba code to copy data from online tool to Excel

[Anantha Krishna]

Hello All,

I need your help for the below requirement.

Once we open the page we have many Serial numbers, for each serial number there will be status and comment .

So based on serial number we have to copy comment from webpage and paste it to excel.

i have attached the screenshots of webpage and webelement.

Regards,
Anantha

 

[Anantha Krishna]

Can anybody help me on this please?

 

[Marc L]

A must read (in particular How to get the Best Results) :​

​

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

 

[Anantha Krishna]

Hello Marc,

Hope which i have given information is not enough to understand situation.

Till main page i am using below code, after this i need coding as i explained on 1st quote.

In Excel Serial Number column we have serial numbers which we need to search in webpage and for that serial number what we have status, based on that comment will be there, so that comment we have to paste it in Excel comment column.

On webpage for each serial number we have in table format.

Eg: Serial number - TH85U5G1GC , and Status - Sytstem Reject, then what comment we have under Internal comment "Invoice to partner Ingram Micro (UK)Ltd"

This comment i need to paste in excel on Comment column.

Sub PECtool()
Dim appIE As InternetExplorerMedium
'Set appIE = Nothing
Dim objElement As Object
Dim objcollection As Object

Set appIE = New InternetExplorerMedium

sURL = "webpage URL"
With appIE
    .Navigate sURL
    .Visible = True
End With

Do While appIE.Busy Or appIE.ReadyState <> 4
    DoEvents
Loop

       Application.Wait Now + TimeSerial(0, 0, 5)
  
    Set objcollection = appIE.Document.getElementsByTagName("input")
  
   i = 0
    While i < objcollection.Length
        If objcollection(i).Type = "text" Then

            ' Set text for search
            objcollection(i).Value = "E-GBDOA1326312"
        
        End If
        If objcollection(i).ID = "Submit" Then
        objcollection(i).Click
        
        End If
i = i + 1
    Wend
  
Do While appIE.Busy Or appIE.ReadyState <> 4
    DoEvents
Loop
Application.Wait Now + TimeSerial(0, 0, 5)


Set appIE = Nothing
MsgBox (done)
End Sub

Can please help me on this.

Thanks in Advance,
Anantha

 

[Anantha Krishna]

Hello All,

Can anyone help me on the above situation, if any details i need to provide means kindly let me know.

Thanks in Advance,
Anantha

 

[Marc L]

Very difficult to help without a webpage access !​

​

So from your webbrowser inspect the desired elements so see if they have an ID, a name, …​

​

 

[Anantha Krishna]

Hello Marc,

Thanks for your response, the thing i am not getting element id or name for this table.

I have attached element screenshots and webpage screenshot, with this it is possible to help me.

Regards,
Anantha

 

[Marc L]

Without access I just can give some directions …​

• When an element doesn't have an ID, search back to the object model to find out a parent element with one.

• In the case of an HTML table, if the desired element is always at the same place, you can directly access to it

just using some property of the table like Cells, Rows, … To see in the DOM (Document Object Model).​

​

See also the samples on forums.​

​

 

[Anantha Krishna]

Thanks for your suggestion Marc.

 

[Marc L]

To reach a table in a web document, you may set an object variable on a tables collection via getElementsByTagName("TABLE")​

then with a break point you can inspect this object in order to find out the index of the desired table if the tables order never changes …​

​

Then with a data always in the same place within the table, you just have to find out the row index then the cell index​

as Rows is a direct collection of a table object and Cells collection is inside any item of Rows collection …​

So within the DOM, the property data for a cell of a table object is Tables(x).Rows(y).Cells(z).innerText … (x, y & z as indexes)​

​

 

[Anantha Krishna]

Thank you so much Marc, I will try this