当我试图让excel选择要使用的工作簿时,下标超出范围给我一个错误。我不知道我是否必须引用它来自哪个文件夹。它们都在同一个文件夹中。我浏览了其他人的解决方案,但是我既没有相同的格式也没有相同的任务。错误在分配工作簿编号的行上
Sub bringbookstogether()
Dim currentsheet As Worksheet
Set currentsheet = Application.ActiveSheet
Dim othersheets As Worksheet
Dim wbook As Workbook
Dim c As String
'assigns the number to start with
Dim a, b, d As Integer
a = 4
b = 6
d = 1
'assigns workbook numbers
If (d = 1) Then
Set wbook = Workbooks("MaintPrep Sheet 1st")
Else
If (d = 2) Then
Set wbook = Workbooks("MaintPrep Sheet 2nd")
Else
If (d = 3) Then
Set wbook = Workbooks("MaintPrep Sheet 3rd")
End If
End If
End If
'End if it's done with all the workbooks
Do Until (d = 4)
'Looks for the sheet that has the same name
Do While (c = currentsheet.Name)
'Ends in row 99
Do While (b < 99)
'Ends in Column 52
Do While (a < 52)
currentsheet.Cells(b, a) = currentsheet.Cells(b, a) + Workbooks(d).Sheets(c).Cells(b, a)
a = a + 1
Loop
b = b + 1
Loop
Loop
d = d + 1
Loop
End Sub
最佳答案
首先,最好使用完整的文件名:Workbooks("MaintPrep Sheet 1st.xlsx")
等
第二,一旦您要访问的工作簿之一当前未打开,此代码就会出错。如果未打开工作簿,则该工作簿在当前上下文中不存在,因此Excel将引发错误91。
要解决此问题,您可以执行以下操作:
Sub a()
Dim wb As Workbook
On Error Resume Next 'To avoid error 91
Set wb = Workbooks("MaintPrep Sheet 1st.xlsx")
On Error GoTo 0 'To avoid not seeing other errors.
If Not wb Is Nothing Then
'Do stuff
MsgBox "Opened!"
Else
'Handle the fact that it's missing
MsgBox "Not open!"
End If
'Alternatively, OPEN the workbook if you couldn't set it in the first place:
On Error Resume Next
Set wb = Workbooks("MaintPrep Sheet 1st.xlsx")
On Error GoTo 0
If wb Is Nothing Then
Set wb = Workbooks.Open("C:\FullPath\MaintPrep Sheet 1st.xlsx")
'Do stuff
End If
End Sub
关于vba - 选择工作簿时下标超出范围,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/44886962/