• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vba code to copy data from online tool to Excel

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
 

Attachments

  • Datacopy.JPG
    Datacopy.JPG
    61.1 KB · Views: 12
  • element1.JPG
    element1.JPG
    75.5 KB · Views: 9
  • element2.JPG
    element2.JPG
    56.2 KB · Views: 7
A must read (in particular How to get the Best Results) :​
 
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.

Code:
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
 

Attachments

  • sample.xlsx
    10.2 KB · Views: 0
Last edited by a moderator:
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
 
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, …​
 
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
 

Attachments

  • Serial# Value.JPG
    Serial# Value.JPG
    38.8 KB · Views: 3
  • serial#.JPG
    serial#.JPG
    51.9 KB · Views: 3
  • Status1.JPG
    Status1.JPG
    128.8 KB · Views: 3
  • webview.JPG
    webview.JPG
    69.1 KB · Views: 3
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.​
 
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)​
 
Back
Top