I have an Employee class with properties Name and Worksheet for the purpose of compiling Employees Overtime sheets. The following code iterates through a folder, creates an Employee and adds it to a Dictionary. My problem is that once we’re out of the Loop, the Worksheet becomes a generic Object, so I can’t do any operation on it!
Sub ImportEmployeeFilesTest()
Dim FolderPath As String
Dim FileName As String
Dim wb As Workbook
Dim employee As EmployeeObject
Dim EmployeesDict As Object
Application.ScreenUpdating = False
Application.DisplayAlerts = False
FolderPath = "C:Users00379070DesktopVSCodedata"
FileName = Dir(FolderPath & "*_PunchClock.xlsm")
Set EmployeesDict = CreateObject("Scripting.Dictionary")
Do While FileName <> ""
Set wb = Workbooks.Open(FolderPath & FileName)
Set employee = New EmployeeObject
employee.SetWorksheet wb.Sheets(1)
EmployeesDict.Add employee.Name, employee
MsgBox TypeName(EmployeesDict(employee.Name).EmployeeWorksheet) ' This prints Worksheet
wb.Close False
FileName = Dir
Loop
MsgBox TypeName(EmployeesDict("Peter-Nicholas").EmployeeWorksheet) ' This prints Object
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
EmployeeObject.cls
Option Explicit
Public Name As String
Public EmployeeWorksheet As Worksheet
Private Sub Class_Initialize()
End Sub
Public Sub SetWorksheet(ws As Worksheet)
Set EmployeeWorksheet = ws
Me.SetName
End Sub
Public Sub SetName()
If Not EmployeeWorksheet Is Nothing Then
Me.Name = EmployeeWorksheet.Range("A2").Value
Else
Err.Raise vbObjectError + 1000, "Employee.SetName", "Worksheet is not set!"
End If
End Sub
I have no idea why this happens and couldn’t find anyone with the same problem on forums! I have tried using a Collection instead of Dictionary, but I get the same exact results… Please Help!
4
This is due to closing wb, not due to using a Dictionary or Collection. A Worksheet can’t exist without a parent Workbook.
A simple example:
Sub Test()
Dim wb As Workbook
Set wb = Workbooks.Add
Dim ws As Worksheet
Set ws = wb.Worksheets(1)
Debug.Print TypeName(ws) ' Worksheet
wb.Close False
Debug.Print TypeName(ws) ' Object
End Sub