Re: Fastest way to read data to populate combo controls?


"Ed from AZ" <prof_ofwhat@xxxxxxxxx> wrote in message
news:b22820ff-d078-4946-b9e6-49754e4bf75a@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
If it was me, and the Excel File is of moderate size, I would consider
dumping the whole thing into a CSV and parse it using VB. (Not necessarily
the best idea, but I'm simply more confortable with VB.)

When you talk about using a CSV, Ralph, I have no clue how VB would
mass-inport such a file. My idea of importing a CSV into VB would be
to read the file line by line; each line is a string; parse the string
against the commas with SEARCH or FIND and MID. Seems rather
neanderthal - unless this really is the method!! If so, is it really
that efficient?

My Excel data is simply like this:
ColA ColB ColC ColD ColE
F title path comment1 comment2
S title path comment1 comment2
etc ("F" and "S" do not necessarily alternate)

The Excel macro creates a Word document with six tables:

F1:
title path

F2:
com1 path

F3:
com2 path

and three similar for the S files.

The VB app opens the Word doc, iterates the tables and creates six
arrays, then writes these arrays into combos. It works fine. But I
know I tend towards klunky hammer-and-chisel code, and thought I'd
find out if there was a better way to do this.

Ed

=============================================

Probably time to update your skill-set. 'That Guy' in another post has a
good suggestion - use ADO to treat your Excel Spread*** as a database.
This will give you flexibility for this project and for others that are sure
to come along.

Checkout the following articles...
"How To Use ADO with Excel Data from Visual Basic or VBA"
http://support.microsoft.com/kb/257819
(which contains a link to the following...)
"ExcelADO demonstrates how to use ADO to read and write data in Excel
workbooks"
http://support.microsoft.com/kb/278973/EN-US/

From your project open up the Excel spread***.
You can now 'mine' the *** for any data and arrangement of the data you
need.

eg, to create F1 do a simple query ... [Warning Air Code!]

Dim F1()
rs.Open "Select ColB, ColC From [***$] where ColA = 'F'", cnn,
adOpenStatic
F1 = rs.GetRows()
....
For Each n In F1
If bPath Then
sPath = n : bPath = False
Else
sTitle = n : bPath = True
End If
Next

I'm just showing a string variable, but you could sub any other store such
as a combo list value.

You could also use 'Data Binding' to directly attach the rs to the control.
This should get you started.

hth
-ralph


.