[问题]EXCEL VBA阵列索引超出范围(错误代码9)

楼主: hakim9505 (hakim)   2017-11-03 11:07:56
日安:
各位大大好
因工作需要在网络上找了一些范例,要去抓取营收的资料
但是会出现
"阵列索引超出范围(错误代码 9"的错误
请问有大大可以指点迷津一下该如何修正呢?
vba码
Sub 国内上市Data()
Dim year As String
Dim month As String
Dim qurl As String
year = Worksheets("设定").Range("$A$4") <==此行执行时报错
month = Worksheets("设定").Range("$B$4")
qurl = "http://mops.twse.com.tw/nas/t21/otc/t21sc03_" + year + "_" + month + "_0.html"
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & qurl, Destination:= _
Worksheets("国内上市Data").Range("$A$1"))
.Name = "otc/t21sc03_"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Worksheets("设定").Range("$A$3") = "年份"
Worksheets("设定").Range("$B$3") = "月份"
End Sub
软件:Office 2010
版本:
作者: soyoso (我是耀宗)   2017-11-03 11:17:00
有可能无符合"设定"工作表名的工作表如https://i.imgur.com/uJmfsQ8.png 测试无该符合工作表名出现原文所述错误讯息
楼主: hakim9505 (hakim)   2017-11-03 11:19:00
!真的,我发现我那一分页叫做"1.设定"不是"设定"Orz改完后变这样@@https://i.imgur.com/2JM09FJ.png1.设定那页长这样https://i.imgur.com/9YFsfaW.jpg
作者: soyoso (我是耀宗)   2017-11-03 11:30:00
报错那列上方加上 worksheets("国内上市Data").select
楼主: hakim9505 (hakim)   2017-11-03 11:52:00
过!!!了!!!大大我爱你!我对你的景仰....

Links booklink

Contact Us: admin [ a t ] ucptt.com