CODEWITHSUNDEEP
arraysexcelvbamultidimensional-array
BIGGEST
BIGGEST

Reputation: 209

2D array from text file in excel VBA

I am opening a text file which contains text like this

asdf,zxcv,asdwqe,asdh,we5,dvsew,safhc
asdf8,asdf3,asdf4,asdf5,asdf6,asdf7,asdf2
....

I am trying to put this all into a 2d array, the only way I have been able to do anything with this so far is to read the file line by line and split that line with a comma into a 1D array and use the information that way. But I have no idea how I'm supposed to do this with a 2D array, I have googled and have found nothing that is helpful. is there a way I can put my 1d array into a 2d array or something?

Also I do not know how many strings there are per line(but all lines will have the same amount) nor how many lines there are in the file.

EDIT: To clarify, how I want it to work is for example if I do MsgBox myArray(1,3) I want "asdf5" to be displayed in that message box.

Upvotes: 0

Views: 2180

Answers (1)

cyboashu
cyboashu

Reputation: 10433

This should work : 

Sub arrayTest()

    Dim arrData
    Dim wbtemp      As Workbook


    '/ 2 = Comma (format parameter)

    Set wbtemp = Workbooks.Open("C:\temp\test.txt", False, True, 2)

    '/ Read in Array. range array is always 2D
    arrData = wbtemp.Worksheets(1).UsedRange
    wbtemp.Close (0)

    '/ Range array will always start from 1. No 0 base,
    '/ but given how less code one needs to write, its a fair trade off.

    MsgBox arrData(2, 4)

End Sub

Upvotes: 3

Related Questions