Date formatting lost

Nick Burns

Member
Joined
May 24, 2017
Messages
162
Reaction score
0
Points
16
Excel Version(s)
Office 365
What would cause the date formatting to get lost when exporting to a table?

LostDateFormat.PNG
 
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!)
 
Darn it - explicitly setting it to date as the last step didn't help

ExplicitDate.PNG

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 :confused:)
 
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?
 
If I load the data to a table manually, the date format is maintained.

Here's the VBA code that does the importing

Code:
    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
 
Weird...

What I might try is removing this line:

Code:
.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"?
 
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:
 
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.
 
Back
Top