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.

Read More: Excel VBA: Merge Multiple CSV Files into One Workbook

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.

• Press Ctrl + S to save the code. Close the code window.
• Unselect the Design Module in the Developer tab.

• Click on the Command Button.
• A new dialogue box will open.
• Select the folder that contains the CSV file and press OK.

The data of the CSV file is imported into an Excel file.

Read More: Excel VBA to Convert CSV File to XLSX

Download the Practice Workbook

You can download the practice workbook from here.

Related Articles

• Excel VBA to Read CSV File into Array
• Excel VBA to Read CSV File Line by Line