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

On Sep 19, 5:09 pm, "Ralph" <nt_consultin...@xxxxxxxxx> wrote:
"Ed from AZ" <prof_ofw...@xxxxxxxxx> wrote in messagenews: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

I have written an example that takes a csv file and directly binds it
to a datacombo. You can not use a Combo box to do this kind of
operation without using line by line. but if you use a datacombo you
can do it really easily.

I tried to send an example to ed's email but it came back as a not
delivered.
Send me an email if you are interested ed. I Think I have a good
solution for you.
.