Dynamo Excel-Export Loop

Hello World of Dynamo.

I am trying my best on looping an excelExport but am not able to do so yet.

There are 4 numbers in each name of the List.

  • get all List-items 0001 to 0099 and export them to Excel (startRow 10)
  • get all List-items 0101 to 0199 and export them to Excel (startRow 20)
  • get all List-items 0200 to 0299 and export them to Excel (startRow 30)
  • (…)
  • get all List-items 9901 to 9999 and export them to Excel (startRow 1000)

I hope you can help me out with that issue - thanks in advance. :slight_smile:

Here’s what i got atm:

1 Like

It’s not exactly clear to me what you are trying to achieve.
If the goal is to have different lists saved in different columns or rows - here’s my take on this.

Excel.dyn (24.5 KB)

1 Like

Let me specify my problem:

The list to sort and split looks something like this.
0001-testobject1-time
0012-test objectR-time
0178-test objectE-fast
1189-test objectK-loop
.
.
.
9942-testobjectY-answer

Now this list should be sorted as follows and exported to Excel:
0001-0099 one line at a time
then 10 lines spacing
0101-0199 one line each
then 10 lines spacing
and so on until 9901-9999.

Try to add a *10 to the results of the range node in the example given.

1 Like

The idea is brilliant and I wish it would work. Unfortunately the Script doesn’t write every single list to a “new” start row but tries to export all lists to every start value instead. This ends in a bunch of incomplete lists and one (the last one) complete list - which isn’t seperated by empty rows as well.

Levels and lacing. :slight_smile:

3 Likes

Hey Jacob, thank you for another quick response. Since i’m new to Dynamo, could you please elaborate on your answer? I know where to make these settings, but I’m not sure if I understood the exact use of it :confused:

Try using shortest lacing on the Data.ExportExcel node. Then set the levels to @L3 (might be @L2 - I am on vacation so i cannot test it myself). If that doesn’t work try longest lacing for data and column inputs, same levels.

1 Like

Shortest lacing with @L3 worked, thanks a lot :slight_smile:
Unfortunately there is no separation between the 100 steps. If I can fix this with some help, I would be very very happy.

I would like to tell him:
Start 0-99 in line 0
Start 100-199 in line 10 (or 2 or 30)
Start 200-299 in line 20 (or 4 or 60)
and so on.

Thanks for helping me out despite your vacation. I hope you have a great time. :relieved:

@ChrisWithRevit

Is the objective just to produce an array of numbers in a particular format in Excel- or is there some dependency on Revit or model data ?

If the former- you’d probably be better off with an Excel macro.
The below is a very simple example- if you wanted to adjust the number ranges, you could create a function with arguments and have an input form i.e. you just input the start or finish numbers on a form

Sub CreateNumberArray()
   
   'row 1
With Sheet1.Range("a1")
    .Value = 0
    .AutoFill .Resize(1, 100), xlFillSeries
End With
    
'row 2
With Sheet1.Range("a3")
    .Value = 100
    .AutoFill .Resize(1, 100), xlFillSeries
End With
    
'row 3
With Sheet1.Range("a5")
    .Value = 200
    .AutoFill .Resize(1, 100), xlFillSeries
End With

'row 4
With Sheet1.Range("a7")
    .Value = 300
    .AutoFill .Resize(1, 100), xlFillSeries
End With

End Sub

Post an image of the dyn. :slight_smile:

Atm it looks like that:

Try startCol @L1.

1 Like

Not working. The Export always uses startCol 0 and no more input than that.

I was able to accomplish your desired outcome using the BBData node and the Write Excel node from the bumblebee package. I had to take the resulting lists from List.Chop and get the data at each index and feed it into a BBData node along with the sheet name and starting cell. Not sure I did it the most efficient way but the result was as intended.

EXCEL%20WRITE%20EXAMPLE

Edit: Here is the file I made. EXCEL WRITING TEST.dyn (40.6 KB) I ran this in Dynamo 1.3.2.2480 via Revit 2018.

1 Like

Hi, I would like to contribute a little for this topic.

Does “Contains” node support multiple search?
I will need to search different parameters from different sheets, but need to keep their structure for the next step. Now it is only two sheets, but the purpose is to be able to search for multiple parameters from multiple sheets.

This is a development for the colleagues, so I’m using Dynamo Studio 2017 since it is not updating every year. if following Revit Dynamo, I will need to rewrite the script every year. (there are many obsolete Dynamo scripts in the repository, the fact is that no one is willing to update them. )

Moving to Dynamo 2.0 means a graph like this (most of them in fact) will run in any Dynamo Revit version from 2017 to 2022. Dynamo Studio is capped at Dynamo 1.3.4, which is missing about 3 years of updates.

Thanks for pointing out. I will need to convince my colleagues to keep learning Dynamo :laughing: