VB6:Wanting to speed up populating six arrays on Form.Load

[EdNerd]

I have a VB6 (Classic VB, not .NET) project. On Form.Load, I need to populate six arrays to feed into about a dozen Combo boxes.

Currently, I have my main list in an Excel sheet. An Excel macro looks at all my info and generates a Word document with six tables. (Word and Excel are 2000.) When my VB form loads, it opens the Word doc and iterates the tables, putting Table1 into Array1, etc. This is slow going!

I used the Word doc with Tables because it was easy to get six different tables of data into one file, and yet separate the info into arrays. The only other way I could think was to have the Excel macro write six different text files, then open those one by one and read into the arrays at Form.Load.

Is there a better, faster, easier way to set up the data from Excel to be read into the arrays?

 

[RoryA]

Is there a reason you cannot simply load the data from the ranges in Excel into the arrays either by automating Excel or querying it using ADO?

 

[EdNerd]

I keep the list in Excel because it's easier than trying to keep the list in a Word table. I've got five columns: ID (either "F" or "S"), Title, hyperlink, First, Second. When I write the six tables, they are separated out as hyperlink text and either Title, First, or Second, with one set of three for ID="F" and another three for ID="S" - six tables total. Plus I can sort the Excel sheet to alphabetical order before writing out the tables.

Editing the spreadsheet is not done while the VB program is opened. The VB program is not used in conjunction with the Excel file. I'm not calling the VB form from the Excel file. Since I have to open some kind of data file when I launch the VB program to populate the arrays, it just seemed easier to load up the arrays with a simple "For x = 1 To 6, Load array(1) with the contents of Table(1)" from Word than to iterate down Excel columns. Everything is text-only in the Word doc; in Excel, I'd need to grab just the link text and then either Col 2, 4, or 5, making sure I use the ID in Col 1 to decide which array set.