| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date format
Hello
I have made an Visual Basic script where i have a date in this formet 09-03-2007, this i am writing to an excel sheet witch is formatted as short date, but my problem is that it take it as a calculation so what i get is the number -2001. How can this be -- EKJ |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date format
How are you "writing to an excel sheet" ?
NickHK "EKJ" wrote in message ... Hello I have made an Visual Basic script where i have a date in this formet 09-03-2007, this i am writing to an excel sheet witch is formatted as short date, but my problem is that it take it as a calculation so what i get is the number -2001. How can this be -- EKJ |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date format
Hello
I am using a Siemens product named WinCC where i have made a script like this Dim OmkoerslerD Dim GodeEmnerD Dim BrokD Dim ProdD Dim IkkeRegTidD Dim OmstillingD Dim OmkoerselD Dim moedeD Dim LaveAndetD Dim nedBrakD Dim rengoeringD Dim mglVarerD Dim smaStopD Dim BemandetD Dim DatoD Dim DatoA Dim ProdA Dim IkkeRegTidA Dim DatoN Dim ProdN Dim IkkeRegTidN Dim Dato_Test OmkoerslerD = OmkoerslerDag GodeEmnerD = GodeEmnerDag BrokD = BrokDag ProdD = SmartTags("ProdDag") IkkeRegTidD = SmartTags("IkkeRegTIdDag") OmstillingD = SmartTags("OmstillingDag") OmkoerselD = SmartTags("OmkoerselDag") moedeD = SmartTags("moededag") LaveAndetD = SmartTags("laveAndetDag") nedBrakD = SmartTags("nedBrakDag") rengoeringD = SmartTags("rengoeringDag") mglVarerD = SmartTags("mglVarerDag") smaStopD = SmartTags("smaStopDag") BemandetD = SmartTags("BemandetDag") DatoD = FormatDateTime(Date, 1) DatoA = Date ProdA = SmartTags("ProdAften") ProdA = SmartTags("IkkeRegTidAften") DatoN = Date ProdA = SmartTags("ProdNat") ProdA = SmartTags("IkkeRegTidNat") SmartTags("EKJ_DAG") = Int(Day(Date)) SmartTags("EKJ_AAR") = Int(Year(Date)) SmartTags("EKJ_MDR") = Int(Month(Date)) SmartTags("EKJ_DATO") = (EKJ_DAG&"/"&EKJ_MDR&"/"&EKJ_AAR) Dato_Test = FormatNumber Dim objConnection Dim strConnectionString Dim strSQL Dim objCommand strConnectionString = "Provider=MSDASQL;DSN=OEE_05681;UID=;PWD=;" strSQL = "INSERT INTO Dag (DataAARD,DataMDRD,DataDAGD,ProdDag,IkkeRegTidDag, OmstillingDag,OmkoerselDag,moedeDag,laveAndetDag,n edBrakDag,rengoeringDag,mglVarerDag,smaStopDag,Bem andetDag,GodeEmnerDag,OmkoerslerDag,BrokDag) VALUES (" & DatoD & "," & EKJ_MDR & "," & EKJ_DAG & "," & ProdD & "," & IkkeRegTidD & "," & OmstillingD & "," & OmkoerselD & "," & moedeD & "," & LaveAndetD & "," & nedBrakD & "," & rengoeringD & "," & mglVarerD & "," & smaStopD & "," & BemandetD & "," & GodeEmnerD & "," & OmkoerslerD & "," & BrokD & ");" Set objConnection = CreateObject("ADODB.Connection") objConnection.ConnectionString = strConnectionString objConnection.Open Set objCommand = CreateObject("ADODB.Command") With objCommand .ActiveConnection = objConnection .CommandText = strSQL End With objCommand.Execute Set objCommand = Nothing objConnection.Close Set objConnection = Nothing -- EKJ "NickHK" skrev: How are you "writing to an excel sheet" ? NickHK "EKJ" wrote in message ... Hello I have made an Visual Basic script where i have a date in this formet 09-03-2007, this i am writing to an excel sheet witch is formatted as short date, but my problem is that it take it as a calculation so what i get is the number -2001. How can this be -- EKJ |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date format
I assume you are working in Excel VBA ?
It would help to type your variables. Otherwise everything is a Variant and therefore difficult to know exact what value it contains. e.g. Dim DatoD As Date 'etc Also, maybe dates need to be surrounded in "#". e.g. VALUES (#" & DatoD & "#," & Add a <Debug.Print strSQL to see exact what you are using as the SQL text. NickHK "EKJ" wrote in message ... Hello I am using a Siemens product named WinCC where i have made a script like this Dim OmkoerslerD Dim GodeEmnerD Dim BrokD Dim ProdD Dim IkkeRegTidD Dim OmstillingD Dim OmkoerselD Dim moedeD Dim LaveAndetD Dim nedBrakD Dim rengoeringD Dim mglVarerD Dim smaStopD Dim BemandetD Dim DatoD Dim DatoA Dim ProdA Dim IkkeRegTidA Dim DatoN Dim ProdN Dim IkkeRegTidN Dim Dato_Test OmkoerslerD = OmkoerslerDag GodeEmnerD = GodeEmnerDag BrokD = BrokDag ProdD = SmartTags("ProdDag") IkkeRegTidD = SmartTags("IkkeRegTIdDag") OmstillingD = SmartTags("OmstillingDag") OmkoerselD = SmartTags("OmkoerselDag") moedeD = SmartTags("moededag") LaveAndetD = SmartTags("laveAndetDag") nedBrakD = SmartTags("nedBrakDag") rengoeringD = SmartTags("rengoeringDag") mglVarerD = SmartTags("mglVarerDag") smaStopD = SmartTags("smaStopDag") BemandetD = SmartTags("BemandetDag") DatoD = FormatDateTime(Date, 1) DatoA = Date ProdA = SmartTags("ProdAften") ProdA = SmartTags("IkkeRegTidAften") DatoN = Date ProdA = SmartTags("ProdNat") ProdA = SmartTags("IkkeRegTidNat") SmartTags("EKJ_DAG") = Int(Day(Date)) SmartTags("EKJ_AAR") = Int(Year(Date)) SmartTags("EKJ_MDR") = Int(Month(Date)) SmartTags("EKJ_DATO") = (EKJ_DAG&"/"&EKJ_MDR&"/"&EKJ_AAR) Dato_Test = FormatNumber Dim objConnection Dim strConnectionString Dim strSQL Dim objCommand strConnectionString = "Provider=MSDASQL;DSN=OEE_05681;UID=;PWD=;" strSQL = "INSERT INTO Dag (DataAARD,DataMDRD,DataDAGD,ProdDag,IkkeRegTidDag, OmstillingDag,OmkoerselDag ,moedeDag,laveAndetDag,nedBrakDag,rengoeringDag,mg lVarerDag,smaStopDag,Beman detDag,GodeEmnerDag,OmkoerslerDag,BrokDag) VALUES (" & DatoD & "," & EKJ_MDR & "," & EKJ_DAG & "," & ProdD & "," & IkkeRegTidD & "," & OmstillingD & "," & OmkoerselD & "," & moedeD & "," & LaveAndetD & "," & nedBrakD & "," & rengoeringD & "," & mglVarerD & "," & smaStopD & "," & BemandetD & "," & GodeEmnerD & "," & OmkoerslerD & "," & BrokD & ");" Set objConnection = CreateObject("ADODB.Connection") objConnection.ConnectionString = strConnectionString objConnection.Open Set objCommand = CreateObject("ADODB.Command") With objCommand .ActiveConnection = objConnection .CommandText = strSQL End With objCommand.Execute Set objCommand = Nothing objConnection.Close Set objConnection = Nothing -- EKJ "NickHK" skrev: How are you "writing to an excel sheet" ? NickHK "EKJ" wrote in message ... Hello I have made an Visual Basic script where i have a date in this formet 09-03-2007, this i am writing to an excel sheet witch is formatted as short date, but my problem is that it take it as a calculation so what i get is the number -2001. How can this be -- EKJ |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date format
Hi,
XL expect a date to be in the format as defined in the regional setting.(/ or - or " ") Try to use DateSerial function Regards JY "EKJ" wrote in message ... Hello I am using a Siemens product named WinCC where i have made a script like this Dim OmkoerslerD Dim GodeEmnerD Dim BrokD Dim ProdD Dim IkkeRegTidD Dim OmstillingD Dim OmkoerselD Dim moedeD Dim LaveAndetD Dim nedBrakD Dim rengoeringD Dim mglVarerD Dim smaStopD Dim BemandetD Dim DatoD Dim DatoA Dim ProdA Dim IkkeRegTidA Dim DatoN Dim ProdN Dim IkkeRegTidN Dim Dato_Test OmkoerslerD = OmkoerslerDag GodeEmnerD = GodeEmnerDag BrokD = BrokDag ProdD = SmartTags("ProdDag") IkkeRegTidD = SmartTags("IkkeRegTIdDag") OmstillingD = SmartTags("OmstillingDag") OmkoerselD = SmartTags("OmkoerselDag") moedeD = SmartTags("moededag") LaveAndetD = SmartTags("laveAndetDag") nedBrakD = SmartTags("nedBrakDag") rengoeringD = SmartTags("rengoeringDag") mglVarerD = SmartTags("mglVarerDag") smaStopD = SmartTags("smaStopDag") BemandetD = SmartTags("BemandetDag") DatoD = FormatDateTime(Date, 1) DatoA = Date ProdA = SmartTags("ProdAften") ProdA = SmartTags("IkkeRegTidAften") DatoN = Date ProdA = SmartTags("ProdNat") ProdA = SmartTags("IkkeRegTidNat") SmartTags("EKJ_DAG") = Int(Day(Date)) SmartTags("EKJ_AAR") = Int(Year(Date)) SmartTags("EKJ_MDR") = Int(Month(Date)) SmartTags("EKJ_DATO") = (EKJ_DAG&"/"&EKJ_MDR&"/"&EKJ_AAR) Dato_Test = FormatNumber Dim objConnection Dim strConnectionString Dim strSQL Dim objCommand strConnectionString = "Provider=MSDASQL;DSN=OEE_05681;UID=;PWD=;" strSQL = "INSERT INTO Dag (DataAARD,DataMDRD,DataDAGD,ProdDag,IkkeRegTidDag, OmstillingDag,OmkoerselDag,moedeDag,laveAndetDag,n edBrakDag,rengoeringDag,mglVarerDag,smaStopDag,Bem andetDag,GodeEmnerDag,OmkoerslerDag,BrokDag) VALUES (" & DatoD & "," & EKJ_MDR & "," & EKJ_DAG & "," & ProdD & "," & IkkeRegTidD & "," & OmstillingD & "," & OmkoerselD & "," & moedeD & "," & LaveAndetD & "," & nedBrakD & "," & rengoeringD & "," & mglVarerD & "," & smaStopD & "," & BemandetD & "," & GodeEmnerD & "," & OmkoerslerD & "," & BrokD & ");" Set objConnection = CreateObject("ADODB.Connection") objConnection.ConnectionString = strConnectionString objConnection.Open Set objCommand = CreateObject("ADODB.Command") With objCommand .ActiveConnection = objConnection .CommandText = strSQL End With objCommand.Execute Set objCommand = Nothing objConnection.Close Set objConnection = Nothing -- EKJ "NickHK" skrev: How are you "writing to an excel sheet" ? NickHK "EKJ" wrote in message ... Hello I have made an Visual Basic script where i have a date in this formet 09-03-2007, this i am writing to an excel sheet witch is formatted as short date, but my problem is that it take it as a calculation so what i get is the number -2001. How can this be -- EKJ |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date format
Hello again
This worked with #" & DatoD & "# Thank you verry much and have a nice week-end. EKJ "NickHK" skrev: I assume you are working in Excel VBA ? It would help to type your variables. Otherwise everything is a Variant and therefore difficult to know exact what value it contains. e.g. Dim DatoD As Date 'etc Also, maybe dates need to be surrounded in "#". e.g. VALUES (#" & DatoD & "#," & Add a <Debug.Print strSQL to see exact what you are using as the SQL text. NickHK "EKJ" wrote in message ... Hello I am using a Siemens product named WinCC where i have made a script like this Dim OmkoerslerD Dim GodeEmnerD Dim BrokD Dim ProdD Dim IkkeRegTidD Dim OmstillingD Dim OmkoerselD Dim moedeD Dim LaveAndetD Dim nedBrakD Dim rengoeringD Dim mglVarerD Dim smaStopD Dim BemandetD Dim DatoD Dim DatoA Dim ProdA Dim IkkeRegTidA Dim DatoN Dim ProdN Dim IkkeRegTidN Dim Dato_Test OmkoerslerD = OmkoerslerDag GodeEmnerD = GodeEmnerDag BrokD = BrokDag ProdD = SmartTags("ProdDag") IkkeRegTidD = SmartTags("IkkeRegTIdDag") OmstillingD = SmartTags("OmstillingDag") OmkoerselD = SmartTags("OmkoerselDag") moedeD = SmartTags("moededag") LaveAndetD = SmartTags("laveAndetDag") nedBrakD = SmartTags("nedBrakDag") rengoeringD = SmartTags("rengoeringDag") mglVarerD = SmartTags("mglVarerDag") smaStopD = SmartTags("smaStopDag") BemandetD = SmartTags("BemandetDag") DatoD = FormatDateTime(Date, 1) DatoA = Date ProdA = SmartTags("ProdAften") ProdA = SmartTags("IkkeRegTidAften") DatoN = Date ProdA = SmartTags("ProdNat") ProdA = SmartTags("IkkeRegTidNat") SmartTags("EKJ_DAG") = Int(Day(Date)) SmartTags("EKJ_AAR") = Int(Year(Date)) SmartTags("EKJ_MDR") = Int(Month(Date)) SmartTags("EKJ_DATO") = (EKJ_DAG&"/"&EKJ_MDR&"/"&EKJ_AAR) Dato_Test = FormatNumber Dim objConnection Dim strConnectionString Dim strSQL Dim objCommand strConnectionString = "Provider=MSDASQL;DSN=OEE_05681;UID=;PWD=;" strSQL = "INSERT INTO Dag (DataAARD,DataMDRD,DataDAGD,ProdDag,IkkeRegTidDag, OmstillingDag,OmkoerselDag ,moedeDag,laveAndetDag,nedBrakDag,rengoeringDag,mg lVarerDag,smaStopDag,Beman detDag,GodeEmnerDag,OmkoerslerDag,BrokDag) VALUES (" & DatoD & "," & EKJ_MDR & "," & EKJ_DAG & "," & ProdD & "," & IkkeRegTidD & "," & OmstillingD & "," & OmkoerselD & "," & moedeD & "," & LaveAndetD & "," & nedBrakD & "," & rengoeringD & "," & mglVarerD & "," & smaStopD & "," & BemandetD & "," & GodeEmnerD & "," & OmkoerslerD & "," & BrokD & ");" Set objConnection = CreateObject("ADODB.Connection") objConnection.ConnectionString = strConnectionString objConnection.Open Set objCommand = CreateObject("ADODB.Command") With objCommand .ActiveConnection = objConnection .CommandText = strSQL End With objCommand.Execute Set objCommand = Nothing objConnection.Close Set objConnection = Nothing -- EKJ "NickHK" skrev: How are you "writing to an excel sheet" ? NickHK "EKJ" wrote in message ... Hello I have made an Visual Basic script where i have a date in this formet 09-03-2007, this i am writing to an excel sheet witch is formatted as short date, but my problem is that it take it as a calculation so what i get is the number -2001. How can this be -- EKJ |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| permanent conversion of 1904 date format to 1900 date format | Excel Worksheet Functions | |||
| convert serial date format to normal date format | Excel Discussion (Misc queries) | |||
| Convert date + time text format to date format | Excel Worksheet Functions | |||
| code to convert date from TEXT format (03-02) to DATE format (200203) | Excel Programming | |||
| Change a date in text format xx.xx.20xx to a recognised date format | Excel Programming | |||
Linear Mode
