Import data to Excel 2003 with a login page

Melvin picture Melvin · Jul 16, 2012 · Viewed 8.2k times · Source

I am building a project and I understand that Excel 2003 supports the import of data from external webpages via "Data -> Import External Data -> New Web Query".

This can be done via the few steps listed here: http://www.internet4classrooms.com/excel_import.htm

However, the site I am importing data from is an internal website (Intranet) and it requires a login everytime I access it.

The website does not remember the password and everytime I hit the "import" button, it does not do anything due to the login.

How do I prompt for a username + password and login to the website while importing the data from an external website in Excel 2003?

Answer

UberNubIsTrue picture UberNubIsTrue · Jul 28, 2012

I ran into this about a year ago and as JimmyPena suggested, IE automation is probably the way to go. This is going to look much more complicated then you were expecting but believe me, I spent hours trying to find a simpler way and couldn't find one.

Take some time to learn about HTML and the DOM object. It might seem like overkill for what you are doing but it will come in handy down the road if you want to get data from websites. Here's a script to get you pointed in the right direction:

  1. Create a Userform with two textboxes and a button
  2. Textbox1 will be the username input and textbox2 will be your password
  3. You should mask the password input by selecting a password character in the properties window (Press F4 in the VBA Editor, select textbox2 from the dropdown and enter a character next to PasswordChar)
  4. Double click on the button you just created and paste in the following code:

    Option Explicit
    
    Private Sub CommandButton1_Click()
    Const READYSTATE_COMPLETE = 4
    Const tempDir As String = "C:\Windows\Temp\"
    
    Dim userName$, passWord$, URL$, s_outerhtml$ ''These are strings
    Dim IE As Object, IE_Element As Object, IE_HTMLCollection As Object
    Dim i_file% ''This is an integer
    Dim blnUsernameEntered As Boolean, blnPasswordEntered As Boolean, blnSheetFnd As Boolean
    Dim ws As Excel.Worksheet
    
    ''Test for missing username or password
    If Me.TextBox1 = vbNullString Then MsgBox "Enter a User Name", vbOKOnly, "User Name Missing": Exit Sub
    If Me.TextBox2 = vbNullString Then MsgBox "Enter a Password", vbOKOnly, "Password Missing": Exit Sub
    
    ''Set the username and password based on the userform inputs
    userName = Me.TextBox1.Value
    passWord = Me.TextBox2.Value
    
    ''Hide the form
    Me.Hide
    
    ''Enter your address to navigate to here
    URL = "http://theofficialjbfansite.webs.com/apps/auth/login"
    
    ''Create an Internet Explorer object if it doesn't exist
    If IE Is Nothing Then Set IE = CreateObject("InternetExplorer.Application")
    
    ''Make the window visible with true, hidden with false
    IE.Visible = True
    ''navigate to the website
    IE.Navigate URL
    
    '' use this loop to make wait until the webpage has loaded
    Do While IE.Busy Or IE.readyState <> READYSTATE_COMPLETE
       DoEvents
    Loop
    
    ''This is where it will get tricky - see my notes on DOM at the end of this post
    ''build a collection of input elements
    Set IE_HTMLCollection = IE.document.getElementsByTagName("input")
    ''for each html element in the "input" collection
    For Each IE_Element In IE_HTMLCollection
      If IE_Element.Name = "email" Then IE_Element.innerText = userName:   blnUsernameEntered = True
      If IE_Element.Name = "password" Then IE_Element.innerText = passWord: blnPasswordEntered = True
      If blnUsernameEntered = True And blnPasswordEntered = True Then Exit For
      ''Unblock line below if you are having trouble finding the element name,
      ''view the output in the Immediate Window (Ctrl + G in the VBA Editor)
      ''Debug.Print IE_Element.Name
    Next
    
    ''Find the form and submit it
    Set IE_HTMLCollection = IE.document.getElementsByTagName("form")
    For Each IE_Element In IE_HTMLCollection
       If IE_Element.Name = "loginForm" Then IE_Element.submit
    Next
    
    Do While IE.Busy Or IE.readyState <> READYSTATE_COMPLETE
       DoEvents
    Loop
    
    ''The next line helps ensure that the html has been fully loaded
    Application.Wait Now() + TimeValue("0:00:02")
    s_outerhtml = IE.document.body.OuterHtml
    i_file = FreeFile
    
    
    ''This is a modification of some code I found at www.tek-tips.com <--great resource
    ''the code saves a temporary copy of the webpage to your temp file
    Open tempDir & "\tempFile.htm" For Output As #i_file
    Print #i_file, s_outerhtml
    
    Close #i_file
    
    ''Creating a "Data" sheet if it doesn't exist
    For Each ws In ThisWorkbook.Worksheets
       If ws.Name = "Data" Then blnSheetFnd = True: Exit For
    Next
    
    If blnSheetFnd = False Then Sheets.Add: ActiveSheet.Name = "Data"
    
    Sheets("Data").Cells.Clear
    
    ''Here is your webquery, using the temporary file as its source
    ''this is untested in 2003, if it errors out, record a macro
    ''and replace the property that throws the error with your recorded property
    With Sheets("Data").QueryTables.Add(Connection:= _
        "URL;" & tempDir & "tempFile.htm" _
        , Destination:=Range("$A$1"))
        .Name = "Data"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingAll
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    
    ''delete the temporary file
    Kill tempDir & "\tempFile.htm"
    
    ''clean up after yourself, foo!!
    IE.Quit
    Set IE = Nothing
    Set IE_HTMLCollection = Nothing
    Unload UserForm1
    
    End Sub
    
  5. Change the URL to your website and modify the getelement methods to work with your webpage

The trickiest part for someone unfamiliar with HTML and the DOM (Document Object Model) will be finding the correct elements on the page.

A good trick is to use Internet Explorer's Developer Tool. Open up your intranet page in IE and press F12. This will open the Developer Tool. Click on the arrow icon (the arrow points up and to the left) in the toolbar and switch back to your intranet page. Hover over the page and you will see blue boxes painted around each element. Hover over the username login and click on the input box. This will highlight the HTML in the source code.

From here you can identify the element id, name, tagname, and class if it has one. Do some research on getelementbyID,getelementsbytagname, etc. or step through the code above to get a feel for how it works.

One last note, if your intranet page has a form element, you will have to get the form object with the getelement methods above and submit it with .submit. If the page uses a button object, get the button element and use .click. Good luck!