I wrote this class to serve as a base class for some specialized Excel exporting classes. I thought it might be of use for other people, so I've posted it here. It uses late binding, and the versioning only deals with 2003 and 2007, but can be easily altered to handle earlier versions (and later versions as they come out).
Upon second thought, the code is too long to chunk up. Therefore, I zipped the single vb file and attached it. There are two classes in the file. One is the base class, which has a bunch of overloaded methods to save either datatables or datareaders to an excel spreadsheet. The other class is a, potentially useless, class that saves information about the new data that was saved. Since the UsedRange method can cover the same ground in normal use, the second class may have no value, but if the base class is extended or inherited in a way that appends information into existing spreadsheets, then the information retained by the second class could have value. Appending data has so many considerations attached to it that it didn't look reasonable to add the functionality to the base class.
I've inherited this class into classes that create pivot tables, and classes that use templates and macros to do lots of other things, but those are really custom implementations (and the pivot tables currently only work for 2003, reliably), so I haven't included any of the inherited classes.
Reply With Quote