I don't know anything about the hidden apoxtrophe. The dat is not in
Microsoft Werial date so DateValue need to be used

format(DateValue(dtDate),"MM-DD-YY")

You may want to see the value so you can figure out what is wrong
msgbox(dtDate)

"Chris" wrote:

The format function gives me a type mismatch error. So, I tried the
FormatDateTime function, without success and also a Trim does not solve the
problem. Browsing the web, I found something about an hidden apostrophe. Do
you know anything about this?

"Joel" wrote:

CSTR add a space infront of the string. The space is a place holder for a
plus or minus sign. I think the extra space is why the data is left aligned
in the cell. Instead of cstr() use format()

from
cstr(dtDate)
to
format(dtDate,"MM-DD-YY")


"Chris" wrote:

Hi

I have an excel 12 spreadsheet. With a simple vbscript, I can add some data
to this excel spreadsheet. This works fine. The problem I have, are the data
types. I need to add the current date to this excel spreadsheet. But
regardless of the column format in the excel spreadsheet, the added values
have "Standard" as data type. The pre defined data type in the excel
spreadsheet column (type = date) seems to be ignored. Also changing the data
type after the import does not solve the problem.

Only switching to the edit mode of an excel spreadsheet field solves the
problem. After adding a date value, the value is left aligned in the excel
spreadsheet field. After switching to the edit mode, the value is right
aligned in the excel spreadsheet field and the data type changes to date.

vbscript:
dtDate = Date
strFileName = "c:\temp\logonlog.xlsx"
strProvider = "Provider=Microsoft.ACE.OLEDB.12.0"
strDataSource = "Data Source="+ strfilename
strExtend = "Extended Properties='Excel 12.0;HDR=YES'"
Set objExConnection = CreateObject("ADODB.Connection")
objExConnection.Open strProvider + ";" + strDataSource + ";" + strExtend

' Without converting to string, the code generates a type mismatch error
objExConnection.Execute "Insert into log (logdate) values ('" + cstr(dtDate)
+ "')"
objExConnection.close()

Special thanks for your support.
Chris