Website

Last Updated on December 28, 2021 – 2 min read

In this article, you will discover several options of web browsers using internet explorer in a VBA macro.


How can you retrieve data on a web page? 

You can use the QueryTable object which represents a worksheet table built from data returned from an external data source, such as a SQL server or a Microsoft Access database.

When using the Add method of the QueryTables object, you must specify as an argument the connection, the URL link corresponding to the destination of your data. 

You can format the QueryTable object.

Sub Recover_Data_From_URL_Link()
'Recover Data and paste it in a wbk sheet
'from an URL Link

   Application.ScreenUpdating = False
   Application.DisplayAlerts = False

   Dim MyConn As QueryTable
   Dim URLLink, URLWeb As String

   Sheets("Database weather").Cells.ClearContents
   URLWeb = Sheets("Home").Cells(5, 5).Value2 'https link
   URLLink = "URL;" & URLWeb

   Set MyConn = Sheets("Database weather").QueryTables.Add(Connection:=URLLink, _
      Destination:=Sheets("Database").Range("A1"))

   With MyConn
      .BackgroundQuery = True
      .TablesOnlyFromHTML = True
      .SaveData = True
      .Refresh BackgroundQuery:=True
   End With

   Application.ScreenUpdating = True
   Application.DisplayAlerts = True

End Sub

Connect to Facebook

You can connect to your Facebook account automatically using VBA.

Create an internet explorer application and use all methods and properties associated with the internet explorer object.

The Navigate method allows you to access an internet site by its address.

You can use Document.all.Item properties to access all necessary fields for your email address login and your password.

Use the login button item and the click action to sign in.

Sub Facebook_Connection()

    Dim IE As Object
    Dim sUser As String, sPass As String
    
    sUser = "VBASKILLS"
    sPass = "GoT__987_?a24!"
    
    Set IE = CreateObject("InternetExplorer.Application")
    
    IE.Navigate ("http://www.facebook.com/")
    IE.Visible = True
    
    Do While IE.Busy: DoEvents: Loop
    
    Application.Wait (Now + TimeValue("00:00:02"))

    IE.Document.all.Item("email").Value = sUser
    IE.Document.all.Item("password").Value = sPass
    IE.Document.all.Item("loginbutton").Click
    
End Sub

Search on Google

To launch research on Google, you can use the Navigate method after creating the internet explorer object.

Sub Search_On_Google()
'Do a research on Google

    Dim IE As Object
    Dim Website As String
    
    'Create an internetExplorer Object
    
    Set IE = CreateObject("InternetExplorer.Application")

    'It will search on google with ie the value of your variable
    Website = "The times"

    'Search with navigate
    IE.navigate ("http://www.google.com/search?q=" & Website)

End Sub

You can also launch the Google Chrome application using Shell.

Sub Use_Shell_Open_Google_Chrome ()
'Run Google Chrome using the Shell

    Dim ChromePath As String

    ChromePath = """C:\...\Google\Chrome\Application\chrome.exe """
    Shell(ChromePath)

End Sub

Write HTML code on web pages

We have seen in the Outlook article that it is possible to write HTML code through VBA modules. You can also write HTML code on a test web page by using the internet explorer object and the following method Document.Body.Innerhtml.

Sub Write_Html_Web_Page()

    'Prepare the HTML code in a string
    Text = Text & "<center><p><h1><font color =red><i><b> Warning ! </b></i></font></p></center>"
    Text = Text & "<p><b><font color = blue> Happy New Year <font color = red>  !!! </font> To all ! </font></b></p>"
    Text = Text & "<p><font color = green> Best wishes! </font></p>"
    Text = Text & "<center><h2><font color = bluecyan><i><B> From VBASkills </b> Instagram </i></font></h2></center>"
    
    'Open a blank web page
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = True
    IE.navigate "about:blank"
    'Read the Html in this format
    IE.document.body.innerhtml = texte

End Sub

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s