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à!

Well, Autumn is getting deeper, nights are longer, days – shorter. Try to run away from depression, if you’re not resistant. I love October, cause this month might give us lot of beautiful, colorful views and landscapes. But this year I decided to do something else for myself and… I chose postgraduate studies 🙂 Also end of October and 1st of November are this specific time, when we can slow down a little, think about our relatives, close friends and people, that are gone. Not only cause they are already “on the other side”, but also if somehow they’re not a part of our life anymore.

Today I will present not much new in VBA, besides adding background into Excel Worksheet and saving it as Web Side. And please… don’t waste your time on thinking about this post too much. Just copy prepared code from this note:

Halloween_Quote2019_VBAcode

and find out if result is similar to below screenshot.