Is there a way to set the order of the tabs / sheets that Dynamo exports using the Data.ExportExcel node?
I have a script that exports multiple tabs (sheets) to a single Excel file.
The tabs currently export basically in the order that I created each section of the script (I am guessing on this) - each section exports a separate set of data and has its own ‘Data.ExportExcel’ at the end.
As I have added to this script over development of our Revit tools and new tabs are created, the order they show up in is no longer in the order we need.
I basically have to re-order them after the export if I want them to be organized properly for the team (In the sheetName part of the Write node, I have a sheet number and the name to help with this.)
I tried the Passthrough node, plugging the ‘data’ output from the first Data.ExportExcel node into the ‘waitFor’ in Passthrough, then plug the ‘data’ from the next node into ‘PassThrough’ and continued that through all the sections. But it only shows that yes, it waited for the data.
Is there a way tell it to wait before writing the data and creating that new tab in the Excel file?
I tried to upload a screenshot of the out-of-order tabs but I’m only allowed to upload one file.
Thank you!
Instead of two nodes use one with proper lacing and list levels, and the order of sheets created will match the order provided.
Alternatively you can use a passthrough method to prevent the data flowing into the second excel node until the first finishes.
1 Like
Hi Jacob.
I’m not sure I follow on the first idea you mention - but I am attaching a bigger snapshot of the script. There are currently 16 groups of data that write to the one Excel file - the areas grouped in purple. Seeing that larger view, would you still suggest the lacing and list levels? I haven’t done much with lacing yet…
I do currently have passThroughs from one Excel export group to the next, which I think is what you suggest alternatively? If that is what you meant, it’s not working like I’d hoped. The sheets still pop up out of order in Excel.
Thanks!
Yes. It’d be faster and more stable.
Are it looks like the excel writing nodes are not receiving anything from the pass though nodes. Can you highlight each of the excel nodes, and show the sequence of wires connecting the last and first via the pass through nodes?
As a final option, you could try starting with an excel file which has all the desired tabs in it already.
Maybe check the Excel Template File for pollution too
Hi Marcel.
Thanks for the tip.
The way this script writes to Excel - at this time - is that I start with a blank Excel file. There isn’t any pre-formatting.
I’ve done that in the past, and it looks like that’s the same option Jacob is suggesting if I can’t get this to work the way I want.
But thanks!
Amy
Hi Jacob.
I thought about what you mentioned and I redid the Passthroughs so the data coming into the Write node goes through the Passthrough, waiting on the data output from the previous Write node. In my mind, I moved the Passthroughs to the ‘inside’ of the write process and that fixed it. Awesome! (you can compare between my first screen shot and the new one attached.)
Only thing is, the first time I tried it, I did it backwards. I started the Passthroughs at what would be the first tab on the left in my Excel file, so it wrote them to Excel in the reverse of what I want. haha
I redid it, starting from the last section at the bottom of the script and ended it at the top section for the first tab in Excel.
Worked perfectly!
I still need to learn about your lacing suggestion so I can create a more refined script, but at least this fixes this annoying bit I’ve been hoping to fix.
Thanks so much!
