How to Import CSV file to Excel using Macro VBA

Introduction

In the forums many users asking about import CSV file into excel sheet using macro VBA. The Macro is powerful and inbuilt VBA mechanism to support Programming in Excel. So I decide to publish an article how to write macro and then import CSV content into excel file.

Prerequisites

1. MS Office Excel 2003 or 2007
2. VB 6.0 Programming knowledge

Design

For this demonstration, I have created a simple CSV file with 5 rows and comma separated few columns as like below,

The source CSV file has five rows and 6 comma separated columns and first rows is header of the source file. Let’s start to design excel and write code to import this source.csv file data into excel work book.

Note:I have attached source code and samples end of this article. you can download it.

Implementation

As usual open excel sheet and add a rounded shape. In order to add a shape into sheet

1.    Click Insert menu
2.    Click Shapes icon in tool bar
3.    Click Oval (marked by blue circle in blow image) and place in excel sheet

Once you added and edit text “Import” in oval. The finally designed will be like below,

The designed is ready; let’s assign macro to this button to perform button click and then CSV data in to this sheet. In order to assign macro to this button right click on the circle and select “Assign Macro..”.

Once select “Assign Macro” you will see window as like below to assign macro for this oval.

Here I’m going to write a new macro for this button, so click “New” button and excel will open new code window for write macro.

Now code windows ready to input code within button click.
Note: On this demonstration I’m going to import data from CSV when users click Import button.

VBA Code

Sub Oval1_Click()
      Dim ResultStr As String
      Dim filename As String
      Dim FileNum As Integer
      Dim Counter As Double
      'Ask User for File's Name
      filename = InputBox("Please enter the csv File name")
      'Check for no entry
      If filename = "" Then End
      'Get Next Available File Handle Number
      FileNum = FreeFile()
      'Open Text File For Input
      Open filename For Input As #FileNum
      'Turn Screen Updating Off
      Application.ScreenUpdating = False
      'Set The Counter to 1
      Counter = 1
          
      'Loop Until the End Of File Is Reached
      Do While Seek(FileNum) <= LOF(FileNum)
         'Display Importing Row Number On Status Bar
          Application.StatusBar = "Importing Row " & _
             Counter & " of text file " & filename
          'Store One Line Of Text From File To Variable
          Line Input #FileNum, ResultStr
                  
          'Store Variable Data Into Active Cell
          Dim splitValues As Variant
          splitValues = Split(ResultStr, ",")
          Cells(Counter + 5, 1) = Replace(splitValues(0), Chr(34), "")
          Cells(Counter + 5, 2) = Replace(splitValues(1), Chr(34), "")
          Cells(Counter + 5, 3) = Replace(splitValues(2), Chr(34), "")
          Cells(Counter + 5, 4) = Replace(splitValues(3), Chr(34), "")
          Cells(Counter + 5, 5) = Replace(splitValues(4), Chr(34), "")
          Cells(Counter + 5, 6) = Replace(splitValues(5), Chr(34), "")
          Counter = Counter + 1
      'Start Again At Top Of 'Do While' Statement
      Loop
      'Close The Open Text File
      Close
      'Remove Message From Status Bar
      Application.StatusBar = False
      MsgBox ("Records successfully imported")
End Sub

Code Explanation

1.    First four lines declare necessary variables for use in later part of the coding.
2.    The InputBox is allow to  users to given source file path
3.    In the next line, checking If file name is empty application will be end
4.    And then get the file Handle in order to open  file and process line by line
5.    Application.ScreenUpdating = False is help to updating screen set is disable
6.    In the next line create a counter to use in later part of code to find row in destination excel sheet and set values in cells based on source file row.
7.    And then read each line using Do while loop and  then spilt read line using comma  and stored in Array
8.    As we known the source file structure read from 1 to 6 elements from the array and assign to excels sheet from 6th row.

a.    When you read values from the CSV file it has extra single quotation, so replaced by empty char using following code.

Replace(splitValues(0), Chr(34), "")

9. Once finished reading and assigning excel sheet close the source file and reset Application status bar as well.

Now macro also ready, in order to run the macro in excel sheet you have to save this excel work book as like macro enabled as shown below,

Once you select this option, you will see Save As window as like below and give name and save it.

Once you save the file go the saved location and double click to open macro enabled file and click Oval specify the source file path in input dialog box as like below,

And click ok to import data from CSV to sheet. The final result will look like below,

Output

Note: I have attached source CSV file and macro enabled Excel sheet end of this article. You can download it and try to add more.
Even you can save this as template with formatting and click import button. Once data is imported also formatting will be remaining. See below example

Download Sample Project

Download source files -14 kb

Conclusion

Through this article you have learned how to import CSV file into Excel sheet using macro VBA. Hopes help and stay tuned with codegain for more articles on this section. Thank you for reading.