VBA to Open CSV and Avoid Date Error

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

 


	


	

	

		

			Posted on 					

		
				
			Categories: Data, Excel Blog, Macros, Tips & Tricks		
				
				
		
	

                        



					
	
				
Please note: I reserve the right to delete comments that are offensive or off-topic.




	


		

		
Leave a Reply 
Your email address will not be published. Required fields are marked *
 
 
 



 


 


 


	

	
This site uses Akismet to reduce spam. Learn how your comment data is processed.

			

			8 thoughts on “VBA to Open CSV and Avoid Date Error”		


		
		
    
			
	
  1. 
		
    
			

			
    
				
    Thanks so much for posting this solution. Has helped me correct an issue with a macro so I am really pleased.
    

    Jennifer
    
			
    

			
    
				Reply			
    
		
    

	

    2. 

	
  2. 
		
    
			

			
    
				
    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!
    
			
    

			
    
				Reply			
    
		
    

	

    3. 

	
  3. 
		

	

    4. 

	
  4. 
		
    
			

			
    
				
    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.
    
			
    

			
    
				Reply			
    
		
    

	

    5.