Date formatting lost

[Nick Burns]

What would cause the date formatting to get lost when exporting to a table?

 

[Ken Puls]

Usually the data type not being explicitly set. (Yes, it looks fine, but I don't trust it.)

But if you've already loaded it to a table... you might need to go to Table Tools --> Properties and play with the "preserve formatting" setting.

(PS, nice challenge the other day too. Been meaning to try it, but have too much going on!)

 

[Nick Burns]

Darn it - explicitly setting it to date as the last step didn't help



The Worksheet/Table is created via macros - (using Gil's VBA code) could something there be causing the issue? All that's doing is telling Power Query where to export the data.

(As for the challenge, my model is getting an error: Expression.Error: There were too many elements in the enumeration to complete the operation )

 

[Ken Puls]

Honestly, Nick, I'm not sure. What happens if you just format the Excel table to use a date? Does it stick after the refresh?

 

[Nick Burns]

If I load the data to a table manually, the date format is maintained.

Here's the VBA code that does the importing

    With wsWorksheet
        Set qtQueryTable = .ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Data" _
        , Destination:=Range("$A$1")).QueryTable
    End With


    With qtQueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [" & pstrQueryName & "]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Data"
        .Refresh BackgroundQuery:=False
    End With

 

[Ken Puls]

Weird...

What I might try is removing this line:

.PreserveFormatting = True

That shouldn't be causing a problem, but you never know.

Alternately, you could always adjust the code to add a section to format that column correctly when the table is created. Is it always called "Date"?

 

[Nick Burns]

I did try commenting that line out, but no joy.

I have resorted to formatting the date columns after the export instead of beating my head against the wall on this issue. Bummer I have to do this though. :frusty:

 

[Ken Puls]

Are the date columns always called the same thing? If so, we could add that to the VBA so that it doesn't keep driving you crazy.