Can any VB developers HELP PLEASE!!!!

[sstein]

Hi

Wondering if anyone who knows Visual Basic 6.0 can help with a small problem I am having, it has been driving me mad for over an hour now!

I have written a VB app that takes in from a textbox a date and other info and then writes that data to a spreadsheet.

It is all working fine but when I write the date e.g. 01/06/2006 into the textbox and run the code, it gets written to excel as 06/01/2006???

Code snippet below

The textbox is called txtChqDate
I also have a textbox called txtTest, when I write the date into this just before it gets written to excel it is in the correct format i.e. 01/06/2006.


Dim sDataDateToWrite As String

If IsDate(sDataDateToWrite) = False Then
MsgBox ("Invalid date entered"
End If

txtTest.Text = sDataDateToWrite

Do
DateCell = xlSht.Cells(DateCounterR, 1).Value
'MsgBox (DateCell)
If DateCell <> "" Then
DateCounterR = DateCounterR + 1
End If
Loop Until DateCell = ""

MsgBox (sDataDateToWrite) <<< this displays correct date format!

<< then i open the spreadsheet >>

xlSht.Cells(DateCounterR, 1).Value = sDataDateToWrite

<< close and save the spreadsheet >>

I have tried setting the format of the cells in excel before running the program to date / general / custome dd/mm/yyyy, however when i open it after the program has written to it, it has changed the cell format!

Any help would be greatly appreciated as this is making me nuts, and I don't know what else I could try??

I am on a UK machine with a the regional date / time all set to UK settings.

Should I be forcing the excel formats through the program beforehand?

-

Stuart

[roadsweeper]

As a workaround, what about writing the string to the cell as you're doing and then formatting the cell programmatically? (Is this what you were hinting at the end of your post?)

[sstein]

Yeah thats what I was thinking about,

using the VB program to set the cell format either beforehand or after writing the date to the cell,

The confusing thing is its being written as a string, I think its excel that is being daft and assumming (wrongly) it is a US date.

If i write 25/9/1981 into the textbox and run the program, it writes 25/9/1981 into the spreadsheet and leaves the cell format at General.

Need to try adn work out how edit a cell format through VB

Cheers!
-

Stuart

[polus]

What about writing it to the cell as a string rather then a date?

Edited by polus on Tuesday 29th August 22:04

[sstein]

I am writing it as a string,

the textbox data type is set to a string, and it gets put into a string variable and writen to excel. However excel seems to be taking it upon itself to decide its a date, and an American one at that! Regardless of how I set the format beforehand

Cheers

Stuart

[atomicrex]

You are displaying classic symptons of the arse that is Excel!

The dates in Excel often get mixed up with American dates. The way I get round this and is problem free!!! Convert the date to

dtNewDate=Format(dtOldDate,"dd/mmm/yyyy"

By stating mmm this will put todays date = 29/Aug/2006 no confusion ;-)

[sstein]

Thanks!!!!

I will try that!

Why did the Americans decide they didnt like dd/mm/yyyy!!!

Cheers

-


Stuart

[atomicrex]

it is actually very useful, when sorting dates in a folder having the month first is better as 2/4/2006 is less than 3/2/2006

Naming files I usually do the full thing yyyy-mm-dd that way it always sorts in numerical order!!

[ATG]

Not entirely fair to blame it on Excel. What you are encountering is merely the tip of a the big dog poop iceberg that is handling dates as formatted bits of text. Simple text formats that look anything like dd/mm/yy are ambiguous ... for the first couple of weeks of each month ... +/- one century. On a Windows box there are too many places that you can set internationalisation config bollox to risk that approach of handling dates this way. Even if you go down the "1 Apr 06" route, evntually your machine will decide it's French and cease to understand "Apr". The solution is to abslutely minimise holding dates in strings. If you want to get the user to enter a date, get them to use a "microshite date and time picker" control, not a text box. The value of a date control is of a "date" type, not a "string" type. It is numerical, the number of days since 1 jan 1900, or thereabouts. Declare any intermediate variables as "date" types too. When you tell Excel to assign a "date" type value to a cell, there is no ambiguity. Excel stores dates as numbers, not strings. So if you present Excel with a "date" type, it understand what you mean.

[roadsweeper]

atomicrex said:

I usually do the full thing yyyy-mm-dd that way it always sorts in numerical order!!

I think that's the ISO standard (obviously there's a bit more to it than that) and I use that most frequently. It's also the best for sorting by date but alphabetically if you know what I mean?!

[Don]

ATG said:

Not entirely fair to blame it on Excel. What you are encountering is merely the tip of a the big dog poop iceberg that is handling dates as formatted bits of text. Simple text formats that look anything like dd/mm/yy are ambiguous ... for the first couple of weeks of each month ... +/- one century. On a Windows box there are too many places that you can set internationalisation config bollox to risk that approach of handling dates this way. Even if you go down the "1 Apr 06" route, evntually your machine will decide it's French and cease to understand "Apr". The solution is to abslutely minimise holding dates in strings. If you want to get the user to enter a date, get them to use a "microshite date and time picker" control, not a text box. The value of a date control is of a "date" type, not a "string" type. It is numerical, the number of days since 1 jan 1900, or thereabouts. Declare any intermediate variables as "date" types too. When you tell Excel to assign a "date" type value to a cell, there is no ambiguity. Excel stores dates as numbers, not strings. So if you present Excel with a "date" type, it understand what you mean.

Even if you do this it will still go horribly wrong at every "boundary" - the worst being when you try and assign a VB data into a parameter for a SQL Server stored procedure.

Our (web) app has to handle dates continually and we ended up having to write libraries of date handling code that ensures dates are passed around internally in a consistent format, translated at boundaries correctly and presented to the user in a consistent format. We settled on forcing dates into 00-XXX-0000 for the user interface. Since our app is not designed to be multi-lingual this is mostly OK for us.

[Jinx]

Could you not convert it to the number of days past 01/01/1900 (where 01/01/1900 = 1)hence when it gets dumped into the an excel date field, excel will "helpfully" convert it back into the local date format?

[AtomicRex]

Julian dates are a good way of pushing dates around....but a bugger to debug. Personally if you stick to dd-mmm-yyyy you cant go wrong.

[sstein]

Jinx said:

Could you not convert it to the number of days past 01/01/1900 (where 01/01/1900 = 1)hence when it gets dumped into the an excel date field, excel will "helpfully" convert it back into the local date format?

Just implemented this and it seems to be working great!! excel is taking it as a number and when i open excel and convert the column to date it is dd/mm/yy format!!

Thanks for all the replies, much appreciated


-

Stuart

[ATG]

Don said:

ATG said:

Not entirely fair to blame it on Excel. What you are encountering is merely the tip of a the big dog poop iceberg that is handling dates as formatted bits of text. Simple text formats that look anything like dd/mm/yy are ambiguous ... for the first couple of weeks of each month ... +/- one century. On a Windows box there are too many places that you can set internationalisation config bollox to risk that approach of handling dates this way. Even if you go down the "1 Apr 06" route, evntually your machine will decide it's French and cease to understand "Apr". The solution is to abslutely minimise holding dates in strings. If you want to get the user to enter a date, get them to use a "microshite date and time picker" control, not a text box. The value of a date control is of a "date" type, not a "string" type. It is numerical, the number of days since 1 jan 1900, or thereabouts. Declare any intermediate variables as "date" types too. When you tell Excel to assign a "date" type value to a cell, there is no ambiguity. Excel stores dates as numbers, not strings. So if you present Excel with a "date" type, it understand what you mean.

Even if you do this it will still go horribly wrong at every "boundary" - the worst being when you try and assign a VB data into a parameter for a SQL Server stored procedure.

Our (web) app has to handle dates continually and we ended up having to write libraries of date handling code that ensures dates are passed around internally in a consistent format, translated at boundaries correctly and presented to the user in a consistent format. We settled on forcing dates into 00-XXX-0000 for the user interface. Since our app is not designed to be multi-lingual this is mostly OK for us.

Yes, but it won't go wrong between Excel and VB. The trick here is to internationalisation issues and that is best tackled by avoiding any unnecessary strings. If you have to take dates between systems that use different numerical representations (SQL Server has day0=31 Dec 1899 rather than 1 Jan 1900 as for VB, IIRC) then ideally an ISO text format is next best. ODBC defines this escape sequence for dates { ts '1998-05-02 01:23:56.123' } which can be used for paramters in ADO and OLEDB