2D array from text file in excel VBA

Question

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.

the "..." represents numerous other lines in the file, I'm not sure what you mean by the data in the second dimension but following my example, if I used myArray(1,3) I want to be able to retrieve the string "asdf5" — BIGGEST
– BIGGEST, Commented Dec 10, 2016 at 1:06
Could you post what you have so far, saves reinventing the wheel. — Captain Grumpy
– Captain Grumpy, Commented Dec 10, 2016 at 1:22

cyboashu · Accepted Answer · 2016-12-10 01:37:04Z

3

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

answered Dec 10, 2016 at 1:37

cyboashu

10.5k2 gold badges32 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

BIGGEST Over a year ago

Thank you! this works great, is there a way to disable the workbook that pops up for a split second from appearing?

cyboashu Over a year ago

Look into application.screenupdating

user6432984 Over a year ago

Application.DisplayAlerts = False

user6432984 Over a year ago

Don't use parentheses unless you are expecting a return value. wbtemp.Close False

Collectives™ on Stack Overflow

2D array from text file in excel VBA

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related