Excel in VBA help

[Paulsburbon]

I am using this in Access. Unfortunately I cannot post the file. I wish I could but my employer will not allow it. The use is I hit a button and it exports a table to an excel spreadsheet. I have another one that exports it then inserts it into an email. I wrote this a while back and now trying to optimize the code and make it easier to read. I plan on updating this to a SQL server and vb.net. I guess I'll have to split it up then. Unless something else can be done. Thanks for your time Bob.

 

[boblarson]

Well, first of all -

Creating different functions is good. However, passing objects to those functions can be a bit problematic. You don't need to set the function to be the object for returning. All it needs to run the function as if the object is there is to pass it in the params and then when the function ends the control is back with the original function, as long as you don't do something like

Set xlSheetVariable = Nothing

which then will honk things up.

 

[Paulsburbon]

I hate to beat a dead horse but wanted to add this just in case someone else reads this. If you have to move data over from access to excel using someone else's parameters then do not use a template. It's a huge pain in the ass and you should just figure out the formatting on your own and code it in to your sub procedure. I'm going to try creating a class to pass the worksheet in and I'll post my results.