Read .TXT into Array in VBA

Hey everyone,

I have a text file that I'm bringing into VBA with the FileSystemObject method.. I want to read the data that is in the .txt file into an array. In the text file there are 5470 lines and 20 columns.. putting a "watch" on my "arr" variable, I basically want the first row to be 20 categories, and then every row thereafter to be sorted into those 20 categories.. This is my code this far.

Please Login or Register  to view this content.

I have also attached an image of my "watch" - Ideally I would like the values of arr(0)(0) through arr (0)(20) to be the expressions themselves, and then every one of the 20 categories in arr(1) through arr(5470) to fall into their respective categories..

Arr.PNG

Try something like this.

Please Login or Register  to view this content.

Hi jindon, thanks for the quick response!

VBE gives me a "Runtime Error '13': Type Mismatch" error at the below area in the code.. not sure why, I am running 2003 if that makes any difference.

Please Login or Register  to view this content.

try

Please Login or Register  to view this content.

@jindon, your code works perfectly for outputting the text file into the workbook.. though is there a way to create this sort of array "in the cloud" so to speak, so that I can create a userform that references the array for specific data? If not I could always just have to reference a sheet in the book.. I'm just afraid of data bottlenecks as the amount of data in the file becomes greater.

@abousetta, when I put a watch on "arrFinal," it more or less mirrors what the watch on "arr" does in my original code.. is there something I'm missing?

Hasn't this really been answered here: http://www.excelforum.com/excel-prog...txt-file.html?

Must admit I'm curious why you don't use a database since you expect this data to grow consistently, as it does, these methods will get slower and slower and you'd be better off with a database/ADO queries from the other thread

Like this?

Please Login or Register  to view this content.

It's mostly because I have very little experience with any type of databasing.. so I'm averse to trying it, if I can avoid it.. but I guess it's inevitable. Would you recommend Access?

Do you have it installed? If so, try it, it's the most user friendly.

If not, then probably sql server express (but install the management studio also - makes things much easier), because it's free and will handle a lot of people connected to it at the same time.

It'll be a learning curve, but well worth it

To interact with excel you've already got 90% of it from Joseph's post on the other thread

How many people do you envisage using your workbook at any one time?

Access is install, I'd likely have to request permission to get SQL Server Express.. Access could probably do the trick, I only expect at most 10 individuals to be accessing the data.

Give access a whirl then It's much more suited and if you all have access installed, then dump Excel altogether

Unfortunately I don't think dropping excel would be an option currently.. This is one part of a larger project. -- In a nutshell, particular bonds need to be called when a user inputs his/her criteria. Depending on what the inputted criteria is, there is also code written to query internal databases for rates and other information. Specific math is then run on the Par value, DV01, PV01, and the retreived rates etc etc to produce the "hedge rate."

The model is, in simple terms, a dummy swap pricer, which allows then end user to figure out whether the swap is too risky, or okay to put on, and this part of it is simply the component to allow the user to pull bond information based on criteria he or she inputs.

Ah right, fair enough.

there is also code written to query internal databases for rates

Can't you also query the same databases to pull bond info rather than trying to use a text file?

The necessary bond information is dumped into the text file every other day. Apparently I don't have the necessary permissions to access the database because it stores trade inventories not only for municipal bonds, but all products.. to be honest seems like a bunch of BS because it's not as if I would ever query for anything else.. my plate is full enough already. But this is why I am trying to do this

Ah right, how many lines are likely to get added to the text file?

Originally Posted by Kyle123

Ah right, how many lines are likely to get added to the text file?

I'd like to say because it is an inventory of municipal bonds, it can technically grow to the number of municipalities in the US (roughly 30k), though I highly doubt that, and a lot of them don't even issue paper.. so I'd say it could grow to 10k, give or take.