How to Use Excel VBA to Import a CSV File without Opening (3 Methods)

To illustrate the methods, we will use the following CSV file. The file consists of three columns: Salesman, Product, and Sales. We will import the data from the CSV file without opening it.

Method 1 – Applying VBA to Import a Single CSV File without Opening

STEPS:

Go to the Developer tab > Visual Basic or press Alt + F11 to open the Microsoft Visual Basic window.

In the Microsoft Visual Basic window, click on the Insert tab.
Select the option Module.

A blank VBA code window will appear. Enter 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

Click the Run or press the F5 key to run the code.

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

We get the CSV file loaded in our existing Excel worksheet.

Method 2 – Importing Multiple CSV Files without Opening Using VBA

STEPS:

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.
Select the option Module.

A blank VBA code window appears. Enter the following code in the 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

Press F5 or click the Run button to run the code.

The above action asks to select a folder.
Select a folder from our directory that contains multiple CSV files.
Click OK.

The CSV files from that folder have been imported into Excel files without being opened.

Method 3 – Inserting a VBA Command Button to Import a CSV File without Opening

STEPS:

Go to the Developer tab and select the option Design Mode.

Go to Insert > ActiveX Controls > Command Button.

On the active sheet, press the command button, as shown below.

To open the code window, double-click the command button.
Enter the following code in the 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.