Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data type issues adding data from vbscript
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data type issues adding data from vbscript
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data type issues adding data from vbscript
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data type issues adding data from vbscript
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data type issues adding data from vbscript
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" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data type issues adding data from vbscript
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 dont 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" wrote: 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" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data type issues adding data from vbscript
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. "Joel" wrote: 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 dont 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" wrote: 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" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data type issues adding data from vbscript
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 "Chris" wrote: 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. "Joel" wrote: 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 dont 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" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding data from multiple worksheets of the same type | Excel Discussion (Misc queries) | |||
VBA Import Convert Data Type from Type 1 to Type 2 | Excel Programming | |||
Keep data visable in excel from vbscript input | Excel Programming | |||
Inserting large amounts of data with VBScript... | Excel Programming | |||
Creating properly formatted text file from vbscript using excel data | Excel Programming |