This category will hold articles regarding developement in Excel VBA. It will serve as a wiki and an Excel VBA Framework for myself.
Some development tasks reoccur for every customer. Since I am a lazy bum it will be nice to have a central source where I can reuse source code from.
The following methods provide you with three different ways of reading a range into a collection in VBA.
- Hits: 9586
If you have to write an ado db recordset into an Excel worksheet, you have two basic options.
Either you write a recordset object using Excel API function copyFromRecordset or you write the data implementing an own method looping through all records.
However calling the function copyFromRecordset is pretty fast compared to looping through all records, it opposes the risk of incorrectly formatted data in a target worksheet. Root cause for this is the often failing attempt of ADO to guess a data type of a column in a source worksheet based on the first few records.
You can switch some optional parameter in a connection string called IMAX in order to have ADO take all records of a field in a recordset into account before guessing a field type, but this will lead only to incredible bad performance.
On top of that ADO is still likely to fail in guessing a field type of an Excel column even, if you use IMAX.
- Hits: 6482
Handling of Date values can become very annoying. Especially, if Excel always thinks it knows better than the programmer or user *sigh*! Automation is meant to help people, not mess up their work.
In order to help me at least displaying date values in the correct format I implemented a little function to get a pattern string based on a users system locale settings.
- Hits: 6517
Page 1 of 10