Excel VBA to Import CSV File without Opening (3 Suitable Examples)

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.


Download Practice Workbook

You can download the practice workbook from here.


3 Examples of Excel VBA to Import CSV File without Opening

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.

3 Examples of Excel VBA to Import CSV File without Opening


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.

Apply VBA to Import Single CSV File without Opening in Excel

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

Apply VBA to Import Single CSV File without Opening in Excel

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

Apply VBA to Import Single CSV File without Opening in Excel

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

Read More: How to Import CSV into Existing Sheet in Excel (5 Methods)


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.

Import Multiple CSV Files without Opening with VBA

  • In the Microsoft Visual Basic window click on the Insert tab.
  • Next, select the option Module.

Import Multiple CSV Files without Opening with VBA

  • 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 to Convert CSV File to XLSX (2 Easy Examples)


Similar Articles


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.

Insert VBA Command Button to Import CSV File without Opening

  • Secondly, go to Insert > ActiveX Controls > Command Button.

Insert VBA Command Button to Import CSV File without Opening

  • 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

Insert VBA Command Button to Import CSV File without Opening

Read More: How to Convert CSV to XLSX without Opening (5 Easy Methods)

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.

Insert VBA Command Button to Import CSV File without Opening

  • In the end, we get the data of the CSV file imported into an excel file.


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


Related Articles

Mukesh Dipto

Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo