VBA - Visual Basic for Applications
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: 7930
Determining the last row actually containing information in an Excel worksheet can be an annoying task, if you are relying on the Excel API to do the trick for you. Especially when you are not exatcly in control over the handed files you are dealing with and people are allowed to enter information anywhere it is not as easy as it should be.
Source Code automating Excel search function
Below function is determining the last row containing data in provided worksheet using the Excel search function.
This approach works only, if no cells in given sheet are merged.
Therefore merged cells are removed from a handed workbook before determining its last row containing data.
- Hits: 7028
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: 7724
Subcategories
Excel VBA Article Count: 30
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.
MS Office and VBA Article Count: 11
This category holds articles regarding general things in MS Office VBA independent from the MS Office application.
Power Point VBA Article Count: 1
Access VBA Article Count: 7
This category holds articles regarding Access VBA, but also general things I come accross Access and its usage in companies.
Access VBA DAO Article Count: 2
Page 6 of 17