Querying Excel from Word with ADO (Office2k/Win2k)

[doj]

I am working on a development that uses excel to store default data for usre on the userforms

I Hope to use ado to interogate the excel spreadsheets
I use
rsExcel.Open "Select Distinct * from [sheet1$A:A]", cnnExcel, adOpenStatic
which returns the unique values from the first column to populate a combo box
I then want to return data from column B

I can successfully populate a combo box with values but I cant do the next step
Please look @ attached files for details

Thanks for your help

Doj

P.S. Is it a good Idea to open the connection once for the project and check the state each time you use it , or is it better to open and close it each procedure

[alert]

Doj,

The select statement you're having trouble with can be written as follows:

"Select * from [sheet1$B:B] where [sheet1$A:A] = '" & ComboBox1.Value & "'"

With regards to the connection, I'd have a global connection object which I'd create and open when the form loads and then close and destroy when the form unloads, rather than opening and closing a connection each time you use a recordset.

However, if you're using ADO to store default values for your Word userforms, you're better off using Access than Excel for your back-end. It's cleaner to write SQL statements referencing tables and fields than Excel ranges/columns. You can also normalize your data in Access which you can't do in Excel. Another approach could be not to use ADO at all, but simply use .ini files (or registry entries) to store your default form values.

Regards,

Stephan

[doj]

when I append your sting as follows

rsExcel.Open "Select * from [sheet1$B:B] where [sheet1$A:A] = '" & ComboBox1.Value & "'", cnnExcel, adOpenStatic

I get an error
Run-time error -2147217904(80040e10)
no value given for one or more required parameters

Thanks for you input re connection

[HansV]

Try the code below. Key points:
<UL><LI>It uses the Change event of the combo box instead of the DblClick event.
<LI>The text box is cleared at the start, otherwise the code would keep on appending items.
<LI>The recordset consists of column A and B, for we need column A in the WHERE condition.
<LI>It uses the field name in the WHERE condition instead of the column address.
<LI>Since the recordset has two columns, Fields(1) is retrieved.[/list]Private Sub ComboBox1_Change()
Dim cnnExcel As New ADODB.Connection
Dim rsExcel As New ADODB.Recordset
' Clear text box
TextBox1.Text = ""
' Populates the text box with the values from colum B of the excel spread sheet where colum A
' equals the value clicked on
cnnExcel.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Cocuments and SettingsHansMijn documentenLounge.xls;Extended Properties=Excel 8.0;"""
rsExcel.Open "SELECT * FROM [Sheet1$A:B] WHERE Product ='" & ComboBox1 & "'", cnnExcel, adOpenStatic
rsExcel.MoveFirst
Do
TextBox1.Text = TextBox1.Text & rsExcel.Fields(1) & vbCrLf
rsExcel.MoveNext
Loop Until rsExcel.EOF
rsExcel.Close
cnnExcel.Close
End Sub

[doj]

thats great
so the [sheet] needs to refer to all the columns needed

thanks for the help
Re MSaccess
We are stuck with excel as the user administering the template has office standard and the client will not allow any different.
Data is stored in ini files for the global startup template and document variables for the documents.
I have been suggesting that we eliminate this and use one of the miriad of SQL servers to store all data...Should I shut up and leave it as is. I suspect efficencies if SQL is used.
No need to reply just for my info

[HansV]

>> so the [sheet] needs to refer to all the columns needed

Yes, the FROM part of a SQL statement must be a "table" that includes all columns that are referred to - whether to return them or to use them in the WHERE part or ORDER BY part of the statement.

[pieterse]

You are not stuck with XL when you use ADO. ADO can address Access files directly.
I have written an application in Word that stores summary information of documents into a central Access database for a company of 100 employees. Most of them have no Access on their system, just O2K SBE.

In fact, the only user that needs Access would be the one that has to do administrative tasks in the database itself.