Reputation: 41
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
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
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