vba 如何登录以在vba excel中启用Web查询

How to login to enable web query in vba excel

提问by user2983064

I have a website with certain data files saved behind a login/password. I have written a macro which gets the data and uses it but in order for it to work, the user needs to manually open a web query, go to my website and login with the 'remember me' box ticked.

我有一个网站，其中某些数据文件保存在登录名/密码后面。我编写了一个宏来获取数据并使用它，但为了使其工作，用户需要手动打开网络查询，转到我的网站并在“记住我”框打勾的情况下登录。

I want to write some VBA code which does this automatically but can't figure out how. This is code I have to log in with Internet Explorer, and it works, but the web browser in the web query doesn't recognise its logged in, dont think it uses the same cookies or something

我想编写一些自动执行此操作但不知道如何执行的 VBA 代码。这是我必须使用 Internet Explorer 登录的代码，它可以工作，但是 Web 查询中的 Web 浏览器无法识别其已登录，不要认为它使用相同的 cookie 或其他东西

Sub IE_login()
Dim ie As Object
Dim C
Dim ULogin As Boolean, ieForm
Dim MyPass As String, MyLogin As String
 Set ie = CreateObject("InternetExplorer.Application")
redo:
    MyLogin = Application.InputBox("Please enter your VBAX login", "VBAX username", Default:="login", Type:=2)
    MyPass = Application.InputBox("Please enter your VBAX password", "VBAX Password", Default:="Password", Type:=2)

If MyLogin = "" Or MyPass = "" Then GoTo redo

Set ie = New InternetExplorer
ie.Visible = True
ie.Navigate "http://investingsidekick.com/wp-admin"

 'Look for password Form by finding test "Password"
For Each ieForm In ie.Document.forms
    If InStr(ieForm.innertext, "Password") <> 0 Then
        ULogin = True
         'enter details
        ieForm(0).Value = MyLogin
        ieForm(2).Value = MyPass
         'login
        ieForm.submit
        Exit For
    Else
    End If
Next
If ULogin = False Then MsgBox "User is aleady logged in"
Set ie = Nothing
End Sub

回答by jacouh

This enters into login attempts loop to your site as anybody has your username/password then you (My Greetings!).

这进入登录尝试循环到您的站点，因为任何人都有您的用户名/密码，然后是您（我的问候！）。

Function sofGetIELogin20018473()
'
  Dim i As Long, nMaxLogins As Long
  Dim ie As Object
  'Dim C
  Dim ULogin As Boolean, ieForm
  Dim MyPass As String, MyLogin As String
'
  Set ie = CreateObject("InternetExplorer.Application")
  ie.Navigate "http://investingsidekick.com/wp-admin"
  '
  nMaxLogins = 3
  ULogin = True
  '
  i = 0
  '
  ' enter into the loop of login attempts, maximum 3 attempts:
  '
  While (ULogin And i < nMaxLogins)
    '
    ' wait for the query response:
    '
    Do While (ie.Busy Or ie.READYSTATE <> 4)
      DoEvents
    Loop
    '
    ' get username + password:
    '
    ie.Visible = False
    '
    MyLogin = InputBox("Please enter your VBAX login", "VBAX username", Default:="login")
    MyPass = InputBox("Please enter your VBAX password", "VBAX Password", Default:="Password")
    '
    ie.Visible = True

    '
    ' check username + password:
    '
    If MyLogin <> "" And MyPass <> "" Then

      '
      'Look for password Form by finding test "Password"
      '
      ULogin = False
      '
      For Each ieForm In ie.Document.Forms
        If InStr(ieForm.innertext, "Password") <> 0 Then
          ULogin = True
          'enter details
          ieForm.elements(0).Value = MyLogin
          ieForm.elements(1).Value = MyPass
          'login
          ieForm.submit
          Exit For
        End If
      Next
'
    End If
'
    i = i + 1
'
  Wend
'
  Set sofGetIELogin20018473 = ie
  Set ie = Nothing
'
  If (ULogin) Then
    MsgBox "User login failed."
  Else
    MsgBox "User is logged in."
  End If
'
End Function

And then you use this:

然后你使用这个：

Sub sofIELogin20018473()
'
  Dim ie As Object
'
  Set ie = sofGetIELogin20018473()
  ie.Navigate "http://investingsidekick.com/my-secret-contents"
  '
  '...
  '
'
  Set ie = Nothing
'
End Sub