将工作表保存为对象属性后类型变为Object的问题求助

问题根源 #

Excel的Worksheet是COM对象，一旦其所属工作簿关闭，该对象的引用就会失效，无法再作为Worksheet类型进行操作——不管存在Dictionary还是Collection里，这个问题都会出现。

具体解决办法 #

1. 提前提取所需数据，不保存Worksheet引用 #

这是最稳妥的方案：不要直接存储Worksheet对象，而是在工作簿打开时，把需要的加班数据（比如单元格值、关键格式信息等）提取出来，存入自定义类的属性中。

修改后的EmployeeObject类代码：

Private pName As String
Private pOvertimeData As Variant ' 用二维数组存储加班表数据

Public Property Get Name() As String
    Name = pName
End Property

Public Property Let Name(value As String)
    pName = value
End Property

Public Property Get OvertimeData() As Variant
    OvertimeData = pOvertimeData
End Property

Public Property Let OvertimeData(value As Variant)
    pOvertimeData = value
End Property

遍历文件夹时的处理逻辑：

Dim empObj As New EmployeeObject
Dim ws As Worksheet
Dim wb As Workbook

Set wb = Workbooks.Open(filePath)
Set ws = wb.Sheets("加班表") ' 替换为你的工作表名称

empObj.Name = ws.Range("A1").Value ' 假设A1是员工姓名
empObj.OvertimeData = ws.UsedRange.Value ' 提取整个有效区域的数据到数组

wb.Close SaveChanges:=False ' 安全关闭工作簿
dict.Add empObj.Name, empObj ' 存入Dictionary

这种方式下，即使工作簿关闭，数组里的数据依然可以正常读取和操作，不会出现类型失效的问题。

2. 临时保持工作簿打开（仅适用于少量文件） #

如果必须保留Worksheet引用，可以在遍历过程中不关闭工作簿，同时存储所有打开的工作簿对象，确保Worksheet的引用有效。但这种方法会占用大量内存，文件数量多的时候容易崩溃。

示例代码：

Dim openWorkbooks As New Collection
Dim empObj As New EmployeeObject
Dim wb As Workbook

Set wb = Workbooks.Open(filePath)
Set empObj.EmployeeWorksheet = wb.Sheets("加班表")
empObj.Name = "员工姓名"

dict.Add empObj.Name, empObj
openWorkbooks.Add wb ' 存储工作簿引用，防止其被自动释放

后续操作完成后，统一关闭所有工作簿：

Dim wbItem As Workbook
For Each wbItem In openWorkbooks
    wbItem.Close SaveChanges:=False
Next wbItem

3. 存储路径和表名，按需重新获取引用 #

如果需要后续再次操作原工作表，可以存储工作簿的完整路径和工作表名称，需要时重新打开工作簿获取引用。

修改后的EmployeeObject类：

Private pName As String
Private pWorkbookPath As String
Private pWorksheetName As String

Public Property Get Name() As String
    Name = pName
End Property

Public Property Let Name(value As String)
    pName = value
End Property

Public Property Get WorkbookPath() As String
    WorkbookPath = pWorkbookPath
End Property

Public Property Let WorkbookPath(value As String)
    pWorkbookPath = value
End Property

Public Property Get WorksheetName() As String
    WorksheetName = pWorksheetName
End Property

Public Property Let WorksheetName(value As String)
    pWorksheetName = value
End Property

' 按需获取工作表引用的方法
Public Function GetWorksheet() As Worksheet
    Dim wb As Workbook
    Set wb = Workbooks.Open(Me.WorkbookPath)
    Set GetWorksheet = wb.Sheets(Me.WorksheetName)
End Function

需要操作时调用GetWorksheet方法重新打开获取引用，操作完成后记得关闭对应工作簿。

内容的提问来源于stack exchange，提问作者p3terson