How to Open CSV File in VBA

Although it is not strictly and directly defined, Excel relates to all other Office programs and file types. This is best visible and achievable through VBA.

In the example below, we will show how to open the CSV (comma separated values) file with VBA, and how to use the FileDialog option to choose a CSV file among all the files in the folder.

Open CSV File with VBA

To simply open the CSV file through VBA, we first need to input the code in your module. To get there, we need to click ALT + F11 and then right-click anywhere on the left side of the window that appears, go to Insert >> Module:

When we insert a module, the following code needs to be inserted:

Sub Open_CSV_File()
    Workbooks.OpenText Filename:= _
                       "C:\Users\Harun\Documents\CSV file\Book2.csv", Local:=True
End Sub

Sub Open_CSV_File()

Workbooks.OpenText Filename:= _

"C:\Users\Harun\Documents\CSV file\Book2.csv", Local:=True

End Sub

This Is how our code looks in the module:

This is a simple code. To comprehend it, we need to realize that CSV files are text files whose values are separated by a comma, tab, space, or semicolon. We treat them as such in our code as well, so we start it with:

Workbooks.OpenText Filename:=

1	Workbooks.OpenText Filename:=

Then we need to insert the location of the file itself (we can go to the file, right-click on it, go to Properties, and then copy the Location):

Of course, on the location, we need to add the name of the file and its type (in our case .csv). We add the option Local to save files against the language of Excel.

If we go on and execute our code from the Module (by clicking F5 or the green button in this picture):

When we do this, our CSV will be opened with an Excel file:

Open CSV File with File Dialog

There is also a great built-in function in VBA that can allow you to open the file picker and choose what file you want to open.

The code to do this is as follows:

Sub csv_FileDialog()
    Dim wb          As Workbook
    Dim file_name   As String
    Dim FilterIndex As Integer
    Dim filter      As String
    Dim fd2         As FileDialog
    Set fd2 = Application.FileDialog(msoFileDialogFilePicker)
    With fd2
        .AllowMultiSelect = FALSE
        .Filters.Add "Text Files", "*.csv", 1
        .FilterIndex = 1
        .Filters.Add "text files| ", "*.csv"
        If .Show = -1 Then file_name = .SelectedItems(1)
    End With
    If Len(file_name) > 0 Then
        Workbooks.Open file_name
        Set wb = ActiveWorkbook
    End If
End Sub

Sub csv_FileDialog()

Dim wb As Workbook

Dim file_name As String

Dim FilterIndex As Integer

Dim filter As String

Dim fd2 As FileDialog

Set fd2 = Application.FileDialog(msoFileDialogFilePicker)

With fd2

.AllowMultiSelect = FALSE

.Filters.Add "Text Files", "*.csv", 1

.FilterIndex = 1

.Filters.Add "text files| ", "*.csv"

If .Show = -1 Then file_name = .SelectedItems(1)

End With

If Len(file_name) > 0 Then

Workbooks.Open file_name

Set wb = ActiveWorkbook

End If

End Sub

This is what our code looks like in the module:

For the first part of the code, we are simply declaring variables:

Dim wb As Workbook
Dim file_name As String
Dim FilterIndex As Integer
Dim filter As String
Dim fd2 As FileDialog

Dim wb As Workbook

Dim file_name As String

Dim FilterIndex As Integer

Dim filter As String

Dim fd2 As FileDialog

The particularly important one is fd2, which is declared as a file dialog. When you start writing

„Set fd2 = Application.FileDialog(“ and then press CTRL + SPACE (which is a shortcut for IntelliSense- code editing features), you will see multiple options to choose from:

From these options in the picture above, we will choose msoFileDialogFilePicker.

Application.FileDialog gets us to the picker of files, and folders, or to open or save files. The same options that we have when we go to the File tab.

Then we declare the following things to do with the file that will be open:

With fd2
    .AllowMultiSelect = False
    .Filters.Add "Text Files", "*.csv", 1
    .FilterIndex = 1
    .Filters.Add "text files| ", "*.csv"
    If .Show = -1 Then file_name = .SelectedItems(1)
End With

With fd2

.AllowMultiSelect = False

.Filters.Add "Text Files", "*.csv", 1

.FilterIndex = 1

.Filters.Add "text files| ", "*.csv"

If .Show = -1 Then file_name = .SelectedItems(1)

End With

We declare that we will not allow multiple files to be selected. Then we declare that the files that can be opened are the text file, i.e. CSV.

For the last thing, we declare that if we find the file and select it in the folder, then it will be equal to the variable file_name.

For the last part of the code, we use the IF function to define that, if the user has chosen the CSV file, for it to be open:

If Len(file_name) > 0 Then
    Workbooks.Open file_name
    Set wb = ActiveWorkbook
End If

If Len(file_name) > 0 Then

Workbooks.Open file_name

Set wb = ActiveWorkbook

End If

To save our code in the worksheet for the user, we will get back to the Excel file, go to the Developer tab >> Controls >> Insert >> Button (which is the first option in Form Controls):

We will then drag and drop the button, and will be presented with the following window when we are done:

In this window, we will choose the macro that will be connected to the button. We will choose csv_FileDialog, as this is the name of the macro we created for opening the CSV files. When we click OK, our button will be created. We will right-click on it to change its name to “Open CSV file”:

Now, when we click on this button, we will have a file picker:

We can see that this file picker searches for Text Files (*.csv) just as we defined in our code. When we select the file and click Open, our CSV file will be opened.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Post Views: 8,634