Results 1 to 8 of 8

Thread: Date formatting lost

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Acolyte Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    75
    Articles
    0
    Excel Version
    Office 365

    Date formatting lost

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

    Click image for larger version. 

Name:	LostDateFormat.PNG 
Views:	17 
Size:	56.5 KB 
ID:	9365
    Oh... by the way, YOU'RE WELCOME!

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,346
    Articles
    56
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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!)
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Acolyte Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    75
    Articles
    0
    Excel Version
    Office 365
    Darn it - explicitly setting it to date as the last step didn't help

    Click image for larger version. 

Name:	ExplicitDate.PNG 
Views:	6 
Size:	41.5 KB 
ID:	9366

    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 )
    Oh... by the way, YOU'RE WELCOME!

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,346
    Articles
    56
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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?
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Acolyte Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    75
    Articles
    0
    Excel Version
    Office 365
    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
    Oh... by the way, YOU'RE WELCOME!

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,346
    Articles
    56
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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"?
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •