Problem with date format when reading from workbook via SQL / ADODB

[Thebatfink]

Hi. I have a problem with date formats driving me crazy. I have a source workbook which has dates in UK format DD/MM/YYYY. When I open this source file and look at the cell formatting, it is Date Type and is DD/MM/YYYY.

I read the contents of the workbook to a recordset and populate to an array:

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
Dim conStr As String
conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & exportFilePath & exportFileName & "';" & _
             "Extended Properties=""Excel 12.0;HDR=YES;IMEX=1;"";"
Dim strSQL As String
strSQL = "SELECT * FROM [Sheet1$]"
cnn.Open conStr
rs.Open strSQL, cnn, adOpenStatic, adLockOptimistic, adCmdText
Dim sapDeliveryList As Variant
If rs.Fields.Count >= 1 Then
    sapDeliveryList = rs.GetRows
End If

But when I output this array to my workbook, the dates are getting confused with US formats. I added a watch to sapDeliveryList and I see for example sapDeliveryList(1,0) value is #09/02/2024# and the type is Variant/Date. But when I output this array to workbook, the date is displaying as 02/09/2024. My machine is all setup for United Kingdom date time formats. Any way to address this??

Thanks
James

 

[MARK858]

VBA always treats dates as MM/DD/YYYY unless it is converted when importing to a dateserial which is a bit difficult when using SQL You can convert it once in the sheet using Text To Columns.

• Select the column
• Data tab
• Text to Columns
• Delimited
• Next
• Make sure all the checkboxes are cleared
• Next
• Select Date checkbox
• Select the MDY option (yes MDY as you want the source format)
• Click Finish
• Format the cells as desired

 

[RoryA]

How are you putting the data into the worksheet? If you're using Transpose to flip the array, then that is where the actual problem occurs because the dates will become text at that point.

 

[Thebatfink]

That's interesting. I have not seen what Mark858 proposed before. It works in that it 'fixes' the dates at least how I want to see them. I guess it is trying to flip the dates and any which result in an invalid date (which Excel already displayed as DD/MM/YYYY s MM/DD/YYYY wasn't valid) it is skipping over. It seems like a hack but I can live with it.

Rory,

Yes, I'm having to transpose because (I assume) the rs.getrows is transposing the data. You have a solution to remedy that? Do I need to copy the values of the array into a second array and transpose them myself value by value?

Sheet1.Range("A2").Resize(UBound(sapDeliveryList, 2) + 1, UBound(sapDeliveryList, 1) + 1) = Application.Transpose(sapDeliveryList)

Thanks
James

 

[RoryA]

Do I need to copy the values of the array into a second array and transpose them myself value by value

Basically yes.

 

[MARK858]

It works in that it 'fixes' the dates at least how I want to see them.

It isn't just how you want to see them, it alters the date. Try formatting the cells as dd mmmm yyyyy after the text to columns and you will see the date which was originally displaying as 02/09/2024 is now 09 February 2024 and not 02 September 2024

I guess it is trying to flip the dates and any which result in an invalid date (which Excel already displayed as DD/MM/YYYY s MM/DD/YYYY wasn't valid) it is skipping over.

It doesn't skip any cells it reverses the month/day on all the cells whether they could be read as a date or not

 

[Thebatfink]

It doesn't skip any cells it reverses the month/day on all the cells whether they could be read as a date or not

Maybe I did not explain clearly, I didn’t initially mention that some dates obviously display OK, i.e. 16/02/2024 shows 16/02/2024 because 02/16/2024 isn’t valid in a field format for dd/mm/yyyy, I guess excel simply left it as text so it displayed as I wanted to see it (it is for visual use only, no date calculations) or it did interpret it as being dd/mm/yyyy. Then when I ran the text to columns it changes 02/09/2024 to 09/02/2024, and in other rows with example 16/02/2024 I still had 16/02/2024 showing OK also without it erroring.

Basically yes.

So I did this and yes, it now outputs to worksheet correctly with no manipulation of the dates required. I did not know the transpose function was converting everything to string, thanks for the explanation and solution. For anyone looking later, I simply created a new variant array, redimensioned it to size of the initial array but reversed dimensions ‘redim array2(UBound(array, 2), UBound(array, 1)’ then cycle through array and array(0, i) = array2(i, 0), array(1, i) = array2(i, 1) etc. with which array2 can be output to worksheet without the transpose function.

So two things learnt, I am sure both will come in handy in the future. Appreciate your time.

 

[Thebatfink]

Just thought I would come back with some proper code to transpose for posterity.

Dim i as Long
Dim k as Long
Dim array2 As Variant
ReDim array2(UBound(array1, 2), UBound(array1, 1))
For i = 0 To UBound(array1, 2)
    For k = 0 To UBound(array1, 1)
       array2(i, k) = array1(k, i)
    Next k
Next i