Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Baltimore, MD, Maryland, USA
    Thanked 0 Times in 0 Posts

    Making an XL table a database that can be queried (XL97/SR2)

    I use MSQuery to query databases in Access, Sybase, and MS SQL Server with no problems. But everytime I try to set up to query a XL Workbook from another XL Workbook, I get hung up on how to make my XL table into a database that I can connect to. Is there something special you have to do to convert an XL Table into a database?

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Redcliff, Alberta, Canada
    Thanked 5 Times in 5 Posts

    Re: Making an XL table a database that can be queried (XL97/SR2)

    I myself have always used Pivot tables when querying between Excel workbooks. Its just the way things have worked out.
    what you need to do is
    1) Set up the Excel file as a Database.
    You probably know this - but in case others are reading this post. An Excel DB has 1 record per row. The first (header) row should have some formatting to help Excel recognize it as a header row. The records should be unformatted. And you should select the whole database (incl header row) and give it the range name Database. Now Excel can't possibly get confused about your intentions.
    2) Define the Excel file as an ODBC DataSource through control panel.

    That should let MSQuery see your DB.

    (note: I just tried all of this and MSQuery croaked - on the other hand my installation is getting flaky - so who knows <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>)
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Posting Permissions

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