Navisworks Manage 2012 and Data Link to Excel - so close, yet so far

[Anonymous]

I'm trying to do a proof of concept for a customer. Most likely they'll be using MS Access to do this, but just as an example, I'm trying to establish a database link between Microsoft Excel and Navisworks Manage 2012. I'm REALLY close. Interesting side note - why are there TWO places to get to the data tools? - One in File Options and one from the Data Tools button on the Home ribbon? They don't seem to "talk" to each other - the resulting dialogs look the same but what is done in one is not reflected in the other.

At any rate, I am doing all of this in the File Options tool - under the Data Tools tab, I've configured a "Test" data link to use the Microsoft Excel Driver (*.xls, *.xlsx).

My search string reads: SELECT * FROM [Sheet1$] WHERE "Object ID" = %prop ("Element" , "Object ID");

The model is a really simple Revit Architecture model that I've brought in, with a custom parameter of "Object ID" - each object in the model has a unique id number. This shows up on the "Element" tab of the Navisworks Properties window when I select one of them. However with the link enabled, selecting an object does not display the "Test" tab as it should.

If I modify the search string to just say: SELECT * FROM [Sheet1$], then I see all of the fields in the "Test" tab. I've double and triple checked the spelling, etc. and I've played with all kinds of permutations of the search string from the WHERE on, with no results. I even made sure that the "Object ID" column in the Excel file was formatted as Text (the first time through it was formatted as "General" and the values were coming back as decimal numbers which wouldn't have matched my Object ID values in the model.

Can anybody see anything wrong with my search string? This shouldn't be rocket science, and it doesn't look to be, but it also doesn't seem to WORK! Thanks in advance.

Excel file is attached (change the extension from .txt to .xlxs).

[Anonymous]

I'm trying to do a proof of concept for a customer. Most likely they'll be using MS Access to do this, but just as an example, I'm trying to establish a database link between Microsoft Excel and Navisworks Manage 2012. I'm REALLY close. Interesting side note - why are there TWO places to get to the data tools? - One in File Options and one from the Data Tools button on the Home ribbon? They don't seem to "talk" to each other - the resulting dialogs look the same but what is done in one is not reflected in the other.

At any rate, I am doing all of this in the File Options tool - under the Data Tools tab, I've configured a "Test" data link to use the Microsoft Excel Driver (*.xls, *.xlsx).

My search string reads: SELECT * FROM [Sheet1$] WHERE "Object ID" = %prop ("Element" , "Object ID");

The model is a really simple Revit Architecture model that I've brought in, with a custom parameter of "Object ID" - each object in the model has a unique id number. This shows up on the "Element" tab of the Navisworks Properties window when I select one of them. However with the link enabled, selecting an object does not display the "Test" tab as it should.

If I modify the search string to just say: SELECT * FROM [Sheet1$], then I see all of the fields in the "Test" tab. I've double and triple checked the spelling, etc. and I've played with all kinds of permutations of the search string from the WHERE on, with no results. I even made sure that the "Object ID" column in the Excel file was formatted as Text (the first time through it was formatted as "General" and the values were coming back as decimal numbers which wouldn't have matched my Object ID values in the model.

Can anybody see anything wrong with my search string? This shouldn't be rocket science, and it doesn't look to be, but it also doesn't seem to WORK! Thanks in advance.

Excel file is attached (change the extension from .txt to .xlxs).

[dembkod]

MattD,

I took a look at your file. Do you have a Revit file that matches?

Looking at your file, your Object ID appears to be sequential numbers. 123, 234, 345, etc. Do these match Revit Object Ids?

Did you install the proper drivers? You will need to have office 64 bit installed to install this driver.

http://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displa...

Thank you for participating in our community!

David Dembkoski       

Product Support Specialist

Autodesk Product Support

[dembkod]

MattD,

I took a look at your file. Do you have a Revit file that matches?

Looking at your file, your Object ID appears to be sequential numbers. 123, 234, 345, etc. Do these match Revit Object Ids?

Did you install the proper drivers? You will need to have office 64 bit installed to install this driver.

http://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displa...

Thank you for participating in our community!

David Dembkoski       

Product Support Specialist

Autodesk Product Support

[Anonymous]

David,

Yes. This is a really simple model. There are four walls and a door, and I simply gave them object ID's of 123, 234,345,456,789 - because I have no imagination :-).

I've attached a Navisworks NWD file for your review.

I'm painfully aware of the 64 bit issues. Rather than go through the hassle of uninstalling MS Office, installing the 64 bit drivers, then reinstalling MS Office on my PC, I borrowed a 32 bit machine and am testing this with a trial version of Navisworks on that machine. The driver seems to be working - since I can get the Properties window in Navisworks to show all of the rows in the Excel spreadsheet by simply taking out the WHERE statement in the search string.

Thanks for the response.

Matt

[Anonymous]

David,

Yes. This is a really simple model. There are four walls and a door, and I simply gave them object ID's of 123, 234,345,456,789 - because I have no imagination :-).

I've attached a Navisworks NWD file for your review.

I'm painfully aware of the 64 bit issues. Rather than go through the hassle of uninstalling MS Office, installing the 64 bit drivers, then reinstalling MS Office on my PC, I borrowed a 32 bit machine and am testing this with a trial version of Navisworks on that machine. The driver seems to be working - since I can get the Properties window in Navisworks to show all of the rows in the Excel spreadsheet by simply taking out the WHERE statement in the search string.

Thanks for the response.

Matt

[dembkod · Accepted solution]

MattD,

Hello. Datatools is a little confusing. There is commonly an issue with matching data types. I opened your file in excel and changed the Object ID column to be Text. In Excel, select the whole column and right-click, and change the format to text.

If we cut down your command, we can see all the data pull in.

Start with this:

SELECT * FROM [Sheet1$]

You should get a new data tab named whatever your link was named and all the data should populate. Naturally, we want to filter this down.  So we need to work on the WHERE statement.

I took your statement and removed some spaces after the “=” sign:

SELECT * FROM [Sheet1$] WHERE "Object ID" =%prop("Element","Object ID");

At first, this did not seem to work for me, but then I closed Navisworks and reopened and it did.

Can you give this go?

Thank you for participating in our community!

David Dembkoski       

Product Support Specialist

Autodesk Product Support

[dembkod · Accepted solution]

MattD,

Hello. Datatools is a little confusing. There is commonly an issue with matching data types. I opened your file in excel and changed the Object ID column to be Text. In Excel, select the whole column and right-click, and change the format to text.

If we cut down your command, we can see all the data pull in.

Start with this:

SELECT * FROM [Sheet1$]

You should get a new data tab named whatever your link was named and all the data should populate. Naturally, we want to filter this down.  So we need to work on the WHERE statement.

I took your statement and removed some spaces after the “=” sign:

SELECT * FROM [Sheet1$] WHERE "Object ID" =%prop("Element","Object ID");

At first, this did not seem to work for me, but then I closed Navisworks and reopened and it did.

Can you give this go?

Thank you for participating in our community!

David Dembkoski       

Product Support Specialist

Autodesk Product Support

[Anonymous · Accepted solution]

BINGO! That did it. I hadn't thought to remove ALL the spaces - or maybe I did, but combined it with some other edit that broke it a different way. Regardless - this fixes it. Many thanks!

[Anonymous · Accepted solution]

BINGO! That did it. I hadn't thought to remove ALL the spaces - or maybe I did, but combined it with some other edit that broke it a different way. Regardless - this fixes it. Many thanks!

[Anonymous]

MattD,

Any chance you will share your code?

I am desperate to get the ID names and locations out of a Naviswroks file and I am not up to speed with Navisworks. Quite simply, I just want to export all the data to an excel sheet.

 Any help by anyone on this would be appreciated.

Brett

[Anonymous]

MattD,

Any chance you will share your code?

I am desperate to get the ID names and locations out of a Naviswroks file and I am not up to speed with Navisworks. Quite simply, I just want to export all the data to an excel sheet.

 Any help by anyone on this would be appreciated.

Brett