Datatools & Excel

[ben.bringardner]

I am working to link an excel file with properties to a model via datatools following this ( http://revittoday.blogspot.com/2009/03/how-to-link-excel-using-datatools.html ) tutorial. But am not finding the correct driver in the datatools dialog box. I am looking for a "Driver Microsoft Excel"... instead I see "Excel." I complete the remainder of the tutorial and the "excel link" tab has shown up in the properties dialog, but it is not populated. (It surely couldn't be because the excel file was never really pathed to the model).

Does anyone have any insight on how to get an excel file linked with a model?

As a side note I am running windows 7 (64 bit) with microsoft office 2007 (I presume 32 bit). Is this this issue, or is there a driver somewhere that I can download?

Thanks!
Ben Bringardner

[amixam]

Hi Ben,

Unfortuantely, you need to have a 64 bit version of Excel running to use data tools with NW 64 bit. This is the limitation of the ODBC drivers that ship with MS Office. Microsoft in all of their wisdom, has 2 versions of odbcad32. A 32 and a 64 bit version. To top this off, they are both named odbcad32.exe. It gets better. They put the 32 bit odbcad32.exe in the syswow64 directory. They put the 64 bit odbcad32.exe in the system32 directory. 32 bit apps will pick up the 32 bit registry setting and 64 bit will pick up the 64 bit registry setting. system32 comes before syswow64 in the system path so the 64bit software runs before the 32 bit software.

Office 2010 has both 32 and 64 bit versions. I am running Office 2010 64 bit and the Data Tool connections to MS Access and Excel are working fine with NW 2011 64 bit.

Hope this helps.

Cheers

[ben.bringardner]

Very helpful. You are impressively knowledgable about the subject. This is an annoying technicality, since this is a work computer and 2007 is the version of Office we are currently supporting.

Do you happen to know if there is anyway to link a MYSQL database to NW2011? I see support for some other SQL options.


Ben

[JeffHUK]

Hi Ben,

You would probably want something like the following: http://dev.mysql.com/downloads/connector/odbc/

DataTools uses ODBC drivers to connect, and so provided you have an ODBC driver for MySQL, it should connect, though it is not a tested scenario.

On the subject of Excel, please be aware that DataTools has been shown to be relatively slow with Excel specifically. Whilst it is something we intend to look into, preliminary results suggest it's just that the Excel ODBC driver is slow to run SQL queries.

Hope that helps,

Jeff

[ben.bringardner]

Very helpful indeed! I am going to test this out right away.

I personally would rather work from a mysql database anyway. Excel doesn't have to be used for everything, plus if a mysql database can be plugged into NW, the DB could be populated / modified by end users via a web-based interface.


Ben