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
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…
1. First we replace the date in the particular sell with a value to make the date permanent & unchangeable for the specific invoice:
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
FileRequired = "CP_Summ.TXT"
Selection.End(xlUp).Select 'Find last data row in Column A
ActiveCell.Offset(2, 0).Select 'Select 2nd clear row below it
If Cells(2, 1) = "" Then 'Cell A2
Rows("2:2").Select 'Row 2 should not be a blank row
'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.