Pass custom class between files

[andrewhharmon]

Hello. I have created a custom class cTaskPlan in my "TaskPlan" workbook that represents all the data in the workbook. I have a function FillTaskPlan() that creates a new instance of cTaskPlan and populates it with data from the sheet and returns a cTaskPlan object. I'm trying to send out one of these "TaskPlan" workbooks to each team member. Each week I will get the workbook backs. I then want to have a "TaskPlan Aggregator" workbook that i can open, run a macro, it opens each "TaskPlan" workbook and pulls in a populated cTaskPlan object into a collection. Then in the Aggregator file I'll have a collection of all the cTaskPlan objects I can loop through and work with them. I think I have the "TaskPlan" workbook setup correct. From the aggregator, I can open the "TaskPlan" workbook, call the FillTaskPlan() function returning the cTaskPlan object to an object variable. Then I add that to a collection. The problem is, when i close the "TasKPlan" workbook to move on to the next one, the TaskPlan object that was full of data is emptied out. It's as if the object is a reference to an object in the "TaskPlan" workbook and is cleared out upon closing the workbook. Any help would be greatly appreciated. Thanks (Excel 2007)

This is the FillTaskPlan() code in the "TaskPlan" workbook

Please Login or Register  to view this content.

This is the code from the "TaskAggregator" workbook. Everything works well. Upon wbTarget.Close... the TaskPlan object is cleared out. I need it to persist.

Please Login or Register  to view this content.

[andrewhharmon]

Anyone have any thoughts on this? I've tried copying the class modules into the aggregator, but that doesn't seem to work. I feel like this must be an issue a lot of folks have faced.

[JosephP]

I reckon you need to change the FillTaskPlan function so that it takes a cTaskPlan object as an argument (ByRef) and populates that so that your aggregator workbook can create the cTaskPlan objects, pass them to the workbooks to fill out, and then store them. you will need the class modules in the aggregator workbook.

[Andy Pope]

Closing the source workbook will cause automation error when you try and reference the returned TaskPlan object.

Why do you need code in the source workbook?
Instead keep the FillTaskPlan in the TaskAggregator workbook and pass the workbook/worksheet to the function in order to harvest the data.

[andrewhharmon]

I actually use the FillTaskPlan in the source workbook, but no reason I can't also have the code in the aggregator. I'll give it a try. Thanks for the suggestion.

[andrewhharmon]

my problem now is that in the source workbook I've defined some workbook properties. My FillTaskPlan procedure references alot of these custom properties. An example is

Please Login or Register  to view this content.

I do this because I reference this worksheet through out a lot of modules, and if we ever need to change the name of the worksheet, I want to do it once here. I was hoping from the aggregator, I could still reference these properties. Something like wbTarget.wrksTaskPlan would return that worksheet. Is there a way to do this without adding a reference to in the aggregator to the source workbook?

[Andy Pope]

Add another function to the Task workbook in order to allow you to return that information.

Please Login or Register  to view this content.

In the aggragator file

Please Login or Register  to view this content.

You can then use that information to reference the correct sheet. Although if you had used code name reference it would not have matter what the actual tab name was.

[andrewhharmon]

Ok, I think I'm moving in the wrong direction putting all this code in the aggregator. I have lots of "settings" in the task plan file. These are constants that I've set up. the fillTaskPlan code relies on these settings to properly populate the TaskPlan object. In theory, some of these settings could be different for each TaskPlan. That's why the goal was to create a TaskPlan class, and populated it for each taskplan based on it's settings, then pass a TaskPlan object to the aggregator. There must be a way for classes to work across files right?

[andrewhharmon]

Maybe this should be a new post, but I'm questioning my general strategy. Can some one talk a little on the "Correct" way to do this in Excel. Essentially I have multiple excel files that will all use some custom classes I created. I'd like to some how create a library of these classes that I can reference in each file and share these files with other users, so when they send them back, I can interact with the custom classes. I don't want the users to have to install anything or use an appilcation add-in. I'm reading about add-ins, dll files... I'm just not sure what my options are, the correct architecture and how to get started with it. Thanks

[Andy Pope]

What about not closing the work book until you have aggregated the data? It only stops working when you close the workbook and I assume that is because your classes read information directly from ranges rather than loading the data into variables/arrays/collections etc.