VBA + Selenium Copy text web page into excel

bunburyst

New Member
Joined
Apr 18, 2018
Messages
19
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:

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

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Dan_W

Active Member
Joined
Jul 11, 2018
Messages
475
Office Version
  1. 365
Platform
  1. Windows
What have tried thus far?
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,326
Office Version
  1. 2010
Platform
  1. Windows
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

Active Member
Joined
Jul 11, 2018
Messages
475
Office Version
  1. 365
Platform
  1. Windows
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.

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

Watch MrExcel Video

Forum statistics

Threads
1,132,646
Messages
5,654,567
Members
418,142
Latest member
peterappiahkubi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top