如何使用VBA登录网站并检索数据以Excel

Question

有很多

关于如何登录网站的例子，比如gmail

但是由于某种原因，我的代码只使用提供的用户名和密码打开网页而不登录目标页面

我启用了 HTML 对象库并Microsoft互联网控件

有什么想法吗，因为我对这个对象库相当陌生？

另外，登录后如何获取表格以从网站导入 excel？

我尝试了宏录制，但它需要密码才能登录网站

一个例子会有所帮助:)非常感谢！

Dim HTMLDoc As HTMLDocument
Dim MyBrowser As InternetExplorer

Sub MyGmail()
 Dim MyHTML_Element As IHTMLElement
 Dim MyURL As String
 On Error GoTo Err_Clear
 MyURL = "https://www.google.com/accounts/Login"

 Set MyBrowser = New InternetExplorer
 MyBrowser.Silent = True
 MyBrowser.Navigate MyURL
 MyBrowser.Visible = True
 Do
 Loop Until MyBrowser.ReadyState = READYSTATE_COMPLETE
 Set HTMLDoc = MyBrowser.Document
 HTMLDoc.all.Email.Value = "[e-mail]" 
 HTMLDoc.all.passwd.Value = "************" 'my password would be here

 For Each MyHTML_Element In HTMLDoc.getElementsByTagName(“input”)
      If MyHTML_Element.Type = “submit” Then _
           MyHTML_Element.Click: Exit For
 Next

 Err_Clear:
 If Err <> 0 Then
 Err.Clear
 Resume Next
 End If
End Sub

Answer 1

Sub LoginToSite()
Const cURL = "https://singlepoint.usbank.com/cs70_banking/logon/sbuser"
Const ccompanyID = "YourCustomerIDHere"
Const cj_username = "YourUserIDHere"
Const cj_password = "YourPasswordHere"
Dim IE As InternetExplorer
Dim doc As HTMLDocument
Dim LoginForm As HTMLFormElement
Dim CustomerIDInputBox As HTMLInputElement
Dim UserIDInputBox As HTMLInputElement
Dim PasswordInputBox As HTMLInputElement
Dim objElement As Object
Dim objCollection As Object
Set IE = New InternetExplorer
IE.Visible = True
IE.navigate cURL
'Wait for initial page to load
Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop
Set doc = IE.document
'Get the only form on the page
Set LoginForm = doc.forms(0)
'Get the CustomerID textbox and populate it
'input name="companyID" id="companyID" size="18" value="" type="text"
Set CustomerIDInputBox = doc.getElementById("companyID")
UsernameInputBox.Value = ccompanyID
'Get the UserID textbox and populate it
'input name="j_username" id="j_username" size="18" type="text"
Set UserIDInputBox = doc.getElementById("j_username")
UsernameInputBox.Value = cj_username
'Get the Password textbox and populate it
'input name="j_password" id="j_password" size="18" type="password"

Set PasswordInputBox = doc.getElementById("j_password")
PasswordInputBox.Value = cj_password
'Get the form input button and click it
'input name="submit_logon" size="18" type="image"
Set ElementCol = IE.document.getElementsByName("submit_logon")
ElementCol.Item(0).Click
'Wait for the new page to load
Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop
End Sub

Answer 2

如果您尝试登录的是专门用于Gmail，则以下代码将起作用。 我使用它。

  Sub Gmail()
  'Set a reference (Visual Basic Editor) > Tools > References) to the following libraries:
  ' a) Microsoft Internet Controls
  ' b) Microsoft HTML Object Library
  Dim IE As New SHDocVw.InternetExplorer
  Dim HTMLdoc As New MSHTML.HTMLDocument
  Dim HTMLelement As MSHTML.IHTMLElement
  With IE
  .Visible = True
  .Silent = True 'avoid any pop-up
  .navigate "https://www.google.com/accounts/Login"""
  Do While .Busy Or .readyState <> READYSTATE_COMPLETE
  DoEvents
  Loop
  End With
  Call myTimer
  Set HTMLdoc = IE.document
  HTMLdoc.all.identifier.Value = "YourUsernameHere"
  HTMLdoc.all.identifierNext.Click
  With IE
  Do While .Busy Or .readyState <> READYSTATE_COMPLETE
  DoEvents
  Loop
  End With
  Call myTimer
  For Each HTMLelement In HTMLdoc.getElementsByName("password")
  If HTMLelement.getAttribute("type") = "password" Then
  HTMLelement.Value = "YourPasswordHere"
  Exit For
  End If
  Next HTMLelement
  HTMLdoc.all.passwordNext.Click
  Set IE = Nothing
  Set HTMLdoc = Nothing
  Set HTMLelement = Nothing
  End Sub
  Private Sub myTimer()
  Dim timerStart As Single
  Const pauseTIME As Integer = 1 'second
  timerStart = Timer
  Do Until Timer - timerStart > pauseTIME
  DoEvents
  Loop
  End Sub