Writing excel code into excel (or duplicating excel sheets)

I’m creating a graph which can count the elements and their type and write them to an excel file.
Preferably, I’d like to sort them by floor, which means, creating new excel files.
Now, Dynamo (or excel itself) creates a new excel sheets when you write data towards that does not (yet) exist.
However, I’d like these excel sheets to do some calculations as well and for them to be possible to be manually adjusted.
Next to that I’d like to create a summary sheet where there would be excel based references to those specific sheets but those would have to be created via dynamo as these references wouldn’t yet exist.

I think the latter is less important (as you could have a work around with Dynamo itself), but a solution for the latter could be to create an excel template and duplicate it with Dynamo before sending data into it.

This is how I’m currently writing the data towards excel:


Why not make a complete data dump to excel (ODBC) and make some pivot tables.


1 Like

Because I don’t know what that is and how it works. :yum:

(Also: are you @Marcel_Rijsmus? If so, why the other account).

Thats me alright, i had some troubles with two way authentication that was introduced, so i use another account.


Well, I’ll look into it and see if it might solve my problem.

It might not be the solution I was looking for but it is a solution so I’ll mark it as solution.
Thank you!

I actually ran into an issue, a pivot table doesn’t automatically update, is there a way you can force this?



Just by running the Dynamo script I mean. :wink:


Dynamo can update your data.
Maybe @Konrad_K_Sobon knows a trick

1 Like

Just a thought, it might be enough to force a rerun of the graph :slight_smile:

Using the node “SpringsUI.Refresh” allows you to do so without having to close and open the workset.

1 Like

I think you’re not understanding the issue there.
I’m trying to draw tables in excel but I need a couple of specifics which I don’t quite know how to create.
Instead of doing it with Dynamo @Marcel_Rijsmus/@m.rijsmus adviced on simply using the pivot table function from excel based on an “ugly” data collection that you’d send into via dynamo, so having most of the more complex functions just run by excel.
These pivot tables are nice, however, when sending a new “batch” of data into excel via Dynamo the pivot table sticks to the previous data instead of the data that’s currently in the data dump.
Having to do the extra step of pressing Alt-F5 may not seem like much, but when forgotten the consequences might be pretty big (I’m reading out data from a Revit file and it will determine how much of something will have to be ordered).
So I’m looking for something in excel to make sure the pivot table just continuously updates, or otherwise something in Dynamo that can trigger the “Alt-F5”.

1 Like

If you are linking either a database or other Excel files into the one with the pivottable- you just set the connection properties as shown below

1 Like

Does it work for a link to another sheet?


only if you create a new excel file for the pivot table, leaving the data in the first excel file. if i read @Andrew_Hannell 's answer well :slight_smile:

That’s what I’m thinking as well, and that isn’t what I want to hear.

Yes, that is what I meant.

I have found it easier to have one file for the raw data (from Dynamo) and one for the pivottable
(with the data file linked into the pivottable file)

The data file just gets overwritten

It means that you can have the pivottable one open while Dynamo runs without any file sharing issues.

1 Like

I guess that might actually be handy.
However, considering I’m reading out data from another excel file it would mean there’d be 3 excel files for every Revit project but I guess that doesn’t really matter much.

the most robust solution is to export all the data to a single (proper) database (not Excel) and then report on this data- including counts, filters, grouping etc etc

This could either use the ODBC export mentioned by @Marcel_Rijsmus, or use Dynamo to export to a database such as SQLite, MySQL or similar.

It probably sounds complicated, but is simpler than you might think

I can’t see what you are exporting- looks like family types/instances ?


I’m exporting family types and most importantly their number.
Bit of an issue with getting another bit of software is that it will mean it would have to be installed on several systems.
I’m already trying to keep the amount of packages in my graphs to an absolute minimum.
I guess it will be excel then.