I have posted this in the Microsoft Community Forum and have been directed here.

I am using VBA to open a .csv file programmatically.

The .csv file contains text, date and number fields/columns.

When this file is opened manually and not as part of the VBA routine, the date fields show the dates in the dd/mm/yyyy format with no text identifier (floating apostrophe) before the date text.

So a date of 1st May 22 is shown as 01/05/2022.

Below is a screen shot of the .csv file when opened manually - Note Columns I and J show the correct dates

The VBA process has been built to open this file when identified by the user using the 'Application.GetOpenFilename' function.

The file is then opened programmatically.

When I place a break in the code to view the opened file before it is queried, some dates do not reflect the dates when the .csv file is opened manually.

E.g. A date of 01/05/2022 is shown if the programmatically opened file as 05/01/2022

Basically, where the DAY and MONTH when swapped can represent a date value, they are swapped.

The code being used to 'get' the file containing the required data is below

***strEDFile = Application.GetOpenFilename(, , "Select the file containing the Booking Data", "Select", False)

If strEDFile = "False" Then 
   MsgBox ("A file to import was not selected"), vbInformation, "NO SELECTION MADE" 

   Exit Sub 
   Else 
End If 

Application.ScreenUpdating = False 
Application.Calculation = xlManual 

Set wbBD = ActiveWorkbook 

Set wsBD = wbBD.Sheets("Booking Data") 

Set rngBD = wsBD.Range("BD_Start") 

Set wbED = Application.Workbooks.Open(strEDFile) 

Set wsED = wbED.ActiveSheet***

When the file is opened via the above code, the days and months are swapped around in columns I and J - See image below

The solution is to save the .csv file as a .xlsx file before uploading via the VBA process, but this shouldn't need to be done.

CodePudding user response：

The way that the date is interpreted in a .csv file is dependant on the locale settings for Windows.

The way to work around it, is to use the OpenText method.
During the import set the format that is used for each column by using the XlColumnDataType.

Value - Name - Description
1 - xlGeneralFormat - General
2 - xlTextFormat - Text
4 - xlDMYFormat - DMY date format

In the example code below, all columns are imported as Text (2).
Except for the two date columns that are imported as dates in the DMY (4) locale format.

If you are using Excel in Office365, I would recommend looking at PowerQuery (Data - Get Data - From File - From Text/CSV) to import the data.

Sub Example()

    strEDFile = Application.GetOpenFilename(, , "Select the file containing the Booking Data", "Select", False)
    
    If strEDFile = "False" Then
       MsgBox ("A file to import was not selected"), vbInformation, "NO SELECTION MADE"
    
       Exit Sub
       Else
    End If
    
    Application.ScreenUpdating = False
    Application.Calculation = xlManual
    
    Set wbBD = ActiveWorkbook
    
    Set wsBD = wbBD.Sheets("Booking Data")
    
    Set rngBD = wsBD.Range("BD_Start")
    
    Application.Workbooks.OpenText _
            Filename:=strEDFile, _
            StartRow:=1, _
            DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, _
            Comma:=True, _
            FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7, 2), Array(8, 2), _
            Array(9, 4), Array(10, 4))
'Use the OpenText method to open the .csv file

    Set wbED = ActiveWorkbook
'Set a reference to the opened workbook
'OpenText does not return a reference

    Set wsED = wbED.ActiveSheet

End Sub

CodePudding user response：

This is a classic problem where we need to separate the Data Value from what we seen on the screen - saving the file as a .xlsx is only adding an unnecessary step. You could try opening the CSV using a SQL query, then at least you will get the value returned as a Date and you can do what you like with it. See here for an example of how to do that return csv file as recordset Alternatively, read the CSV line by line