Dynamo Overwriting Excel Macros and Forumlas


#1

Hi:
In the morning I open Revit then Dynamo, this algorithm writes Room data to Excel, works great, up to a point. When Dynamo opens Excel the macros that autosort the VBA macros are gone, so are the formulas that live in cells that further process the data, the pie chart, based on the processed data is also gone. It is like Dynamo is writing a new sheet to the workbook each time I open Dynamo. Only data that Dynamo writes persists from day to day :frowning:


#2

@dfglazer
Just a query, can you not have a separate excel for you formulas etc. and the raw data from revit via dynamo as a separate excel spreadsheet? So what are you currently doing? are you recreating the vba etc? can you elaborate more?


#3

As it stands the macro is associated with the sheet the raw data is in and the formulas are as well. This method makes the most sense at this time. I am hoping that there is a way to maintain the method and that perhaps there is a Dynamo setting in which only the data is updated instead of the sheet being overridden each time as it appears to be doing. I could possibly handle it by making another sheet or workbook and reading from that. That would be an Excel method which I know how to implement. Just hoping to avoid making several layers of Excel since several colleagues will be using the Dynamo method and I would prefer to keep it simple.


#4

@dfglazer
thanks for that. how about in your excel try to shift the column where dynamo will write the data, in your example you have it in row 1 column 0. how about write it on say any other part in the sheet as a test somewhere that has no formulas but in the same sheet “Ver B”. I do a lot of excel and in my experience it will be much cleaner if no formulas involved and a separate spreadsheet for formulas (but that’s just me). I must say I did not experience your problem coz I just use the data from Revit/Dynamo as a temp or a holding spreadsheet and all the lovely stuff happens on a separate spreadsheet.


#5

That makes sense. Let each app do what its best at.

Thanks for taking a look

Daniel


#6

Your problem may be related to this bug:


#7

Dynamo 1.2
Win 7
Excel 2007

Another strange thing is that although I have Excel 2007 as the default version Dynamo opens Excel in the 2003 version.

Anyways: I write Revit-room-element property data to cols 1-3. Col 4 returns the total area of each of several rooms types as defined in Col 2 (room type 1 or 2 or 3). If I open an existing sheet after closing the whole setup, say in the morning when I log on at the office, the sheet gets overwriten by Dynamo, not just cols 1-3. So, no formulas and no sheet based macros???

It does seem buggy especially the part where it opens a legacy version of Excel. Any thoughts would be appreciated.

Thanks

Daniel


#8

Interesting i will replicate this issue on my pc. Although i dont do this way i am sure i will encounter this in the future. Good to know i will post what i come up.


#9

Glad to have generated some interest. I have a better idea of the problem though I have no better idea of a solution. I noticed in Windows Explorer that the file automatically generated by Dynamo (out of the box v1.2 no packages) is a .xlsb, which was new to me. It is a binary workbook not an .xml workbook. Not sure if that matters. I still think the problem is that the sheet is getting overwritten by dynamo each time so any data added downstream will overwritten when Dynamo opens. btw overwrite is set to false in the Write.Excel node.