Dynamic AI - Microsoft Excel 2007

[Carsten Sørensen]

Connecting Dynamic AI to Microsoft Excel 2007


In order to connect Dynamic AI to an Excel 2007 file, switch to the database tab (at the bottom of the screen) and create a New Database connection. Then select Excel 2007 from the Type list.

Be aware that connecting to Excel Spreadsheets in the format 2007 is different than connecting to Excel 97-2003 (please see separate topic).

Use browse to find and specify the Excel file OR type the full path to the file.

Browse will only be available when you are using Dynamic AI on the same PC as where you have installed Dynamic AI. If you setup connection on a Dynamic AI Server or Enterprise edition - be aware that you should specify the path as seen from the server.



Be aware that Excel is a file-based “database” and that the windows user used by the Dynamic AI installation has sufficient (read/write) access rights to the file.

Also be aware that Dynamic AI will try to open the file exclusively – therefore you will get the following message if the file is already open (note that the message is mentioning Access as the database engine although it is an Excel file):

The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data.

Test the connection by pressing the ‘Test Connection’ button.

Click SAVE to save the connection. Now you should see the new database connection in the database Explore and be able to browse and use defined ranges of data from the file:






ODBC driver

In most cases the necessary driver for connecting to Excel is already present. However if you have problems connecting to an Excel spreadsheet please check the Drivers tab of the ODBC Data Source Administrator found under Administrative Tools in the Control Panel.



Be aware that you will need the Excel driver that supports ...*.xlsx, *.xlsm, *.xlsb in order to connect to Excel in the Office 2007 format.

If there is no ODBC driver installed you can download the MDAC client from:
http://www.microsoft.com/downloads/deta ... laylang=en

This driver contains core Data Access components such as Microsoft SQL Server, OLE DB provider and ODBC driver.

Sample data

The DEMO optionally installed with Dynamic AI includes a sample Microsoft Excel DEMO file (Sample_95.xls - 97-2003 format) connected to Dynamic AI and used for reports.


Would you like a Dynamic AI Server edition trial version please contact us through our website at http://www.dynamicai.com



Copyright © 1998-2009 CINTAC A/S and/or its suppliers. All rights reserved. Dynamic AI ™, Dynamic Financials ® and CINTAC Dynamic are registered trademarks or trademarks of CINTAC A/S.
Microsoft, Windows and Excel are registered trademarks of Microsoft Corporation. All other products may be trademarks or registered trademarks of their respective companies.

[Jacques]

Hi,

I didn't succeeded to open the file the way you wrote.
In fact, in the exel file, we have to give a name to a group of cells by right clicking on the selected cells and give a name.

DAI will take the name of this group as table where we can work on it. The other table names DAI creates automatically (with a $ sign at the end) are the name of the tabs and it's not possible to work with it.

Jacques

[admin]

Hi Jacques,

Just to clarify...

Dynamic AI connects to excel using a microsoft provided odbc driver.

If you have named ranges, they will appear as tables, if you have just sheets (the tables with $ sign at the end) they must conform to some general criteria for the odbc driver to work properly (in fact, so does the named ranges)

You should have column headings in the top row

You should not mix data types
(the odbc driver scans the first few data values to determine the data type) if cells a2..a11 contains dates only, the odbc driver will present this column with a date data type. If you have a string in a12, the odbc driver will fail.)

You can't have any blank columns

Best regards
Bo

[Jacques]

Bo,

I have tried with sheet's name but I didn't succeed. I have done sheets with only 2 lines (title + data's) and no succes.
Each time the table name has a $ sign, it doesn't succeed. I have tried it on old XP computer or new one (Windows 7 with excell 2010 and Microsoft ODBC installed) without any result. Only the named ranges give results.
I have a french version but I don't know if the problem is there.

What I wonder is why, in the demo database SampleXLS created from "@ROOT@dat\Sample_95.xls", the name of the sheet (Sample2003$) doesn't appair in the list of the table. I wanted to try with this file.

Jacques