Date Format Changes on Copy
 

Has anyone come across this one...?

Whilst copying from a .csv file into an existing Excel workbook a
strange things happens...

The first column of my range loses its date format and changes from a
UK (the one I want) to a US format, peculiarly only on some cells!
Setting the format on the destination cells first first makes no
difference. I'm doing the copy in VBA using:

wkbSource.Sheets(1).UsedRange.Copy _
wkbTarget.Sheets("Timesheet Actuals").Range("A2")

Even more peculiarly, if I carry out the copy by hand the problem
doesn't occur...!

TIA

Jon C

Date Format Changes on Copy
 

VBA works in US English. So if the date can be interpreted as US, it is.
This is not true for Excel itself.

However, I doubt the problem is with the code you show, but in the code that
opens the CSV file. If you have xl2002 or later, you can
use the arguments for Data=Import External Data to help control this I
believe.

--
Regards,
Tom Ogilvy


"Jon C" wrote in message
oups.com...
Has anyone come across this one...?

Whilst copying from a .csv file into an existing Excel workbook a
strange things happens...

The first column of my range loses its date format and changes from a
UK (the one I want) to a US format, peculiarly only on some cells!
Setting the format on the destination cells first first makes no
difference. I'm doing the copy in VBA using:

wkbSource.Sheets(1).UsedRange.Copy _
wkbTarget.Sheets("Timesheet Actuals").Range("A2")

Even more peculiarly, if I carry out the copy by hand the problem
doesn't occur...!

TIA

Jon C

Date Format Changes on Copy
 

Thanks Tom.

I'm using xl2002. The code's pretty straight forward:

Set wkbTarget = ActiveWorkbook

'
' Select file to load
'

fName = Application.GetOpenFilename("Replicon Files (*.csv), *.csv")

If fName < False Then
Set wkbSource = Workbooks.Open(fName)
wkbSource.Activate

Rows("1:3").Select
Selection.Delete Shift:=xlUp

wkbSource.Sheets(1).UsedRange.Copy _
wkbTarget.Sheets("Timesheet Actuals").Range("A2")

wkbSource.Close SaveChanges:=False

End If

......

Is there an argument I can add to the Workbook Open?

I looked up the 'Import' method in xl help and it said if should be
used! I guess I'm not looking in the right place.

Jon C

Date Format Changes on Copy
 

Sorry, xl Help said Import should NOT be used...

Jon C

Date Format Changes on Copy
 

Try setting the Local argument to the workbook.open method to True.

Sorry, xl Help said Import should NOT be used...
Should not be used for what? Importing?

--
Regards,
Tom Ogilvy


"Jon C" wrote in message
oups.com...
Sorry, xl Help said Import should NOT be used...

Jon C