Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 2005 > Pro Visual Basic 2005
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Pro Visual Basic 2005 For advanced Visual Basic coders working in version 2005. Beginning-level questions will be redirected to other forums, including Beginning VB 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Pro Visual Basic 2005 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old January 25th, 2009, 04:57 AM
Authorized User
 
Join Date: Aug 2007
Location: MANAMA, MANAMA, Bahrain.
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to zrtv
Default Excel to vb.net date field problem

Hi All,
I am facing a strange problem, when I import data from excel to vb.net my date field format changes. In excel, date field is "dd-mm-yy" but when i import this cell to vb.net grid
it changes, if the day value is more than the month (ie if day=13 and month=12) then it gives correct data but if the month less than the day (ie if day=11 and month 12) then it changes.

my code is:

dim colD as integer = 4

oXLApp = New Excel.Application 'Create a new instance of Excel
oXLBook = oXLApp.Workbooks.Open(txtFileSource1.Text.ToString , , True, True)
oXLSheet = oXLBook.Worksheets(1) 'Work with the first worksheet
lastFileSourceIndex = 1
Static breakOut AsBoolean, lastGridRow&, IOFlag$
breakOut = False
lastFileRowIndex = 1
lastGridRow = flvwDTR.Rows - 1
WhileNot breakOut
IOFlag = CStr(oXLSheet.Cells(lastFileRowIndex, colF).Value)

If Len(IOFlag) > 0 Then
atdate = CStr(Format(DateTime.Parse(oXLSheet.Cells(lastFileRowI ndex, colD).value, Globalization.CultureInfo.CreateSpecificCulture("en-CA").DateTimeFormat), "dd/MM/yyyy"))
msgbox (atdate)
lastFileRowIndex = lastFileRowIndex + 1
Else
breakOut = True
EndIf
EndWhile
and the result is

excel file date msgbox result
10-11-08 11-10-2008
09-11-08 11-09-2008

13-11-08 13-11-2008
14-11-08 14-11-2008



Please help on this

thanks in advance
  #2 (permalink)  
Old January 25th, 2009, 05:27 AM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Not strange at all.

Your Excel date is being imported into VB *AS A STRING*. And VB's *DEFAULT* string-to-date translation is USA date format: MM-DD-YYYY. So if the first number is 1 through 12, VB accepts it as a month. It's only when the number is more than 12 that VB says "oh, this must be a day number, so let me change to DD-MM-YYYY".

Now, the CULPRIT is here, of course:
Code:
DateTime.Parse(oXLSheet.Cells(lastFileRowI  ndex, colD).value, Globalization.CultureInfo.CreateSpecificCulture("en-CA").DateTimeFormat)

And it *SHOULD* happen that, by specifying the correct Culture (as you are doing), you would get DD-MM-YYYY by default. But...

Perhaps MS thinks that Canadian date format matches USA format??? That is, thinks the "en-CA" defaults to MM-DD-YYYY???

One easy thing to try would be to use "en-UK" instead of "en-CA". See if that changes things.

By the way, you could improve your code's performance quite a bit by *NOT* doing that "lookup by culture name" each and every time through the loop!!!

Assign the culture at the top of the page ONE TIME, to a local variable, and then use the local variable throughout.

Always try to avoid anything that smacks of "lookup by name string" inside of loops.



  #3 (permalink)  
Old January 25th, 2009, 05:31 AM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Just out of curiosity...

Did you try *NOT* parsing the date coming from Excel??? If that field in Excel is *ALREADY* a DateTime value, you might be able to use it as-is.

That is:
Code:
atdate = Format(oXLSheet.Cells(lastFileRowIndex, colD).value,"dd/MM/yyyy")


Never worked with Excel as an object in .NET, but certainly works this way with Access. If the sheet already knows the value is a date, no transform is needed.

Finally, there was never any reason for your CSTR( ) wrapper, there. Format *does* product a String. Always.


  #4 (permalink)  
Old January 25th, 2009, 05:36 AM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Okay, one more comment...

Your code could be more efficient and smaller.

Thus:
Code:
'  (no need to initialize lastFileRowIndex before loop)

For lastFileRowIndex = 1 To 999999999
    IOFlag = CStr(oXLSheet.Cells(lastFileRowIndex, colF).Value)
    If Len(IOFlag) = 0 Then Exit For
    atdate = ...whatever you decide on ...
    ... and any processing using atdate of course ...
Next
  #5 (permalink)  
Old January 25th, 2009, 08:39 AM
Authorized User
 
Join Date: Aug 2007
Location: MANAMA, MANAMA, Bahrain.
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to zrtv
Default Re: Excel to vb.net date field problem

Hi Old Pedant,
Thanks for the quick and detailed reply,

Unfortunately I already tried both the suggetion you given , but no luck

ie : atdate = Format(oXLSheet.Cells(lastFileRowIndex, colD).value,"dd/MM/yyyy")
atdate=CStr(Format(DateTime.Parse(oXLSheet.Cells(lastFileRowI ndex, colD).value, Globalization.CultureInfo.CreateSpecificCulture("en-UK").DateTimeFormat), "dd/MM/yyyy"))


Kindly help to resolve this issue
  #6 (permalink)  
Old January 25th, 2009, 04:35 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Kind of out of ideas. It looks like the (incorrect) conversion is taking place inside the Excel.Application DLL code, before it ever gets to your code.

You may need to see if there is some way to tell Excel.Application to treat your dates as dd/mm/yyyy format.

You *might* try a bit of debugging, though I tend to think it's going to just confirm what you already know.

For example:
Code:
MsgBox( TypeName(oXLSheet.Cells(lastFileRowIndex, colD)) )
and/or
MsgBox( TypeName(oXLSheet.Cells(lastFileRowIndex, colD).Value) )

 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
VB Net MSAccess need to get Tbl Field stuff jhkraft Pro VB Databases 0 March 13th, 2008 05:11 PM
Get Previous Date in vb.net kostaskir VB How-To 1 August 31st, 2007 09:21 AM
date field problem in access asp varia_mahesh Classic ASP Basics 1 June 6th, 2007 07:02 PM
date field problem access asp varia_mahesh Access ASP 0 June 6th, 2007 08:33 AM
sorting problem with a date field in gridview jwrz200t Visual Studio 2005 0 September 21st, 2006 01:19 PM



All times are GMT -4. The time now is 01:59 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.