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.

' @Author - Alexander Bolte
' @Change Date - 2013-12-14
' @Description - Determines the date format of the system the application is running on.
' The Excel application is getting the date seperator and the format of date values
' from the systems locale settings.
' @Returns - A String holding literals for Day (dd), Month (mm) and Year (yyyy)
' in the order corresponding the systems locale setting separated using the locales date separator.
Function getDateFormat() As String
    Dim dateFormat As String
    Dim datOrder As Integer
    Dim datSeparator As String
    
On Error GoTo err_handler:
    
    datOrder = Application.International(xlDateOrder)
    datSeparator = Application.International(xlDateSeparator)
    
    If datOrder = 0 Then
        dateFormat = "mm" & datSeparator & "dd" & datSeparator & "yyyy"
    ElseIf datOrder = 1 Then
        dateFormat = "dd" & datSeparator & "mm" & datSeparator & "yyyy"
    ElseIf datOrder = 2 Then
        dateFormat = "yyyy" & datSeparator & "mm" & datSeparator & "dd"
    End If
    
err_handler:
    If Err.Number <> 0 Then
        Err.Clear
    End If
    getDateFormat = dateFormat
End Function

Last Updated: 25 July 2015

Hits: 9049

If you need to reference a workbook, which might already be opened but all you have ready is the file path the following function will do the trick for you.

If the Excel workbook is not opened oran opened workbook with the same name is not saved under given file path, Nothing will be returned.

' @Author - Alexander Bolte
' @Change Date - 2013-12
' @Description - Returns a workbook object referencing an Excel file
' available under given file path, if the Excel file was already opened before calling this function.
' @Param filePath - a path to an Excel file.
' @Returns - a workbook object referencing an Excel file available under given file path.
Public Function getOpenedWorkbook(ByVal filePath As String) As Workbook
    Dim ret As Workbook
    Dim wrkName As String
    Dim sep As String
    Dim splitted() As String
    
    On Error GoTo errHandle:
    
    ' Get the separator for path strings on operating system.
    sep = Application.PathSeparator
    splitted = Split(filePath, sep)
    wrkName = splitted(UBound(splitted))
    Set ret = Application.Workbooks(wrkName)
    If ret.path <> filePath Then
        ret = Nothing
    End If
    
errHandle:
    If Err.Number <> 0 Then
        Err.Clear
    End If
    
    Set getOpenedWorkbook = ret
End Function

Last Updated: 07 June 2014

Hits: 8051

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.

Last Updated: 12 July 2015

Hits: 8534