Prevent Excel write to recreate the sheet

Revit
1

Dear all,
I’ve a create an export to excel in order to have a list of element with related spaces information. Basically with these script I’m able to have the list of spaces related to an MEP element (duct, cable tray, pipe, air terminal, etc.) If the element is crossing more than a space I can have the entire list of spaces.

2016-12-20 09_50_47-Greenshot.jpg1920×1200 359 KB

For a construction company this is very useful and I’m able now to link this information to a planning but, this is not the problem.
My issue is related with the excel write node. In my Excel I’ve a deep bounces of formulas reading from a sheet which basically contains the list coming from Revit
2016-12-20 09_45_42-test.xlsx - Excel.jpg846×613 159 KB

Every time I update the model and export again, Dynamo deletes the sheet and recreate the sheet again, causing Excel to loose all the references.
2016-12-20 09_47_08-test.xlsx - Excel.jpg1468×1003 540 KB

The only way is to use the function replace in Excel, to relink all the references to the name of the correct sheet.

On the other side, if I’m choosing to not overwrite the excel from dynamo, I don’t have this error but, I’ve to delete manually the sheet content before exporting, in order to prevent old information to be in the sheet.
Question: Is there a way to export information to a sheet in overwrite mode, without creating a sheet from scratch?

As always thank for helping!
Cesare

1 Like
2

Hi Cesare

I suggest using one spreadsheet (or worksheet) to contain the raw data from Dynamo and link that data into another spreadsheet/worksheet which contains the formulas/charts etc

So you would just write the data with Dynamo then refresh the ‘reporting’ spreadsheet

Andrew

3

Thanks but I tried already this.If two files are both opened you have the same issue. I’ll try with one of them closed but I don’t like this solution :slight_smile:

4

Anyway, even if you try to maintain two separate excel sheets you’ve the same error

2016-12-20 11_22_10-Greenshot.jpg1920×1080 434 KB

Basically you’ve an error on the rows that you delete from the model.

5

another option is to use a proper database that knows about about appending data
Then either use a view in the database, or link into Excel to get the data/charts that you want.

Search on this forum for SQLite- some people seem scared off and try to do everything in Excel, but it is really pretty simple

Andrew

6

@Cesare_Caoduro3
Try to set the bool for overwrite excel sheet, to false.

7

This is not solving the issue. If you have less rows then you won’t delete previous information

8

Hi @Cesare_Caoduro3

Are you saying if you hit Run the previous sheets in your model gets deleted?

9

When you export to Excel you can choose to overwrite or not the worksheet. If you choose yes, in the excel file the worksheet is deleted and recreated runtime and with this, all the references from other worksheets and formulas are lost.
If you choose “no” the worksheet is not deleted but then you are not sure that all the previous information are cleaned.
I would like something to “clean” the worksheet before starting to write new data so, the worksheet still exist and all the references are ok.
Thanks
Cesare

10

@Cesare_Caoduro3 how can you say that it is not clean? May be you can create a screencast showing your issue that might help others to get clear understanding.

11

I wrote this node to clear the worksheet before starting to write and it works.
The only problem now is that at the end of the run, I have two excel opened.

2016-12-26 17_48_49-Greenshot.jpg1920×1080 378 KB

1 Like
12

It’s easy to prove that. If you export to excel a model with couple of elements and then you update the model removing some element and export again, you will see still the previous elements in the excel. THis is because if you choose to not overwrite, you overwrite only changes. This is good you you add more elements but it is not, if you delete elements.

13

@Cesare_Caoduro3 Did you try using Bumble Bee package. You can export values by cell rows and columns.

14

Yes, I tried but is not an export issue, just I think in the export node should be included a function to clear the worksheet if this is already in the excel file, or create the worksheet if not.

1 Like
15

Hi Cesare

I suffered from the same issue. Not sure if you have already figured out but I found that adding a Boolean Node with True helped it work.

Hopefully you found helpful.

Cheers

16

Hi, Im having the same issue. You mean adding this node to the custom Write Excel? or the Bumblebee node WirteExcel?

17

For Excel.Write To File last option, overwrite, you could add Boolean to overwrite the excel instead of updating.

18

This is a bug and still isn’t fixed so you should try Bumblebee as a workaround.

19

I know this was a while ago, but did you ever figure out a better way to do this. I am having the exact same problem you described in your original post.

20

HI Alex,
to be honest I completely abandoned Excel long time ago, to move to more sustainable and flexible dictionaries using JSON.
The short answer is I have never looked into it again.
Good luck!
Cesare