TeamMaclean
asked on
Intermittently getting day/month swapped when copying dates from Excel 2003 to a CSV text file using a Excel VBA macro
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).Ac
Range("A9999").Select
Selection.End(xlUp).Select
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
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).Ac
Range("A9999").Select
Selection.End(xlUp).Select
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
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
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
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.
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.
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)
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
CInt(sDateParts(1)), CInt(sDateParts(2))) + _
TimeSerial(CInt(sTimeParts
CInt(sTimeParts(1)), _
CInt(sTimeParts(2)))
End Function
>> 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
CInt(sDateParts(1)), CInt(sDateParts(2))) + _
TimeSerial(CInt(sTimeParts
CInt(sTimeParts(1)), _
CInt(sTimeParts(2)))
End Function
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.
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.
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.
>> 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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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?
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.
ya...it seems like i prefer your method FatBoy....i forgot about the difference in long date for different country.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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).