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 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:
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
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
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
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
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.