Reading CSV into variables

  • Thread starter Thread starter farazh
  • Start date Start date
F

farazh

Hello

I am trying to get data from a csv file and store it directly int
variables rather than first importing the csv file into excel.

for example consider that my CSV file has 2 rows:

"Input 1", "Input 2"
"Input 3", "Input 4"

So far I have come up with the following code:

Sub test()

Const ForReading = 1, ForWriting = 2, ForAppending = 8

Set MyFSO = CreateObject("Scripting.FileSystemObject")

Set TextFile = MyFSO.OpenTextFile(ThisWorkbook.Path + "\MyFile.csv"
ForReading)

MyLine = TextFile.readline

TextFile.Close

End Sub

Using this code the variable MyLine returns the first line of my fil
i.e. "Input 1, Input 2"

But what I would like to do is have 4 different variables i.e
Var_1 = "Input 1"
Var_2 = "Input 2"
Var_3 = "Input 3"
Var_4 = "Input 4"

Thanks for any help that you can provid
 
farazh

How about putting all of them in an array? Try this

Sub ReadCSV()

Dim Fname As String
Dim Fnum As Long
Dim sInputs As String
Dim vInputs As Variant
Dim vInput1() As Variant
Dim lVarCnt As Long
Dim i As Long

Fnum = FreeFile
Fname = "C:\Dick\Ng\06June\Test.csv"

Open Fname For Input As Fnum

Do While Not EOF(Fnum)
Line Input #Fnum, sInputs

vInputs = Split(sInputs, ",")

For i = LBound(vInputs) To UBound(vInputs)
lVarCnt = lVarCnt + 1
ReDim Preserve vInput1(1 To lVarCnt)
vInput1(lVarCnt) = Trim(vInputs(i))
Next i
Loop

Close Fnum

For i = LBound(vInput1) To UBound(vInput1)
Debug.Print i, vInput1(i)
Next i

End Sub
 
Back
Top