VBA EXCEL：修复“运行时错误‘9’”：下标超出范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26424403/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA EXCEL: Fixing "Runtime-error '9'": Subscript out of range
提问by mario R.
I have the below code which attempts to open a file name which changes on a daily basis if the sheet is empty. The file name is always named as such:
我有下面的代码，它尝试打开一个文件名，如果工作表为空，该文件名每天都会更改。文件名总是这样命名：
- "K:\Shared\Num\Temp\Available_list_" & Year(Now()) & Month(Now()) & Day(Now()) & ".txt"
- "K:\Shared\Num\Temp\Available_list_" & Year(Now()) & Month(Now()) & Day(Now()) & ".txt"
The reason behind this is to automate the file activation instead of copying, pasting, and renaming the file every time. I have received this error when I updated the code to include the above mentioned path. Can someone point to me out where my error is? You may find the code below:
这背后的原因是自动激活文件，而不是每次都复制、粘贴和重命名文件。当我更新代码以包含上述路径时收到此错误。有人可以指出我的错误在哪里吗？您可以在下面找到代码：
Private Sub UserForm_Initialize()
Application.ScreenUpdating = False
If IsEmpty(A_Regular.Range("A2")) Then
Dim TxtPath, TxtName As String
TxtPath = "K:\Shared\Num\Temp\Available_list_" & Year(Now()) & Month(Now()) & Day(Now()) & ".txt"
TxtName = Year(Now()) & Month(Now()) & Day(Now()) & ".txt"
Workbooks.OpenText Filename:=TxtPath, Origin:=437, StartRow:=1, _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=True, Semicolon:=False, Comma:=False, Space:=False, _
Other:=True, OtherChar:=",", FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 5), Array(4, 1), Array(5, 1)), TrailingMinusNumbers:=True
'-------------------------------------------------------------------------'
' Setup Sheet '
'-------------------------------------------------------------------------'
With Workbooks(TxtName)
'-------------------------------------------------------------------------'
Dim TotalFree, TotalFields As Double
TotalFree = Application.WorksheetFunction.CountIf(Range("D:D"), "FREE")
Range("A1:E" & TotalFree).Copy
End With
Workbooks("Matcher.xlsm").Activate
Activation.Visible = True
Activation.Activate
With Activation
Range("A:E").PasteSpecial
Range("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1") = "GSM"
Range("B1") = "Type"
Range("C1") = "Date"
Range("D1") = "Status"
Range("E1") = "Number"
Range("F1") = "Pattern"
Call Encryption
End With
Activation.Cells.Clear
Windows(TxtName).Close
Control.Activate
Activation.Visible = False
End If
Application.WindowState = xlMinimized
NumberManagement.Show 0
End Sub
回答by Fratyx
You open a file named "Available_list_" & Year(Now()) & Month(Now()) & Day(Now()) & ".txt"(filename without path) but you try to reference the sheet by Year(Now()) & Month(Now()) & Day(Now()) & ".txt"without Available_list. A worksheet with this name of course does not exist.
您打开了一个名为"Available_list_" & Year(Now()) & Month(Now()) & Day(Now()) & ".txt"（不带路径的文件名）的文件，但您尝试通过Year(Now()) & Month(Now()) & Day(Now()) & ".txt"不带Available_list. 具有此名称的工作表当然不存在。
