Website

We had seen that thanks to hyperlinks it is possible to click on a link to open a website. We will see here that we can use several options of web browsers using internet explorer in a VBA macro.

Load data from an URL link

How can you retrieve data on a web page?

For this you can use the QueryTable wich object represents a worksheet table built from data returned from an external data source, such as a SQL server or a Microsoft Access database, here it will be though an URL.

When using the Add method of the QueryTables object, you must specify as an argument the connection, here the URL link, as well as the destination where we will display the retrieved data. You can then format the defined QueryTable object.

Sub RecoverDataFromURLLink()
'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

To automatically connect to your Facebook account using VBA, first, you must create an internet explorer application. Once you have created this object you can use all the methods and properties associated with the internet explorer object. The Navigate method will allow you to access an internet site by its address. Then, using the Document.all.Item properties you can access to the fields to enter your email address as well as your password. You can be connected using the login button item and the click action.

Sub FacebookConnection()

    Dim IE As Object
    Dim sUser As String, sPass As String
    
    sUser = "VBASKILLS"
    sPass = "GOT987!"
    
    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, after creating the internet explorer object, you can use the Navigate method and the link below.

Sub SearchOnGoogle()
'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

We can also launch the Google Chrome application using the Shell.

Sub UseShellOpenGoogleChrome ()
'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 internet

As for formatting an email, we have seen that it is possible to write HTML code through VBA modules. We can also write HTML code on a test web page, using the internet explorer object and the following method Document.Body.Innerhtml.

Sub WriteHtmlInAWebPage()

    'Prepare the html 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 web page blank
    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 )

Google photo

You are commenting using your Google 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