Linker IT Software - Excel in Office 2007


Welcome, Guest. Please Login or Register.

ExcelLock: Locking and securing your valuable Excel spreadsheets

encOffice: Protect your Excel file easy and safe

Linker IT Software › SQL*XL › Database Connections

‹ Previous topic | Next topic ›

Pages: 1

Excel in Office 2007 (Read 4399 times)

jabot

YaBB Newbies

Posts: 2

Excel in Office 2007
22.08.07 at 14:15:47

We have convinced users to use EXCEL as their data reporting format. We are now using Office 2007 and I followed the suggestion you made on the Forum for connecting to an EXCEL sheet without success.

I would like to press that we use SQL*XL as our solution to the needs we have, but I can't unless I can make a connection work. In the example your from statement is {shett1$] should this be replaced with the actual sheet name (for example, ElementaryScience2007 -- the sheet I am looking at)?

Thank you in advance for your help on this. I was very happy with the product when I was using it and I believe it would be a perfect tool for our new need.

Mike Jabot

IP Logged

Gerrit-Jan Linker

YaBB Administrator

Posts: 1679

Re: Excel in Office 2007
Reply #1 - 22.08.07 at 15:28:31

I have tried it using Excel 2003. I have typed exactly the steps I took to establish the connection and to query the workbook.

Connecting to a workbook (.xls)

Connect
New Connection
Database Connection Wizard
Select Database Type: Excel
Press Next
Enter database file (in my case c:\code\test.xls)
Press Next
Selected the OLEDB driver (Provider Microsoft Jet OLEDB.4.0)
  (I could also have selected the ODBC driver (Provider MSDASQL))
I return to the Database Connection dialog
The new connection is selected
I just press OK to connect to the database
(leave the password field blank and leave the user as Admin)

Querying the workbook:

Open the SQL dialog
Show Editor...
Expand the Schemas node
Expand the Current Schema node
Expand the tables node
I now see 3 "tables": Blad1$, Blad2$ and Blad3$
  Note that my Excel version is in Dutch. In English it would probably be:
  Sheet1$, Sheet2$ and Sheet3$
I select "table" Blad1$
Expand Blad1$
Expand Columns node
Now I see that there are 3 columns I can query.
  I entered a small table in the sheet. I entered aa, bb and cc in
  cells A1, A2 and A3 respectively. Below it I entered a few rows with
  values.
I enter the following SQL in the editor:
  select * from [Blad1$]
Press: Return SQL to SQL*XL
Returning to the SQL dialog I press OK to run the query
The data returns to the sheet.

I immediately open the SQL dialog again.
I change the table name to Blad2$ and run the query
The data is also returned to the worksheet.

It seems to work just fine for me. Perhaps you can repeat my steps and see where it fails for you. I have attached the workbook I tested it with to this post so you can try it exactly as I have tried it above.

Let me know if you need further assistance.

Reference:
Connect to Excel XLS files
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1163266753

connecto_to_excel.zip

Gerrit-Jan Linker
Linker IT Software

IP Logged

jabot

YaBB Newbies

Posts: 2

Re: Excel in Office 2007
Reply #2 - 22.08.07 at 23:59:01

I tried your suggestion without success. I have comment in CAPS below where I ran into problems.

Connect
New Connection
Database Connection Wizard
Select Database Type: Excel

THIS OPTION TO SELECT EXCEL DOES NOT APPEAR TO BE AVAILABLE IN OFFICE 2007

Press Next
Enter database file (in my case c:\code\test.xls)
Press Next
Selected the OLEDB driver (Provider Microsoft Jet OLEDB.4.0)
(I could also have selected the ODBC driver (Provider MSDASQL))

I USED THE ODBC DRIVER TO ESTABLISH A CONNECTION

I return to the Database Connection dialog
The new connection is selected
I just press OK to connect to the database
(leave the password field blank and leave the user as Admin)

Querying the workbook:

Open the SQL dialog
Show Editor...
Expand the Schemas node
Expand the Current Schema node
Expand the tables node
I now see 3 "tables": Blad1$, Blad2$ and Blad3$
Note that my Excel version is in Dutch. In English it would probably be:
Sheet1$, Sheet2$ and Sheet3$

WHEN I CONNECT TO YOUR SAMPLE THE TABLES DO NOT APPEAR

I select "table" Blad1$
Expand Blad1$
Expand Columns node
Now I see that there are 3 columns I can query.

Thank you again for your help on this

Mike Jabot

IP Logged

Gerrit-Jan Linker

YaBB Administrator

Posts: 1679

Re: Excel in Office 2007
Reply #3 - 23.08.07 at 08:44:11

Hi Mike,

I must conclude that you use an old version of SQL*XL that perhaps does not have these features implemented. Do you get the SQL*XL ribbon interface in Office 2007? I integrated the SQL*XL toolbar into the new Excel 2007 ribbon. If you run an older version it will be shown under a final section in the ribbon called addins or something.
Can you send the SQL*XL version number you use? You find it in the SQL*XL About dialog. Perhaps we need to upgrade you to the latest release to see what happens.

I have commented on your remarks below:

Quote:

Select Database Type: Excel

THIS OPTION TO SELECT EXCEL DOES NOT APPEAR TO BE AVAILABLE IN OFFICE 2007

This feature is not tied to Office 2007. The Connection Wizard always has the option to connect to Excel databases (Excel xls files).

Quote:

I USED THE ODBC DRIVER TO ESTABLISH A CONNECTION

ODBC may behave slightly differently. I will have a look at this later today.

Gerrit-Jan Linker
Linker IT Software

IP Logged

Gerrit-Jan Linker

YaBB Administrator

Posts: 1679

Re: Excel in Office 2007
Reply #4 - 23.08.07 at 09:50:22

To connect to an Excel file using ODBC you do the following:

Connect
New Connection
Connection Wizard
Database type: Excel
Enter the path to the .xls file
Select the MSDASQL connection (ODBC)
Note that the connection does not have a user name and password.
Just select the connection and press ok to connect
Open the SQL dialog

Note that the table names do not show in the SQL editor if you would open it. This is likely due to the ODBC driver not providing the table information.

It is a little bit guess work now what the tables are called. The tables are the sheets or named ranges. I tried these:
select * from sheet1; -- failed
select * from sheet1$; -- failed
select * from [sheet1$]; -- ok
select * from [sheet3$]; --ok ; note I started the data on the sheet in D10 instead of A1
select * from [mytab]; --failed
select * from mytab; -- failed
select * from [mytab$]; --ok
select * from mynamedrange; --ok
select * from [mynamedrange]; --ok
select * from [mynamedrange$]; -- failed

Conclusions:
-You can connect fine with ODBC to Excel files however OLE DB connections work better as the table names are shown
-You need to append a dollar sign to the name of the tab and wrap the whole in square brackets []: select * from [mytab$];
-You can query named ranges too. You can just refer to the name of the range or put it in square brackets []: select * from my_named_range;

Gerrit-Jan Linker
Linker IT Software

IP Logged

Gerrit-Jan Linker YaBB Administrator Posts: 1679	Re: Excel in Office 2007 Reply #5 - 23.08.07 at 21:54:08 After upgrading to version 4.2 I received this feedback: Quote: WOW!!!!! This is completely different. Took almost nanoseconds to connect to the EXCEL workbook (I had memorized your directions after trying probably 25 combinations yesterday!) I conclude there are no further problems connecting to Excel...
Back to top	Gerrit-Jan Linker Linker IT Software IP Logged

Pages: 1

Linker IT Software › SQL*XL › Database Connections

‹ Previous topic | Next topic ›

Copyright (C) 1995-2007 Linker IT Software BV. All Rights Reserved. Oracle is a registered trademark of Oracle corporation. Excel and Office are registered trademarks of Microsoft corporation. Other names appearing on the site may be trademarks of their respective owners. Software, files, documents, articles and other material are provided "as is" and without warranties as to performance or mechantability or any other warranties whether expressed or implied. No warranty of fitness for a particular purpose is offered.
sitemap