使用VBA有密码保护的网站数据 [英] Data from password protected website using VBA

问题描述

使用Excel 2007和VBA 6.5

我试图自动化数据收集任务，但我一直运行到运行时错误91 - 对象变量或带块变量未设置这是一个参考的问题还是有我的脚本做

感谢。

脚本包含如下（密码和可变保护）

 子Basic_Web_Query（）即设置=的CreateObject（InternetExplorer.Application）
    与IE浏览器
        。可见=真
        .Navigatehttps://www.urbics.com/Login.php
        做，直到.ReadyState = 4
            的DoEvents
        循环
        .document.all.Item（用户名）。值=用户名
        .document.all.Item（密码）。值=密码
        .document.forms（0）.submit
    结束与随着ActiveSheet.QueryTables.Add（连接：= _
\"URL;http://www.urbics.com/Urbics.php?hs=a97cd706q9948q11e1qbbacq00259002436c&pgreq=AVTotals&aid=4517\",目的地：=范围（$ B $ 4））。名称=Q？S = goog_2
.FieldNames = TRUE
.RowNumbers =假
.FillAdjacentFormulas =假
。preserveFor​​matting = TRUE
.RefreshOnFileOpen = FALSE
.BackgroundQuery = TRUE
.RefreshStyle = xlInsertDeleteCells
.SavePassword = FALSE
.SaveData = TRUE
.AdjustColumnWidth = TRUE
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables =1,2
名.web preFormattedTextToColumns = TRUE
.WebConsecutiveDelimitersAsOne = TRUE
.WebSingleBlockTextImport = FALSE
.WebDisableDateRecognition =假
.WebDisableRedirections =假
.REFRESH BackgroundQuery：= FALSE结束与
结束小组
 

解决方案

更改这些行

  .document.all.Item（用户名）。值=用户名
    .document.all.Item（密码）。值=密码
 

到

  .document.all.Item（loginUserName）。值=用户名
    .document.all.Item（loginUserPassword）。值=密码
 

和重试。

using Excel 2007 and VBA 6.5

I'm trying to automate a data collection task, however i keep running into the "Runtime Error 91 - "Object Variable or With Block Variable not Set." Is this a reference issue or does it have to do with my script.

Thanks.

Script is included below (password and variable protected)

Sub Basic_Web_Query()

Set ie = CreateObject("InternetExplorer.Application")
    With ie
        .Visible = True
        .Navigate "https://www.urbics.com/Login.php"
        Do Until .ReadyState = 4
            DoEvents
        Loop
        .document.all.Item("User Name").Value = "UserName"
        .document.all.Item("Password").Value = "Password"
        .document.forms(0).submit
    End With

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.urbics.com/Urbics.php?hs=a97cd706q9948q11e1qbbacq00259002436c&pgreq=AVTotals&aid=4517", Destination:=Range("$B$4"))

.Name = "q?s=goog_2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "1,2"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False

End With
End Sub

解决方案

Change these lines

    .document.all.Item("User Name").Value = "UserName"
    .document.all.Item("Password").Value = "Password"

to

    .document.all.Item("loginUserName").Value = "UserName"
    .document.all.Item("loginUserPassword").Value = "Password"

And try again.

这篇关于使用VBA有密码保护的网站数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！