Data type issues adding data from vbscript

[Chris]

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

 

[Joel]

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]

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]

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]

The format function gives me a type mismatch error anymore. Please excuse me,
I am not adept in programming, but I can't find the format function at msdn
vbscript language reference. So I tried the FormatDateTime function again.
This function works without converting to string, but in excel, I have the
problem again.

 

[Joel]

The format function in the VBA help is almost impossible to find.

to test it try this code

msgbox(format(DateValue("1/1/08"),"MMMM-DD-YYYY"))


Here is the VBA help. It is easier to read as HTML format from the help menu.

Format Function


Returns a Variant (String) containing an expression formatted according to
instructions contained in a format expression.

Syntax

Format(expression[, format[, firstdayofweek[, firstweekofyear]]])

The Format function syntax has these parts:

Part Description
expression Required. Any valid expression.
format Optional. A valid named or user-defined format expression.
firstdayofweek Optional. A constant that specifies the first day of the week.
firstweekofyear Optional. A constant that specifies the first week of the
year.



Settings

The firstdayofweek argument has these settings:

Constant Value Description
vbUseSystem 0 Use NLS API setting.
VbSunday 1 Sunday (default)
vbMonday 2 Monday
vbTuesday 3 Tuesday
vbWednesday 4 Wednesday
vbThursday 5 Thursday
vbFriday 6 Friday
vbSaturday 7 Saturday



The firstweekofyear argument has these settings:

Constant Value Description
vbUseSystem 0 Use NLS API setting.
vbFirstJan1 1 Start with week in which January 1 occurs (default).
vbFirstFourDays 2 Start with the first week that has at least four days in
the year.
vbFirstFullWeek 3 Start with the first full week of the year.



Remarks

To Format Do This
Numbers Use predefined named numeric formats or create user-defined numeric
formats.
Dates and times Use predefined named date/time formats or create
user-defined date/time formats.
Date and time serial numbers Use date and time formats or numeric formats.
Strings Create your own user-defined string formats.



If you try to format a number without specifying format, Format provides
functionality similar to the Str function, although it is internationally
aware. However, positive numbers formatted as strings using Format don’t
include a leading space reserved for the sign of the value; those converted
using Str retain the leading space.

If you are formatting a non-localized numeric string, you should use a
user-defined numeric format to ensure that you get the look you want.

Note If the Calendar property setting is Gregorian and format specifies date
formatting, the supplied expression must be Gregorian. If the Visual Basic
Calendar property setting is Hijri, the supplied expression must be Hijri.

If the calendar is Gregorian, the meaning of format expression symbols is
unchanged. If the calendar is Hijri, all date format symbols (for example,
dddd, mmmm, yyyy) have the same meaning but apply to the Hijri calendar.
Format symbols remain in English; symbols that result in text display (for
example, AM and PM) display the string (English or Arabic) associated with
that symbol. The range of certain symbols changes when the calendar is Hijri.

Symbol Range
d 1-30
dd 1-30
ww 1-51
mmm Displays full month names (Hijri month names have no abbreviations).
y 1-355
yyyy 100-9666

 

[Chris]

I have a type mismatch error again, exactly using your command.

Thank you for your notes concerning the format function. I am not 100% sure
again, that this function works in VBScript. Microsoft writes the following
on msdn:

VBScript supports some of the built-in Visual Basic functions such as
Msgbox, Date, and IsNumeric. However, because VBScript is a subset of Visual
Basic, not all built-in functions are supported. For example, VBScript does
not support the Format function and the file I/O functions.

The format function in the VBA help is almost impossible to find.

to test it try this code

msgbox(format(DateValue("1/1/08"),"MMMM-DD-YYYY"))


Here is the VBA help. It is easier to read as HTML format from the help menu.

Format Function


Returns a Variant (String) containing an expression formatted according to
instructions contained in a format expression.

Syntax

Format(expression[, format[, firstdayofweek[, firstweekofyear]]])

The Format function syntax has these parts:

Part Description
expression Required. Any valid expression.
format Optional. A valid named or user-defined format expression.
firstdayofweek Optional. A constant that specifies the first day of the week.
firstweekofyear Optional. A constant that specifies the first week of the
year.



Settings

The firstdayofweek argument has these settings:

Constant Value Description
vbUseSystem 0 Use NLS API setting.
VbSunday 1 Sunday (default)
vbMonday 2 Monday
vbTuesday 3 Tuesday
vbWednesday 4 Wednesday
vbThursday 5 Thursday
vbFriday 6 Friday
vbSaturday 7 Saturday



The firstweekofyear argument has these settings:

Constant Value Description
vbUseSystem 0 Use NLS API setting.
vbFirstJan1 1 Start with week in which January 1 occurs (default).
vbFirstFourDays 2 Start with the first week that has at least four days in
the year.
vbFirstFullWeek 3 Start with the first full week of the year.



Remarks

To Format Do This
Numbers Use predefined named numeric formats or create user-defined numeric
formats.
Dates and times Use predefined named date/time formats or create
user-defined date/time formats.
Date and time serial numbers Use date and time formats or numeric formats.
Strings Create your own user-defined string formats.



If you try to format a number without specifying format, Format provides
functionality similar to the Str function, although it is internationally
aware. However, positive numbers formatted as strings using Format don’t
include a leading space reserved for the sign of the value; those converted
using Str retain the leading space.

If you are formatting a non-localized numeric string, you should use a
user-defined numeric format to ensure that you get the look you want.

Note If the Calendar property setting is Gregorian and format specifies date
formatting, the supplied expression must be Gregorian. If the Visual Basic
Calendar property setting is Hijri, the supplied expression must be Hijri.

If the calendar is Gregorian, the meaning of format expression symbols is
unchanged. If the calendar is Hijri, all date format symbols (for example,
dddd, mmmm, yyyy) have the same meaning but apply to the Hijri calendar.
Format symbols remain in English; symbols that result in text display (for
example, AM and PM) display the string (English or Arabic) associated with
that symbol. The range of certain symbols changes when the calendar is Hijri.

Symbol Range
d 1-30
dd 1-30
ww 1-51
mmm Displays full month names (Hijri month names have no abbreviations).
y 1-355
yyyy 100-9666

The format function gives me a type mismatch error anymore. Please excuse me,
I am not adept in programming, but I can't find the format function at msdn
vbscript language reference. So I tried the FormatDateTime function again.
This function works without converting to string, but in excel, I have the
problem again.

 

[Chris]

I found a workaround myself. Before executing the "insert" command, I convert
the datetime value in a double data type. Excel 12 is able to dissolve this
value as datetime afterwards.

Special thanks for your support.
regards chris

I have a type mismatch error again, exactly using your command.

Thank you for your notes concerning the format function. I am not 100% sure
again, that this function works in VBScript. Microsoft writes the following
on msdn:

VBScript supports some of the built-in Visual Basic functions such as
Msgbox, Date, and IsNumeric. However, because VBScript is a subset of Visual
Basic, not all built-in functions are supported. For example, VBScript does
not support the Format function and the file I/O functions.

The format function in the VBA help is almost impossible to find.

to test it try this code

msgbox(format(DateValue("1/1/08"),"MMMM-DD-YYYY"))


Here is the VBA help. It is easier to read as HTML format from the help menu.

Format Function


Returns a Variant (String) containing an expression formatted according to
instructions contained in a format expression.

Syntax

Format(expression[, format[, firstdayofweek[, firstweekofyear]]])

The Format function syntax has these parts:

Part Description
expression Required. Any valid expression.
format Optional. A valid named or user-defined format expression.
firstdayofweek Optional. A constant that specifies the first day of the week.
firstweekofyear Optional. A constant that specifies the first week of the
year.



Settings

The firstdayofweek argument has these settings:

Constant Value Description
vbUseSystem 0 Use NLS API setting.
VbSunday 1 Sunday (default)
vbMonday 2 Monday
vbTuesday 3 Tuesday
vbWednesday 4 Wednesday
vbThursday 5 Thursday
vbFriday 6 Friday
vbSaturday 7 Saturday



The firstweekofyear argument has these settings:

Constant Value Description
vbUseSystem 0 Use NLS API setting.
vbFirstJan1 1 Start with week in which January 1 occurs (default).
vbFirstFourDays 2 Start with the first week that has at least four days in
the year.
vbFirstFullWeek 3 Start with the first full week of the year.



Remarks

To Format Do This
Numbers Use predefined named numeric formats or create user-defined numeric
formats.
Dates and times Use predefined named date/time formats or create
user-defined date/time formats.
Date and time serial numbers Use date and time formats or numeric formats.
Strings Create your own user-defined string formats.



If you try to format a number without specifying format, Format provides
functionality similar to the Str function, although it is internationally
aware. However, positive numbers formatted as strings using Format don’t
include a leading space reserved for the sign of the value; those converted
using Str retain the leading space.

If you are formatting a non-localized numeric string, you should use a
user-defined numeric format to ensure that you get the look you want.

Note If the Calendar property setting is Gregorian and format specifies date
formatting, the supplied expression must be Gregorian. If the Visual Basic
Calendar property setting is Hijri, the supplied expression must be Hijri.

If the calendar is Gregorian, the meaning of format expression symbols is
unchanged. If the calendar is Hijri, all date format symbols (for example,
dddd, mmmm, yyyy) have the same meaning but apply to the Hijri calendar.
Format symbols remain in English; symbols that result in text display (for
example, AM and PM) display the string (English or Arabic) associated with
that symbol. The range of certain symbols changes when the calendar is Hijri.

Symbol Range
d 1-30
dd 1-30
ww 1-51
mmm Displays full month names (Hijri month names have no abbreviations).
y 1-355
yyyy 100-9666

The format function gives me a type mismatch error anymore. Please excuse me,
I am not adept in programming, but I can't find the format function at msdn
vbscript language reference. So I tried the FormatDateTime function again.
This function works without converting to string, but in excel, I have the
problem again.

:

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)

:

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?

:

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")


:

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