Open & read it line by line

Agnieszka

Hi!

Today I would like to present you other way to import file into Excel, using an option of reading whole contents of data inside the file or just the part of it under several conditions.

Let’s open our Excel Workkbok and save it first as .xlsm (or whatever you want actually).

Now, go to VisualBasic botton and insert New Module. I will use file with .csv (comma delimited) extension. If you have no sample, use this one below (you can copy it into notepad or import into excel and save as .csv):

Name;Surname;Age;Position;PassportNumber
Louis;Dannoval;34;CEO;12345
Mary;Jain;56;Secretary;45678
Victoria;Lilith;44;Board Member;78901
Liam;Vint;36;Sales Manager;98765

Dim Opt As VbMsgBoxResult 'optional answer
Dim Path As String 'full path where file is located
Dim FileLine As String 'lines inside 
Dim i As Integer 'number of each row in Active Worksheet

Path = "C:\Users\Agnieszka\Desktop\CSVFile.csv"
i = 0

There is something else we need to define in our procedure: a one-dimesional table:

Dim Words() As String 'noticed that there is no number or variable inside

ok, time to open our file, based on declared path

 Open Path For Input As #1


Do Until EOF(1)
Line Input #1, FileLine
Words = Split(FileLine, ";")
ActiveCell.Offset(i, 0).Value = Words(0)
ActiveCell.Offset(i, 1).Value = Words(1)
ActiveCell.Offset(i, 2).Value = Words(2)
ActiveCell.Offset(i, 3).Value = Words(3)
ActiveCell.Offset(i, 4).Value = Words(4)
i = i + 1
Loop

At the end we need to close what has been opened at the begining:

Close #1

Opt = MsgBox("File succesfully read and imported into Excel Worksheet, -
would you like to clear all now and repeat process??", vbYesNoCancel, -
 "Clearing Data")
If Opt = vbYes Then
Cells.Select
Selection.Clear
Range("A1").Select
ElseIf Opt = vbNo Then
MsgBox "Save data before you will quit application."
Else: Exit Sub
End If

Rows(1).Select
Selection.Font.Bold = True
Cells.EntireColumn.AutoFit
Range("A1").Select

If we don’t want to import all data from the file we can modify our macro a little by adding this condition :Or FileLine = 3 into our Do Until Loop:

Do Until EOF(1) Or FileLine = 3

and FileLine = FileLine + 1 before Loop

Voilà!