You can create a macro to open a CSV file. One problem you may face is that dates are treated as US dates. A simple change can fix this.
The code to open a CSV can look like this
Sub Open_CSV() Dim strPath As String strPath = "E:A4\Data\Data.csv" Workbooks.Open Filename:=strPath End Sub
Unfortunately VBA defaults to using the US date format mm/dd/yy when it opens a CSV file.
Only the US uses that date structure, everyone else uses the dd/mm/yy.
The solution is easy, you just add an option to the end of the Open statement to instruct Excel to use the local date format.
You add ,Local:=True to the end of the Open statement. The adjusted code for the above macro is shown below.
Sub Open_CSV() Dim strPath As String strPath = "E:A4\Data\Data.csv" Workbooks.Open Filename:=strPath, Local:=True End Sub
Related Posts
Thanks so much for posting this solution. Has helped me correct an issue with a macro so I am really pleased.
Jennifer
You are welcome Jennifer – glad it helped. Have you tried Power Query? It really simplifies importing data via a CSV file.
I’ve been banging my head against a wall for a significant period of time, trying all manner of code acrobatics to get this to work, and couldn’t for the life of me figure out what was going wrong. This here is exactly what was going wrong, and the fix was as simple as typing “, Local:=True” into my code.
Thanks so much!
Hi David – happy to help – yes VBA can be frustrating.
this solution is awesome, solved my issue in VBA
Glad it helped.
Have you tried out Power Query, it can be better than VBA in automating data importation.
Thank you very much for this. It explains a lot of the niggles I have had over the years and how I can fix them.
Hi James
These days Power Query is the best way to import CSV files. More control over the data types.
Regards
Neale