Breaking down HTML into VBA functions in Excel

[Kuljack]

Hello forum,

Using MS Excel 2010.


I have found some code that I'm modifying for my purposes, bit by bit. However, I'm still not fluent in how HTML code from a webpage translates to actions in Excel via the functions. Looking to maybe seek some assistance with how to understand the below HTML and convert the action into a VBA function.

I am modifying the content of course for privacy purposes, I hope this doesn't impact the solution:

HTML piece that involves the action I want to take on a web page:

[COLOR="#FF0000"]menuGroupId[/COLOR][COLOR="#0000FF"] ="300">< [/COLOR][COLOR="#B22222"]/ie:menuitem[/COLOR]><[COLOR="#B22222"]ie:menuitem [/COLOR][COLOR="#FF0000"]id[/COLOR][COLOR="#0000FF"]="zz17_ExportToSpreadsheet" [/COLOR]
[COLOR="#FF0000"]type[/COLOR][COLOR="#0000FF"]="option" [/COLOR][COLOR="#FF0000"]iconSrc[/COLOR][COLOR="#0000FF"]="/_layouts/images/MenuSpreadsheet.gif" [/COLOR]
[COLOR="#FF0000"]onMenuClick[/COLOR][COLOR="#0000FF"]= "Here there is a long bit of javascript actions, remitting for privacy"[/COLOR]
[COLOR="#FF0000"]text[/COLOR][COLOR="#0000FF"]="Export to Spreadsheet" [/COLOR]
[COLOR="#FF0000"]description[/COLOR][COLOR="#0000FF"]="Analyze items with a spreadsheet application." [/COLOR]

Below, I have the follow code provided from the following site:

Working with Internet Explorer Using VBA - Collection of VBA Code Snippets and Useful Excel Knowledge

Sub ExplorerTest()
Const myPageTitle As String = "Release Management"
Const myPageURL As String = "My website, remitted for privacy"

{Here is where my strings/actions should go I belive}


Dim myIE As SHDocVw.InternetExplorer

  'check if page is already open
  Set myIE = GetOpenIEByTitle(myPageTitle, False)
  
  If myIE Is Nothing Then
    'page isn't open yet
    'create new IE instance
    Set myIE = GetNewIE
    'make IE window visible
    myIE.Visible = True
    'load page
    If LoadWebPage(myIE, myPageURL) = False Then
      'page wasn't loaded
      MsgBox "Couldn't open page"
      Exit Sub
    End If
  End If
  
  With myIE.Document.forms()

   {Here is where the actions go I believe}
    
  End With
       
End Sub

There are a few functions used within this code, they are broken out below:

Function GetOpenIEByTitle(i_Title As String, _
                          Optional ByVal i_ExactMatch As Boolean = True) As SHDocVw.InternetExplorer
Dim objShellWindows As New SHDocVw.ShellWindows

  If i_ExactMatch = False Then i_Title = "*" & i_Title & "*"
  'ignore errors when accessing the document property
  On Error Resume Next
  'loop over all Shell-Windows
  For Each GetOpenIEByTitle In objShellWindows
    'if the document is of type HTMLDocument, it is an IE window
    If TypeName(GetOpenIEByTitle.Document) = "HTMLDocument" Then
      'check the title
      If GetOpenIEByTitle.Document.Title Like i_Title Then
        'leave, we found the right window
        Exit Function
      End If
    End If
  Next
End Function

Function GetOpenIEByURL(ByVal i_URL As String) As SHDocVw.InternetExplorer
Dim objShellWindows As New SHDocVw.ShellWindows

  'ignore errors when accessing the document property
  On Error Resume Next
  'loop over all Shell-Windows
  For Each GetOpenIEByURL In objShellWindows
    'if the document is of type HTMLDocument, it is an IE window
    If TypeName(GetOpenIEByURL.Document) = "HTMLDocument" Then
      'check the URL
      If GetOpenIEByURL.Document.URL = i_URL Then
        'leave, we found the right window
        Exit Function
      End If
    End If
  Next
End Function

Function LoadWebPage(i_IE As SHDocVw.InternetExplorer, _
                     i_URL As String) As Boolean
  With i_IE
    'open page
    .Navigate i_URL
    'wait until IE finished loading the page
    Do While .ReadyState <> READYSTATE_COMPLETE
      Application.Wait Now + TimeValue("0:00:01")
    Loop
    'check if page could be loaded
    If .Document.URL = i_URL Then
      LoadWebPage = True
    End If
  End With
End Function

Function GetNewIE() As SHDocVw.InternetExplorer
  'create new IE instance
  Set GetNewIE = New SHDocVw.InternetExplorer
  'start with a blank page
  GetNewIE.Navigate2 "about:Blank"
End Function

Hopefully someone can make sense of this as I am utterly lost, and only testing theories off what I know but success has not been my product.

Thank you all!

 

[Kuljack]

Hate to reply on my own post prior to a response but if anyone has a better approach for pulling up the website that'd be great too.


I have a code that works fine, but I liked the approach of addressing if the page opens or not.

Simple code for pulling up a webpage:

Dim IE As Object

    Set IE = CreateObject("InternetExplorer.Application")

    IE.Visible = True
[COLOR="#00FF00"]    'Go to this Web Page![/COLOR]
    IE.Navigate "http://www.google.com"