excel - 用于列出所有工作表的简单 VBA 宏会生成错误 : Type Mismatch

Question

我正在制作一个 ExcelComparer，但我遇到了一个可能明显的错误，我显然错过了一些东西。

我在 Excel 2007 中运行 vba 宏

我得到的确切错误是“运行时错误 13:类型不匹配”当循环尝试获取第二个 worksheet.name 时会发生这种情况。
因此，第一个工作表名称返回正常

下面是宏

提前致谢，升

Sub compare()

Dim strWorkbook1, strWorkbook2 As String
Dim Workbook1, Workbook2 As Workbook
strWorkbook1 = Worksheets("Sheet1").Range("C5") & Worksheets("Sheet1").Range("D5")
strWorkbook2 = Worksheets("Sheet1").Range("C6") & Worksheets("Sheet1").Range("D6")

Set xlapp = CreateObject("Excel.application")
Set Workbook1 = xlapp.Workbooks.Open(strWorkbook1)
xlapp.Visible = False

Dim ws As Worksheet
For Each ws In Workbook1.Sheets
  'ws.Select
  If Not ws.Visible = xlSheetVeryHidden Then
      MsgBox (ws.Name)
  End If
Next ws
xlapp.Close
End Sub

Answer 1

将其用于 For 循环:

For Each ws In Workbook1.Worksheets

根据 MSDN，Sheets 和 Worksheets 属性之间的区别是:

This property does not return macro sheets, charts, or dialog sheets. Use the Sheets property to return those sheets as well as worksheets. You can also use the specialized properties Excel4MacroSheets and Excel4IntlMacroSheets to return macro sheets and the Charts property to return charts.