Import data into Excel 2003 using the login page

, , , IE, , . , , , , .

, HTML DOM. , , , -. script, :

• Userform
• Textbox1 , textbox2 .
• , ( F4 VBA, 2 PasswordChar)

• , , :

  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
  

• URL- - getelement -.

-, HTML DOM (Document Object Model), .

Internet Explorer. IE F12. . ( ) . , , . . HTML .

, , , . getelementbyID, getelementsbytagname .. , , .

: , getelement .submit. , .click. !