Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Excel in Office 2007 (Read 12490 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
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
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
Back to top
« Last Edit: 22.08.07 at 15:35:41 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   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
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
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.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
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;
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
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
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1