In this article, we will demonstrate different examples of using Excel VBA code to import a CSV file without opening it. Sometimes, we get our dataset in CSV format. However, the CSV format is not very user-friendly whether we open it with Excel or Notepad. Also, we might need to import data from a CSV file of our user and we don’t want to interrupt their files. So, it’s convenient to import data from a CSV file into an Excel file without opening it.
Excel VBA to Import CSV File without Opening: 3 Examples
Throughout this article, we will show you 3Â examples of using Excel VBA to import a CSV file without opening it. To illustrate our examples we will use the following CSV file. The file consists of 3 columns: Salesman, Product, and Sales. We will import the data from the CSV file without opening that file.
1. Apply VBA to Import Single CSV File without Opening in Excel
VBA Macro can import CSV files from device directories. Excel selects and imports CSV files from the device directory with a few lines of Macro code using the Application.GetOpenFilename statement.
STEPS:
- To begin with, go to the Developer tab > Visual Basic or press Alt + F11 to open the Microsoft Visual Basic window.
- In addition, In the Microsoft Visual Basic window click on the Insert tab.
- Select the option Module.
- Furthermore, a blank VBA code window will appear. Type the following code in that code window:
Sub csv_Import()
Dim wsheet As Worksheet, file_mrf As String
Set wsheet = ActiveWorkbook.Sheets("Single")
file_mrf = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Provide Text or CSV File:")
With wsheet.QueryTables.Add(Connection:="TEXT;" & file_mrf, Destination:=wsheet.Range("B2"))
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.Refresh
End With
End Sub- Next, click on the Run button or press the F5 key to run the code.
- Then, in the Macro The Application.GetOpenFilename statement accesses the device directory, where CSV files can be selected.
- Select the CSV file that we want to import and click on OK.
- Lastly, we get the CSV file loaded in our existing Excel worksheet.
2. Import Multiple CSV Files without Opening with VBA
In this method, we will use Excel VBA to import data from multiple CSV files without opening them. This example will show you how we can import all the CSV files of a particular folder by selecting that folder. Let’s see the steps to do this.
STEPS:
- First, press Alt + F11 or go to the Developer tab > Visual Basic to open the Microsoft Visual Basic window.
- In the Microsoft Visual Basic window click on the Insert tab.
- Next, select the option Module.
- Then, a blank VBA code window appears. Insert the following code in that blank code window:
Sub import_csv()
Dim file As FileDialog
Dim filePath As String
Dim csv As String
Dim wsheet As String
Application.DisplayAlerts = False
Application.StatusBar = True
wsheet = ActiveWorkbook.Name
Set file = Application.FileDialog(msoFileDialogFolderPicker)
file.Title = "Folder Selection:"
If file.Show = -1 Then
filePath = file.SelectedItems(1)
Else
Exit Sub
End If
If Right(filePath, 1) <> "\" Then filePath = filePath + "\"
csv = Dir(filePath & "*.csv")
Do While csv <> ""
Application.StatusBar = "Converting: " & csv
Workbooks.Open Filename:=filePath & csv
ActiveWorkbook.SaveAs Replace(filePath & csv, ".csv", ".xlsx", vbTextCompare), xlWorkbookDefault
ActiveWorkbook.Close
Windows(wsheet).Activate
csv = Dir
Loop
Application.StatusBar = False
Application.DisplayAlerts = True
End Sub- Furthermore, press the F5 key or click on the Run button to run the code.
- So, we can see that the above action asks to select a folder.
- After that, we will select a folder from our directory that contains multiple CSVÂ files.
- Now, click on OK.
- Finally, we can see that the CSV files from that folder have been imported into Excel files without being opened.
Read More: Excel VBA: Merge Multiple CSV Files into One Workbook
3. Insert VBA Command Button to Import CSV File without Opening
In the last example, to import a CSV file to an XLSX file, we’ll utilize a command button. We will insert the VBA code in the command button. We can also paste it into the Visual Basic window directly. So, without further delay, let’s get started on learning the entire process by following the instructions below.
STEPS:
- Firstly, go to the Developer tab and select the option Design Mode.
- Secondly, go to Insert > ActiveX Controls > Command Button.
- Thirdly, on the active sheet, draw the command button as shown below.
- Afterward, to open the code window, double-click the command button.
- Then, insert the following code in that code window:
Private Sub CommandButton1_Click()
Dim z As FileDialog
Dim zPath As String
Dim csv As String
Dim wsheet As String
Application.DisplayAlerts = False
Application.StatusBar = True
wsheet = ActiveWorkbook.Name
Set z = Application.FileDialog(msoFileDialogFolderPicker)
z.Title = "Folder Selection:"
If z.Show = -1 Then
zPath = z.SelectedItems(1)
Else
Exit Sub
End If
If Right(zPath, 1) <> "\" Then zPath = zPath + "\"
csv = Dir(zPath & "*.csv")
Do While csv <> ""
Application.StatusBar = "Converting: " & csv
Workbooks.Open Filename:=zPath & csv
ActiveWorkbook.SaveAs Replace(zPath & csv, ".csv", ".xlsx", vbTextCompare), _
xlWorkbookDefault
ActiveWorkbook.Close
Windows(wsheet).Activate
csv = Dir
Loop
Application.StatusBar = False
Application.DisplayAlerts = True
End Sub
NOTE:
You can also paste this code into the Module box. In that instance, press Alt + F11 to bring up the Visual Basic window, then choose Insert. There will be a drop-down menu. From there, select Module and paste the code into the Module window. Example-2 illustrates this procedure.
- Now, press Ctrl + S to save the code. Then close the code window.
- Moreover, deselect the Design Module in the Developer tab.
- Next, click on the Command Button.
- The above action will open a new dialogue box.
- After that, select the folder that contains the CSV file and press OK.
- In the end, we get the data of the CSV file imported into an Excel file.
Read More: Excel VBA to Convert CSV File to XLSX
Download Practice Workbook
You can download the practice workbook from here.
Conclusion
In conclusion, this tutorial shows 3 examples of using Excel VBA to import a CSV file without opening it. Put your skills to the test with the practice worksheet in this article. Please leave a remark below if you have any questions. Our team will try to react to you as soon as possible. In the future, keep an eye out for more innovative Microsoft Excel solutions.
