CODEWITHSUNDEEP
vbaexcel
Ivan Filipović
Ivan Filipović

Reputation: 41

VBA Date shown as Number

I am new to VBA and i need to get some rows out of a database. The Problem is that the Date the first time it is selected is shown in the format dd.mm.yyyy, but after some time the date is shown as a whole number like 42814 or something like that.

Here are 2 pictures for better understanding

Image1 Image2

And here the code

Application.CutCopyMode = False
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "ODBC;DSN=OpsApps;UID=Alligatoah;Trusted_Connection=Yes;APP=Microsoft Office 2016;WSID=AT000616;DATABASE=OpsApps" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandText = Array( _
        "SELECT TV.Customer, TV.KNUM, TV.DMRF, TV.BoM, TV.Costs, TV.PlnLaunch, TV.Date, TV" _
        , _
        "_.ActualCosts" & Chr(13) & "" & Chr(10) & "FROM OpsApps.dbo.TV TV" & Chr(13) & "" & Chr(10) & "WHERE (TV.SystemMCSDate>={ts '2017-01-01 00:00:00'})" _
        )
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_Query_from_VillachOpsApps"
        .Refresh BackgroundQuery:=False
    End With

Can anyone help? thanks in advance.

Upvotes: 1

Views: 1817

Answers (1)

Wolfie
Wolfie

Reputation: 30175

You want to set the NumberFormat property of your range

For example, if I have 18/08/17 in a cell and then convert its NumberFormat manually in the Excel ribbon to "general", we see 42965. You can specify this format in VBA using a string like so: 

' Use your column here, could also use a table range
ThisWorkbook.Sheets("MySheet").Columns("F").NumberFormat = "dd.mm.yyyy"

Upvotes: 2

Related Questions