VBA + Selenium Copy text web page into excel

[bunburyst]

Hi guys,

I don't understand the Selenium language yet and would need to adapt a macro that I used in IE to be able to use it in Edge. I have installed selenium basic and edge webdriver.
My macro copied the text from a web page and pasted it into an excel cell. I would appreciate any help.

Thanks a lot.

My macro is:

Public Declare PtrSafe Function ShowWindow& Lib "user32" _
(ByVal hwnd As Long, ByVal nCmdShow As Integer)
 
Sub LoginWebReports2()
     
    Dim ie As Object
     
  
     
    Set objWSS = CreateObject("WScript.Shell")
    Set ie = CreateObject("InternetExplorer.Application")
     
                 
                With ie
                    .Visible = True
                    .navigate "https://www.commodity3.com/content/41/energy-news"
                     Application.Wait Now + TimeValue("0:00:05")
                     Do While .Busy: DoEvents: Loop
                        Do While .readyState <> 4: DoEvents: Loop
                        End With
                         
                        ie.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DODEFAULT
                        ie.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT
                                             
                         Windows("Precios.xlsm").Activate
                         Sheets("Hoja1").Select
                         Range("A1").Select
                        ActiveSheet.Paste
                         
                   
                     
                    Set ie = Nothing
                     
                    Exit Sub
                   
                End Sub

 

[Dan_W]

What have tried thus far?

 

[diddi]

a simple XMLHTTP rip will grab that text. it is just a big lump right at the end with all the news. no need to set up selenium i dont think at first inspection anyway

 

[Dan_W]

I agree with Diddi. Whenever someone mentions Selenium, I automatically assume that there must be some Javascript element to the site, but looking at the URL you provided, it seems like XMLHTTP would suffice. The following code should work. Let me know if you have any problems with it.

Sub RetrieveText()

    Dim URL As String, StartCell As Range, strText As String
   
    ' Set target URL
    URL = "https://www.commodity3.com/content/41/energy-news"
   
    ' Retrieve text form the website
    strText = GetWebText(URL)
   
    Set StartCell = Application.ActiveWorkbook.Sheets("Hoja1").Range("A1")
   
    ' Check to see if there are any new lines in the text. If there are,
    ' split the text into an array, and paste the array to the worksheet.
    If InStr(strText, vbCrLf) Then
        vartext = Split(strText, vbCr)
        Set TextRange = StartCell.Resize(UBound(vartext), 1)
        With TextRange
            .Value = WorksheetFunction.Transpose(vartext)
            .WrapText = False
        End With
    Else
        StartCell.Value = strText
    End If

End Sub
Function GetWebText(URL As String) As String
   
    On Error GoTo ErrHandler
    Dim XMLObject As Object, HTML As Object

    ' Contact server for URL
    Set XMLObject = CreateObject("MSXML2.XMLHTTP")
    XMLObject.Open "GET", URL, False
    XMLObject.send
   
    ' If request to server successful, create HTMLDocument to
    ' render HTML code into text
    If XMLObject.Status = 200 Then
        Set HTML = CreateObject("htmlfile")
        HTML.body.innerhtml = XMLObject.responseText
        GetWebText = HTML.body.innertext
        Else: GetWebText = ""
    End If
   
' Handle any errors.
ErrHandler:
    If Err.Number Then Debug.Print Err.Number & " - " & Err.Description
    Set XMLObject = Nothing
    Set HTML = Nothing
End Function

 

[bunburyst]

Thanks to @diddi and @Dan_W , it works!. y
You are very kind