Solved

Intermittently getting day/month swapped when copying dates from Excel 2003 to a CSV text file using a Excel VBA macro

Posted on 2004-09-22
14
3,741 Views
Last Modified: 2008-02-01
Hi,

I run a VBA macro to copy invoice data from an Excel (2003) worksheet to a CSV file for me to then later import to my accounting package. This imported data consists of figures and dates…

The problem is that every once in a while we will find the day and month of the imported data (within the CSV file) has been swapped around. It’s an intermittent problem that cannot be attributed to changes made, to the macro and/or excel template, between a valid date import & invalid / swapped date import.
i.e. the date in Excel reads – 02/10/2004, but sometimes when its imported it would read 10/02/2004 ??? It can happen 2 out of 10 times in a batch and next time without any problems…

 VBA steps:
1. First we replace the date in the particular sell with a value to make the date permanent & unchangeable for the specific invoice:

Selection.Copy
    Selection.PasteSpecial Paste:=xlValues  'Replace the contents with the actual Invoice Date permanently
    Application.CutCopyMode = False

2.We copy the range of sells containing the valid data to a CSV file, ready for importing into our accounting package:

   'Copy the remaining rows of the matrix to the summary, for transfer to MYOB
    Range("BA13:CO20").Select 'All the data left in the invoice matrix area
    Selection.Copy
    ChDrive "S:"
    ChDir "S:\ALL_DATA\MYOB.DAT"
    FileRequired = "CP_Summ.TXT"
    Workbooks(FileRequired).Activate
    Range("A9999").Select
    Selection.End(xlUp).Select          'Find last data row in Column A
    ActiveCell.Offset(2, 0).Select      'Select 2nd clear row below it
    Selection.PasteSpecial Paste:=xlValues
    ActiveCell.Select
    If Cells(2, 1) = "" Then            'Cell A2
        Rows("2:2").Select              'Row 2 should not be a blank row
        Selection.Delete Shift:=xlUp
    End If

   'Save Summary, TAB Delimited format for MYOB
    ActiveWorkbook.SaveAs filename:=FileRequired, FileFormat:=xlText, CreateBackup:=True
    ActiveWorkbook.Close                'Close the Tab delimited TXT file in MYOB directory

If anyone has any ideas, please help… as you can imagine it can have huge implications when invoice data gets imported with the wrong dates.

Cheers
Jan
0
Comment
Question by:TeamMaclean
  • 6
  • 4
  • 3
  • +1
14 Comments
 
LVL 1

Expert Comment

by:craduza
ID: 12130513
i've faced the same probs. but within MS Access and VB6. So i decided to use the string variable instead of using date variable.
and i did it with long date (using format "dd MMMM yyyy"), if i want to use the date value i'll convert the string to date value...(1/March/2004 can't be assumed as 3/January/2004 if u use the conversion), so it will always be 1/3/2004 (in dd mm yyyy) or 3/1/2004 (in mm dd yyyy).
0
 
LVL 8

Expert Comment

by:rajaloysious
ID: 12131544
Further more to be precise..
here are the links which deals with multiple version of MS Office products...
http://www.kbalertz.com/kb_Q292491.aspx
http://www.kbalertz.com/Feedback.aspx?kbNumber=290576

Hope this answers ALL your questions.
Cheers. Bye
0
 
LVL 10

Expert Comment

by:fds_fatboy
ID: 12131631
An alternative and simple and locale independent method is to change your date formats to numeric (0 dec places) - or General will probably do before exporting. This will, for example give 38272, 38331 respectively for 10 February 2004 and  2 October 2004.

If the app that reads the csv is in VB and uses the CDate function it will first try and read in based on the machine's locale, and failing that it will swap day and month around. e.g. using my locale settings (dd/mm/yyyy).

CDate("10/02/2004") gives 10/02/2004 (10 Feb 2004)
CDate ("31/12/2004")  gives 31/12/2004 (31 Dec 2004)  
CDate ("12/31/2004")  gives 31/12/2004 (31 Dec 2004)  - oops.

To get to the root of the problem - not just work around it - first look at the dates that are failing are do they follow a pattern e.g. day numbers greater than 12?

Consider looking at the locales on your machines are.

Remember that passing dates using strings is inherently dangerous due to formatting and locale differences
You might also consider using the "independent" internet date format - but you will have to translate it at either end - I've got some VB code somewhere to do it.
0
 
LVL 1

Expert Comment

by:craduza
ID: 12132034
what if the user machine setting differ from the designer locale settings?..i've faced this things so many times, and for me using the long date (dd MMMM yyyy) is the best solution. there will be NO error if the locale setting is different between both user and designer. (these are my experience)
0
 
LVL 10

Expert Comment

by:fds_fatboy
ID: 12133573
craduza

>> what if the user machine setting differ from the designer locale settings?..i've faced this things so many times, and for me using the long date (dd MMMM yyyy) is the best solution. there will be NO error if the locale setting is different between both user and designer. (these are my experience

I disagree, because if the MMMM is not the same language on both locales it will fail.

For example I was working in a department and we asked our customers to send us orders in a CSV the dates being in ddmmmyyyy format. This worked until we got an order from Turkey which failed.

e.g. An English date in dd MMMM yyyy format: 25 December 2004. The same date using the same format and Malaysian (Malay) locale settings gives 25 Disember 2004. In both locales, the date.

If passing the date as the integer part of a number, you can avoid all locale problems (see above) as long as both applications use the same numbering system. In excel, the dates/times are held in 32bit real numbers (as is VB). The integer part is the date (number of days past a given date), the decimal is the time past midnight. The value of each number is the same for a given date/time regardless of the system locale.

I use the w3c standard - which is derived from the international standard ISO 8601 date standard.
http://www.iso.ch/iso/en/prods-services/popstds/datesandtime.html#two

This is the code I use nowadays - for XML in and out of VB apps. The date :

Public Function VBDate2XMLDate(ByVal VBDate As Date) As String
    VBDate2XMLDate = Format$(VBDate, "YYYY-MM-DD\THH:NN:SS")
End Sub

Public Function XMLDate2VBDate(ByVal xmlDate As String) As Date
    Dim sDateTime() As String
    Dim sDateParts() As String
    Dim sTimeParts() As String

    sDateTime = Split(xmlDate, "T")
    sDateParts = Split(sDateTime(0), "-")
    sTimeParts = Split(sDateTime(1), ":")

    XMLDate2VBDate = DateSerial(CInt(sDateParts(0)), _
                     CInt(sDateParts(1)), CInt(sDateParts(2))) + _
                     TimeSerial(CInt(sTimeParts(0)), _
                     CInt(sTimeParts(1)), _
                     CInt(sTimeParts(2)))
End Function

0
 
LVL 4

Expert Comment

by:Prestaul
ID: 12134237
The issue goes further for this user.  Fatboy is using a great method for serializing a date so that it doesn't get warped by the users local settings and VB's sometimes insufficient formatting procedures, but I assume that these dates are being entered by hand into an Excel spreadsheet...  This is where the problem occurs as Excel will automatically convert the date to a numeric value based on local settings before any of his VBA code ever sees the date!

Try it out:  Open a new sheet and place today's date 9/23/2004 into a cell then copy it and paste the VALUE into another cell (as he is doing programmatically) and you'll see 38253.  Use something like 3/2/2004 and the number actually stored will vary depending on local settings.
0
 
LVL 10

Expert Comment

by:fds_fatboy
ID: 12134399
Prestaul
>> Use something like 3/2/2004 and the number actually stored will vary depending on local settings

Yes I understand this - but you have to assume that the user is typing in the dates in the locale that their machine is in. For instance if I type the date on my machine (UK locale) as 3/2/2004 I mean 3 Feb 2004. If I wanted to enter the same date and my machine was US locale I would have to enter 2/3/2004. If I get this the wrong way around then there are only four possibilities:

1. My system has the wrong locale settings;
2. I have typoed;
3. I am stupid.
4. All of the above

Unfortunately when we pick up the data, there is nothing we can do about it - or detect it.

PS I suspect it is number 3.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 10

Expert Comment

by:fds_fatboy
ID: 12134442
PS I was right - it was 3. I see what you mean now I'm reading again. And yes set the format to dd mmm yyyy in excel so that when the user types in they know what they are typing in. Yes. Then serialise the date and send it to the CSV.
0
 
LVL 4

Accepted Solution

by:
Prestaul earned 50 total points
ID: 12134568
Yes, #3 is possible.  Is it not also possible that a shared sheet, across locals, might get the date typed in by someone with UK settings in the US format for consistency's sake (assuming there are explicitly formatted dates in the sheet somewhere).  Most users would never realize that there is something other than a few numbers and some dashes stored and wouldn't realize that a date typed in that way is actually interpretted by the computer to be a different date.  People think that what they type into Excel is static unless they put in a formula.
0
 
LVL 10

Expert Comment

by:fds_fatboy
ID: 12134777
Prestaul:

Yes this is true. And more to point at number 3. But this would be a poorly thought out design - and the only solutions (I can think of off the top of my head) would be not to explicitly format dates to a specific locale in this situation (use "short date" etc); or enter the cells as strings and convert the strings to dates using a formula/macro elsewhere.

My own view on software design is to respect people's system settings. If they want black writing on a black background, then that's their lookout.

>> People think that what they type into Excel is static unless they put in a formula.
Yes, if these things were completely simple what would I do for a living when people got themselves into trouble?
0
 
LVL 4

Expert Comment

by:Prestaul
ID: 12134862
Agreed, Fatboy.  Most of this discussion probably did nothing by satisfy our own interests though.  There is more than enough information here for the question's author to figure something out or re-ask her question with a few more details.  I don't think that we can acheive anything more until the author makes another visit to the discussion.
0
 
LVL 1

Expert Comment

by:craduza
ID: 12138962
ya...it seems like i prefer your method FatBoy....i forgot about the difference in long date for different country.
0
 
LVL 1

Assisted Solution

by:craduza
craduza earned 50 total points
ID: 12226487
what about this situation?...one user locale setting is mm/dd/yyyy and other user locale setting dd/mm/yyyy. and they are passing the value to a database. in this case if they are using  their own locale setting then the database will store using its locale setting. is there any where to standardize it?.....i dunno about the ISO that said before.
0
 
LVL 10

Assisted Solution

by:fds_fatboy
fds_fatboy earned 400 total points
ID: 12228323
>> what about this situation?...one user locale setting

This is covered using my previous example:

The database should store dates as datetimes or dates (locale independent) not strings. In most DBMSs date/datetimes are Integer/Real real numbers.
The integer component contains the number of days after an arbitary start date, and the decimal component the time as a proportion of 24 hours i.e. 0.25 -> 0.25*24 hours = 6 hours -> 6 O'clock in the morning.

e.g.

A user in Locale 1  enters a date in mm/dd/yyyy format: 11/03/1966  (3 November 1966). It is stored on the database as 24414 (RDBMS dependent).
A user in Locale 2 using the same RDBMS enters a date in dd/mm/yyyy format: 03/11/1966  (3 November 1966). It is stored on the database as 24414.

The number 24414 is locale independent unfortunately it is not RDBMS dependent. Oracle will do it differently from SQL/Server. SQL/Server differently from Access. etc. My example, 24414 is the excel representation for that date.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now