English and American dates...

  • Thread starter Thread starter Nick
  • Start date Start date
N

Nick

Hiya

Dunno how many Americans out there are aware of this issue
but basically us Brits like to display our dates in a
dd/mm/yyyy format whilst I understand Americans use
mm/dd/yyy.

Excel understandably prefers to use American date formats
but my programs needs to use English.

I am copying data from a CSV file into an excel
spreadsheet using the copy and paste methods within VBA.
Unfortunately a date such as 01/04/2000 which is the first
of April 2000 ends up as 04/01/2000 which is the fourth of
January.

The code looks something like:

Workbooks.Open WorkBookName

ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Activate
LastColumnNum = ActiveCell.Column
LastRowNum = ActiveCell.Row

Range(Cells(1, 1), Cells(LastRowNum, LastColumnNum)).Copy
ActiveWorkbook.Close False

ThisWorkbook.Worksheets("Temp Data"). _
Range(PasteCell).PasteSpecial xlPasteAll

Could someone sketch out a code that would copy and paste
the data as seen in a text format. I can then write a mid
function which will do all the work for me.

Thanks in advance,

If this is a bit confusing say so and I'll explain further

Nick
 
Actually I might have sorted this using the xlPasteValue
variable.

This pastes the dates as a serial number.

I think I can retrieve the correct parts of the date using
the DAY, MONTH, YEAR functions.

Nick
 
Or just use a number format????

Actually I might have sorted this using the xlPasteValue
variable.

This pastes the dates as a serial number.

I think I can retrieve the correct parts of the date using
the DAY, MONTH, YEAR functions.

Nick
 
Back
Top