[RESOLVED] Excel-VBA: SaveAs looses userdefined cell formatting

[Zvoni]

Hi Folks,
something weird:

i have an xlsm-workbook, which executes some queries against a DB, and populates a sheet in that workbook.
Works as it's supposed to.

Now, as a final piece of code, it runs

ThisWorkbook.SaveAs "SomeNewFileName.xlsx", xlOpenXMLWorkbook

Note: I'm changing from xlsm to xlsx (No Macros).

Weird thing:
I have one column in my populated sheet, which has user-defined/custom Formatting.
It's actually a DateTime-String, and contrary to the predefined Formattings, i need the seconds,
so i set a custom formatting of "dd.mm.yyyy hh:mm:ss" (german format) for that column.

After execution of the SaveAs-Part, i do get that new Workbook as expected, but in that new Workbook, that single column loses its custom formatting.
All other columns which use predefined formatting are fine.

Huh?

[Zvoni]

Found the Culprit;

I was using CopyFromRecordset, and that overwrites all formatting with the Format/DataType of the underlying DataField.

Solution: Save Column-Formatting in an Array, CopyFromRecordset, restore Formatting from the Array

Public Sub SQLGetOverview(ByRef SQL As String, ByRef ASheet As Worksheet)
Dim f(1 To 14) As String 'Columns A to M
Dim i As Long
    CreateRS SQL
    CleanSheet ASheet.Name, FIRSTROW
    If CLng(RS2.RecordCount) > 0 Then
        For i = 1 To 14
            f(i) = ASheet.Columns(i).NumberFormat  'Save Column-Format
        Next
        ASheet.Range("A" & FIRSTROW).CopyFromRecordset RS2
        For i = 1 To 14
            ASheet.Columns(i).NumberFormat = f(i)  'Restore Column-Format
        Next
    End If
    CloseRS