using vba copy data from .csv file into an excel spreadsheet

[vineeta]

Hi

I'm trying to copy (several columns and several rows) data from a .csv
file into an excel spreadsheet which also happens to contain columns of
dates.

I can't seem to be able to get around getting warped output in Excel.
Ie, the dates are copied and pasted just fine whilst others get
transferred from Australian dates (ie dd/mm/yyy) to American dates
(mm/dd/yyyy) and others do not even get recognised as numbers but
rather text.

I have tried using a couple of ways of getting around this for example,


(1) by formatting the data in the .csv file into dd-mmm-yy format
before copying and pasting.

(2) using a formula in the excel spreadsheet to =(required cell in the
.csv)

Each time the date gets warped.

However, this doesn't seem to be an issue when I manually cut and paste
the data or manually use a formula.

Has anyone else ever had this problem and managed to overcome it?

Much appreciated
Vineeta

 

[Tom Ogilvy]

When VBA touches the data, it uses a US centric interpretation of a date.
The usual way is to use the date serial number which is what is actually
stored and which isn't ambiguous. If you are accessing the cells
individually, use the Value2 property for dates, rather than the Value
property. (you can use the value2 property for all cells - as it won't be
different from Value for things that are not dates or currency.