9

I have .csv file which is a time series of daily data, with several data points associated with each date.

When I manually open the file, the dates open correctly, as the date format dd/mm/yyyy.

When I open the file programmatically, the dates up to the 12th of each month are opened as mm/dd/yyyy, although the format remains dd/mm/yyyy (e.g. the 1st of July 1983 (1/7/1983), would be opened as the 7th of January 1983 (7/1/1983) - this isn't just a formatting issue, the Julian Date (days since 1 Jan 1901) associated with these dates also changes), and the dates after the 12th of each month are opened correctly, although as text rather than a date.

The data coming in as text is not an issue, however, the dates changing as soon as the file is opened is problematic. I could try to import the entire .csv file as comma delimited text rather than opening the file, however, it would be easier and faster if I could stop the dates from changing when I open the file.

Flder = InputBox("Copy and Paste Folder path here:")

Set FSO = CreateObject("Scripting.FileSystemObject")
Set SourcePath = FSO.GetFolder(Flder)

For Each File In SourcePath.Files        
    Workbooks.Open (File)

    FlNm = File.Name

    StrtCol = Workbooks(FlNm).Worksheets(1).Range(Cells(4, 1), Cells(4, 30)).Find ("Mean").Column

    Workbooks(FlNm).Worksheets(1).Range(Cells(1, 1), Cells(60000, 1)).Copy (Workbooks("Find Water Years V2.xls").Worksheets(1).Range("A3"))
    Workbooks(FlNm).Worksheets(1).Range(Cells(1, StrtCol), Cells(60000, StrtCol + 1)).Copy (Workbooks("Find Water Years V2.xls").Worksheets(1).Range("B3"))

    Workbooks(FlNm).Close
Next

The problem seems to occur at the line Workbooks.Open(File).

Improve this question
16
  • Can you define "open programmatically"? Do you mean via Excel automation? Or directly in VBA code? There's no way "opening the file" could change any content; it's something other than that, but you've posted nothing pertinent to that in your question. Opening a .csv file does not do anything to change the content in itself (for instance, in Notepad); post some code or other info that shows how you're reading it and what you're doing with the text you read. Otherwise, I'd suspect your question is going to be closed as "not a real question". :) Commented Mar 8, 2012 at 23:06
  • Sorry for the lack of clarity on the one Ken, code below. Commented Mar 8, 2012 at 23:15
  • Have to agree with Ken here. There is an underlying cause. What does the file look like in notepad at that point, how are you opening the file? Is it being pulled into a template. Pasted? Commented Mar 8, 2012 at 23:17
  • The file is opened directly into excel using Workbooks.Open(File) (see the above code that I just pasted). When the data is opened in Notepad the dates are correct, in dd/mm/yyyy format, the same as when the file is opened manually. Commented Mar 8, 2012 at 23:24
  • 2
    so, rep isn't high enough to answer my own just yet. The solution was to set the Local variant of Workbooks.Open to true, forcing it to parse in the data using the Local settings, rather than the default (US). If one of you want to answer the q feel free, otherwise I'll throw up the answer later this afternoon or tomorrow morning. Commented Mar 9, 2012 at 2:02

7 Answers 7

Reset to default
13

Since the question has already been answered by the OP in the comments but not posted as an official answer I'll put it here in case someone else misses it like I did.

workbook = workbooks.Open(filename, Local:= true)

By setting Local = true uses the local machines date format rather than assuming mdy so in locales such as Australia (dmy) it'll change the behavior of Open()

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thank you for clarifying this, as just had the same problem. While is obvious there is a local issue, is not as obvious what the fix is (unless you know each little function parameters out of your head). I can't see a reason why that option is not true by default.
Me either, given that when you open a file interactively it behaves as if Local:=True so I agree it should be the default
2

I experienced this problem with Office 365. With importing an csv file with Excel some dates dd/mm/jjjj (European) where imported as American dates mm/dd/jjjj. The csv file doesn't have any formatting of dates closed in.

By opening as follows:

Set xla = CreateObject("Excel.Application")
Set xlb = xla.Workbooks.Open(sFilePath, ReadOnly:=False, Local:=True)

The local settings where used, and problem solved :-)

Other options with opening a file are:

expression.Open (FileName, UpdateLinks, ReadOnly, 
                 Format, Password, WriteResPassword, 
                 IgnoreReadOnlyRecommended, Origin, 
                 Delimiter, Editable, Notify, Converter, 
                 AddToMru, Local, CorruptLoad)
Improve this answer

Comments

0

I could try to import the entire .csv file as comma delimited text rather than actually opening the file, however, it would be easier and faster if I could stop the dates from changing when I open the file.

It is still fast if you open it as CDT. You simply use .OpenText instead of .Open. Rest of the code remains same :)

Try recording a macro and you will see that the code looks somewhat like this.

Workbooks.OpenText Filename:= File, _
Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True
Improve this answer

2 Comments

My experience is OpenText doesn't work if the dates aren't formatted mdy
No. But you can enforce importing as a text, manipulate the text to have correct unambiguous format (preferable ISO, which is YYYY-MM-DD) and apply CDate on such manipulated string.
0

I found that user2981639 had the right idea, but I got a syntax error with 'Local: true' so I used 'local:=True' instead

This problem was driving me nuts because I also had 2 memo fields in the csv file. If I tried to format the dates in any way the memo fields would not import correctly, they would either be truncated to 255 characters or any embedded CRLF characters would cut the record up into pieces depending on how many there were.

Thanks guys for posting

Improve this answer

Comments

0

Here, you see a working code with example. This problem frustrated me as well for a while. I was opening a .txt file in excel and the date I had use for was converting from dd-mm-yyyy format to mm-dd-yyyy format. Below you see a solution. It lay in the last command (,Local := True). See below. Hope it helps.

Note that while trasferring .txt file to excel use File Origin as "xlWindows:
    Workbooks.OpenText Filename:= _
    ThisWorkbook.Worksheets("Reporting").Cells(3, 6), Origin:=xlWindows, _
    StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(49 _
    , 1), Array(50, 1), Array(67, 1), Array(80, 1), Array(93, 1), Array(106, 1), Array(119, 1)) _
    , DecimalSeparator:=",", ThousandsSeparator:=".", TrailingMinusNumbers:= _
    True, Local:=True

NOTE: rest above settings for array and decimal separator are my work specific - may not apply to you.

Improve this answer

Comments

0

I am also struggling to find an answer. However what resolves this problem is.:-

Local should be true. and workbook close save changes must be false. That should work.

Improve this answer

Comments

0

Just had this problem myself and found the above solution

 workbook = workbooks.Open(filename, Local:= true)

to be problematic in that it generates an error message. The MS documentation doesn't mention brackets or Workbook =, so I used this:-

 Workbooks.Open Path, local:=True

Worked form me, hope it helps you.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.