CSV import casuing Day & Month Swapping around when day is less than 13

JonRowland

Active Member
Joined
May 9, 2003
Messages
417
Office Version
  1. 365
Platform
  1. Windows
Hi ppl,

Same problem http://www.mrexcel.com/forum/showthread.php?t=560649&highlight=IMPORT+CSV+DATE although I'm using VBA to import CSV file into a Template document.

The CSV contains dates formatted as dd/mm/yyyy hh:mm:ss and when the csv data is copied into my template file the date changes to mm/dd/yyyy hh:mm:ss when the day is less than 13. This is causing me a headache now and wonder how I can ensure that the dates are copied in as expected.

My import code is
<code>
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim wsSrc As Worksheet
Dim MyPath As String
Dim strFilename As String

Application.DisplayAlerts = False
Application.EnableEvents = False
Application.ScreenUpdating = False

Workbooks.Open Filename:= _
"H:\Process\Temp\Template.xlsm" ' This is a blank file used for
' the purpose of importing souce data pre-processing


MyPath = "H:\Process" ' change to suit
Set wbDst = Workbooks("Template.xlsm")

strFilename = Dir(MyPath & "\*.csv", vbNormal)

If Len(strFilename) = 0 Then Exit Sub

Do Until strFilename = ""

Set wbSrc = Workbooks.Open(Filename:=MyPath & "\" & strFilename)

Set wsSrc = wbSrc.Worksheets(1)

wsSrc.Copy After:=wbDst.Worksheets(wbDst.Worksheets.Count)

wbSrc.Close False

strFilename = Dir()

Loop

' Deletes original sheet in Template.xlsx which isn't needed
wbDst.Worksheets(1).Delete
</code>

Any ideas plseeeee.

Thx
Jon
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
I'd recommend recording a macro in a new workbook to open the file and format the column in question as date.

Then replace the <code>Workbooks.Open(Filename:=MyPath & "\" & strFilename) portion of your code with the Workbooks.OpenText code.
</code>
 
Upvote 0
Won't this imply that the date fields are always in the same place?

I forgot to say that I am opening more than 1 CSV & each looks different.

What I don't get is why if I open by double clicking there isn't a problem but via VBA there is.

Jon
 
Upvote 0
That sounds really annoying. Seems like you need some way of telling Excel what kind of things it's meant to be importing. The first thing that comes to mind is storing the data in a database (Access?) where you can assign each column a datatype. Is this possible in your case?
 
Upvote 0
Hi

You should be able to get around this by setting the "Local" argument to true e.g.

Set wbSrc = Workbooks.Open(Filename:=MyPath & "\" & strFilename, Local:=True)

There seems to be a lack of information regarding this argument in the help. The online help for 2007 shows this description of the Local argument but for the OpenText method:

Specify True if regional settings of the machine should be used for separators, numbers and data formatting.

Based on my testing (2003 and 2010) it appears as though is also how the Local argument works with the Open method.


HTH
DK
 
Last edited:
Upvote 0
Dk,

All I can say is yahooooooo. Works a treat from the tests I've done.

Cheers bud
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top