VB: date format in an Excel file

[qqxz]

Dear All,

I got this requirement to export a date string to an Excel file
from VB application. The short date format in each PC's Regioal
Options could be different. When the VB reads the date string
from MSSQL in "MM/dd/yyyy", the application will export it like:

dFormat = GetUserLocaleInfo(LCID, LOCALE_SSHORTDATE)
dS = CDate(Format(itm.SubItems(15), "MM/dd/yyyy"))
xl.Cells(2, 16) = Format(dS, dFormat)

The problem that I am facing is, although the cell format is set
to be "dd-MMM-yy" like "14-Mar-98", there are two types of values
in the column: e.g. "28/09/2005" or "06-Dec-04". After I double
clicked on the cell, "28/09/2005" will then be displayed as
"28-Sep-05".

Is there anyway to show a consistent date format? Could we
control it from the VB program?

TIA!

 

[Harald Staff]

Hi

A date is a date is a date, not a string. Formatting is for display only.
What you do here is formatting when you assign it to a variable and also
formatting when you write it into a cell. That is trouble. Assign a real
date to a variable declared as Date, write it directly to a cell and then
format the cell if necessary.

Dim DD as Date
DD = Dateserial(2005, 12, 25)
xl.Cells(2, 16).Value = DD

HTH. Best wishes Harald

 

[qqxz]

Thanks! That is very helpful.