ListWorkbooks时跳过PERSONAL.xlsb工作簿

Question

我有一个列出所有打开的工作簿的代码，因为我所有的代码都在Personal.xlsb中，所以它也会被列出，谁能告诉我一个If条件来跳过列表中的Personal.xlsb。

此外，由于Personal.xlsb没有"Data_Index“，因此也容易给出错误

Sub ListWorkbooks()
Dim Wb As Workbook
For j = 1 To Workbooks.Count
    Sheets("Data_Index").Select
    Range("H3").Cells(j, 1) = Workbooks(j).Name
For i = 1 To Workbooks(j).Sheets.Count

Next i 
Next j
End Sub

Answer 1

修订的答案

通过阅读您对其他答案的评论，您还需要确定哪些打开的工作簿也有一个名为Data_Index的工作表，这就是为什么您得到一个超出范围的下标(您的代码假设每个工作簿都有一个名为Data_Index的工作表。

当我测试时，这对我是有效的：

Sub ListWorkbooks()
Dim Wb As Workbook, wb2 As Workbook
Dim sht As Worksheet
Dim c As Range

'Identify which (if any) of the open workbooks has sheet Data_Index
'Note if more than one it will pick the last one it finds
On Error Resume Next
For j = 1 To Workbooks.Count
    Set sht = Workbooks(j).Sheets("Data_Index")
Next j
On Error GoTo 0

'Check at least one has the required sheet
If sht Is Nothing Then
    MsgBox "There is no open workbook with a sheet named Data_Index", vbExclamation
Else
    'Set the destination for the first workbook name
    Set c = sht.Range("H3")
    For j = 1 To Workbooks.Count
        If Workbooks(j).Name = "Personal.xlsb" Then GoTo NextWb
        c.Value = Workbooks(j).Name
        For i = 1 To Workbooks(j).Sheets.Count
            ' Whatever you want to do cycling sheets
        Next i
        'Offset to the next row ready for the next name
        Set c = c.Offset(1, 0)
NextWb:
    Next j
End If
End Sub

您可以使用数组来完成此操作，但上面的操作将为您完成此操作。

Answer 2

Sub ListWorkbooks()
    Dim Wb As Workbook
    Dim i As Integer, j As Integer
    For j = 1 To Workbooks.Count
        If Workbooks(j).Name <> ThisWorkbook.Name Then
            Workbooks(j).Sheets("Data_Index").Range("H3").Cells(j, 1) = Workbooks(j).Name
        End If

        'not sure what you want to do here
        For i = 1 To Workbooks(j).Sheets.Count

        Next i
    Next j
End Sub