VB: date format in an Excel file

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

qqxz - 24 Jan 2006 11:01 GMT

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!

Signature

qqxz

Reply to this Message

Harald Staff - 24 Jan 2006 12:30 GMT

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

> Dear All,
>
[quoted text clipped - 17 lines]
>
> TIA!

Reply to this Message

qqxz - 25 Jan 2006 07:56 GMT

Thanks! That is very helpful.

Signature

qqxz

Reply to this Message