Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Jul 2002
    Location
    Wellington, New Zealand
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Querying Excel from Word with ADO (Office2k/Win2k)

    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

  2. #2
    New Lounger
    Join Date
    Aug 2002
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Querying Excel from Word with ADO (Office2k/Win2k)

    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

  3. #3
    Lounger
    Join Date
    Jul 2002
    Location
    Wellington, New Zealand
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Querying Excel from Word with ADO (Office2k/Win2k)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Querying Excel from Word with ADO (Office2k/Win2k)

    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

  5. #5
    Lounger
    Join Date
    Jul 2002
    Location
    Wellington, New Zealand
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Querying Excel from Word with ADO (Office2k/Win2k)

    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Querying Excel from Word with ADO (Office2k/Win2k)

    >> 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.

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Querying Excel from Word with ADO (Office2k/Win2k)

    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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •