Complete web data from Excel

[fr1ki]

Hello All.

I need to complete a price of different articles on a website and they give me the data in Excel.


I explain:


Every week they send me an Excel with different article prices and I have to update those on the web. The Excel are two columns, one with the ID field and the other with the Price being as follows:

My idea is, massively change prices on the web by pressing a button on the excel.

For them I have the following code:

Sub Datos_Web


Dim IE As Object


Set IE = CreateObject ("Internet Explorer.aplication")


IE.Navigate "https://www.00000.es/000000_edit.php?editid1=[SIZE=4][U][B]Complete with the data in column A[/B][/U][/SIZE]"


Do


DoEvents


Loop Until IE.ReadyState = 4


IE.Document.getelementbyid("value_precio_1).Value = "Price [SIZE=4][U][B]Complete with the data in column B[/B][/U][/SIZE]"


IE.Document.getelementbyid ("saveButton1").click


IE.Visible = False


End Sub

The code works for me manually changed, but I need it to go through the two columns of data and make the change massively


I finally put the code with an example of the table I put above so that you understand how it should be but with each value.

Sub Datos_Web

Dim IE As Object


Set IE = CreateObject ("Internet Explorer.aplication")


IE.Navigate "https://www.00000.es/000000_edit.php?editid1=[B]11122597[/B]"


Do


DoEvents


Loop Until IE.ReadyState = 4


IE.Document.getelementbyid("value_precio_1).Value = "Precio [B]23.5[/B]"


IE.Document.getelementbyid ("saveButton1").click


IE.Visible = False


End Sub

Let's see if you can help me.


Regards, and thank you very much.

 

[fr1ki]

Hello all.

It can not be done?


Thank you very much.

 

[Domenic]

The following macro assumes that the active sheet contains the data...

Option Explicit

Sub Datos_Web()


    Dim sourceData As Variant
    sourceData = Range("a1").CurrentRegion.Value
    
    Dim IE As Object
    Set IE = CreateObject("InternetExplorer.Application")
    
    Dim i As Long
    For i = LBound(sourceData) + 1 To UBound(sourceData) ' +1 to start at the second row
        With IE
            .Visible = True
            .Navigate "https://www.00000.es/000000_edit.php?editid1=" & sourceData(i, 1)
            Do While .busy Or .readyState <> 4
                DoEvents
            Loop
            .Document.getElementById("value_precio_1").Value = sourceData(i, 2)
            .Document.getElementById("saveButton1").Click
        End With
    Next i
    
    Set IE = Nothing


End Sub

Hope this helps!

 

[Domenic]

I have amended the code to make sure the "value_precio_1" element is found before continuing with the macro. As it stands, if 10 seconds has elapsed without finding the element, the text "N/A" is entered in the corresponding cell in Column C. You can change the seconds and column as desired.

Note, I have not tested the code. So if you run into any problems, don't hesitate to let me know.

Option Explicit

Sub Datos_Web()


    Dim lastRow As Long
    lastRow = Cells(Rows.Count, "a").End(xlUp).Row
    
    Dim IE As Object
    Set IE = CreateObject("InternetExplorer.Application")
    
    Dim element As Object
    Dim i As Long
    With IE
        .Visible = True
        For i = 2 To lastRow
            .Navigate "https://www.00000.es/000000_edit.php?editid1=" & Cells(i, "a").Value
            Do While .busy Or .readyState <> 4
                DoEvents
            Loop
            Set element = MyGetElementByID(.document, "value_precio_1")
            If Not element Is Nothing Then
                element.Value = Cells(i, "b").Value
                .document.GetElementByID("saveButton1").Click
            Else
                Cells(i, "c").Value = "N/A"
            End If
        Next i
    End With
    
    Set IE = Nothing


End Sub


Function MyGetElementByID(ByVal htmlDoc As Object, ByVal elementName As String) As Object


    Const SECS_TO_WAIT As Long = 10 'change as desired
    
    Dim startTime As Single
    startTime = Timer
    
    Dim element As Object
    On Error Resume Next
    Do
        Set element = htmlDoc.GetElementByID(elementName)
        If Not element Is Nothing Then
            Set MyGetElementByID = element
            Exit Function
        End If
        DoEvents
        If Timer - startTime > SECS_TO_WAIT Then Exit Do
    Loop
    On Error GoTo 0
    
    Set MyGetElementByID = Nothing
    
End Function

Hope this helps!

 

[fr1ki]

Hello.


Many thanks. The last code works correctly. You are a crack.

 

[Domenic]

That's great, glad I could help, and thanks for your feedback.

Cheers!