Dataset Overview
To illustrate, we will use a sample dataset as an example. The below dataset within a CSV file represents the Salesman, Product, and Sales of a company.
Method 1 – Simple VBA Code
STEPS
- Open a blank Excel workbook.
- Right-click on the Sheet tab (located just above the Excel status bar).
- Select View Code from the options.
- In the VBA window that appears, copy and paste the following code:
Sub ReadLinebyLine()
Dim file As String, line As String
file = "D:\46\vba read csv file line by line\Sample.csv"
Open file For Input As #1
Do Until EOF(1)
Line Input #1, line
ActiveCell = line
ActiveCell.Offset(1, 0).Select
Loop
Close #1
End Sub
- Save the file and close the VBA window.
- Select any cell where you want to paste the data from the CSV (e.g., select cell A2).
- Go to the Developer tab and select Macros.
- Choose the ReadLinebyLine option and click Run.
- The data from the CSV file will be pasted into the Excel sheet.
Note: This method returns the respective lines in a single cell, separated by commas.
Method 2 – Using FileSystemObject (FSO)
In this method, we’ll utilize FileSystemObject (FSO), which isn’t inherent to Excel VBA. The FSO adheres to international standards and serves as an interface to a computer’s file system. In VBA, we can instantiate an object, referred to as late binding, to leverage the FSO.
STEPS
- Right-click on a worksheet and select View Code.
- In the VBA window, copy and paste the following code:
Sub FileSystemObj()
Dim line As String
Dim FSO As Object
Dim TS As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
Set TS = FSO.OpenTextFile("D:\46\vba read csv file line by line\Sample.csv")
Do While Not TS.AtEndOfStream
line = TS.ReadLine
ActiveCell = line
ActiveCell.Offset(1, 0).Select
Loop
TS.Close
Set TS = Nothing
Set FSO = Nothing
End Sub
- Close the VBA window after saving the file.
- Go to the Developer tab and click on Macros.
- Select FileSystemObj in the Macro dialog box.
- Click Run.
- The data from the CSV file will be pasted into Excel.
Method 3 – Read CSV File Line by Line with VBA and Paste in Individual Cells
In the previous methods, we pasted the dataset lines into a single cell, separated by commas. Now, let’s explore how to paste each line’s data into individual cells using VBA. Follow these steps:
STEPS
- Right-click on a sheet and select View Code.
- The VBA window will appear, displaying a dialog box.
- Copy the following code and paste it there:
Sub IndividualCells()
Dim line As String
Dim FSO As Object
Dim TS As Object
Dim LineElements As Variant
Dim Ind As Long
Dim p As Long
Dim Delimiter As String
Set FSO = CreateObject("Scripting.FileSystemObject")
Set TS = FSO.OpenTextFile("D:\46\vba read csv file line by line\Sample.csv")
Delimiter = ","
Ind = 1
Do While TS.AtEndOfStream = False
line = TS.ReadLine
LineElements = Split(line, Delimiter)
For p = LBound(LineElements) To UBound(LineElements)
Cells(Ind, p + 1).Value = LineElements(p)
Next p
Ind = Ind + 1
Loop
TS.Close
Set TS = Nothing
Set FSO = Nothing
End Sub
- Close the VBA window after saving the file.
- Select Macros under the Developer tab.
- Select IndividualCells from the list in the Macro dialog box and click Run.
- The CSV file data will now appear in the Excel worksheet, with each line’s data in separate cells.
Read More: Excel VBA to Read CSV File into Array
Download Practice Workbook
You can download the practice workbook from here:
Related Articles
- Excel VBA to Import CSV File without Opening
- Excel VBA to Convert CSV File to XLSX
- Excel VBA: Merge Multiple CSV Files into One Workbook