Connecting Excel with SOFiSTiK is something used quite a lot since SOFiSTiK Results offers the Excel export. But being a one-way link, it isn’t a complete solution.
In this post, I want to highlight one of the features of SOFiSTiK 2023. A feature you might not find promoted across social media. But I believe many will benefit from it.
There isn’t a simple way to get data from Excel into SOFiSTiK. You can use the workflow explained in this online tutorial, which requires some experience with VBA. Or you could simply copy/paste, which works, but requires additional effort every time your data in Excel changes.
With SOFiSTiK 2023, a new CADiNP command has been added to open a completely new workflow. I know; you might think instead of VBA, you need to know CADiNP to get data from Excel to SOFiSTiK. And you are right. But trust me, it isn’t that difficult to set it up, and worth the effort. Besides, you benefit from staying within SOFiSTiK, which is perfect.
Let’s find out more about the workflow.
Step 1: A new or existing project
Open an existing project or create a new one; it doesn’t matter if it’s a project built in SOFiSTiK structural Desktop or Teddy.
In SOFiSTiK Structural Desktop, add a new text task. You can find it at the very top of the task library. It’s the one with the yellow teddy bear icon called “Text Editor”—double-click on the new inserted text task.
If you use Teddy, click at the start, end, or somewhere between two programme modules.
Step 2: Create an Excel file/sheet
Ok, independent if you use SOFiSTI KStructural Desktop or Teddy, enter the following input lines to create a TEMPLATE programme block.
Between the input lines “head” and “end”, enter the following input lines:
|sto#xls.name||The input line sto#xls.name “…” defines the Excel file. If the file doesn’t exist, a new one gets created. Furthermore, “xls.name” is a SOFiSTiK variable, which means you can have only one single excel file active in the project at the time.|
If you want to use a different excel file, you will have to reenter this input line referring to that particular file.
|sto#xls.sheet||As every excel file comes with sheets, this is also a required input covered by the input line sto#xls.sheet “…”. Same as for the input xls.name; this is a SOFiSTiK variable valid for the entire project.|
Step 3: Write into the Excel sheet
The Excel file and sheet have been set in our project. Now it’s time to write information into it. You can also use SOFiSTiK Results to export results. And I assume it will be the preferred option. Or you might have an existing Excel file already, then you can jump ahead to Step 4, where you can read about how to read the excel sheet.
If you use CADiNP on an intermediate to expert level, writing data precisely to a certain cell in an excel sheet can be very tempting. To learn that, keep reading.
Add the following input line:
|let#xlsWrite||This is a dummy variable required to get the CADiNP command to work.|
|xls.write(1,2,99)||This is where the magic happens. The xls.write command writes the value 99 into the cell “B1” in the excel sheet “Sheet1”|
Your script should look like the one below.
As you actively write something in the Excel file, it is essential to close it before running the input. Otherwise, you might run into an error while running the script.
Using a variable for the cell value instead of a value is also possible. You can see it in the below input. The variable #value has been used.
Give the above script a try before moving on to Step 4. Play around with the values or add another xls.write command.
If you don’t know how to run the script in SOFiSTiK Structural Desktop, do the following.
- Save the project.
- Right-Click on the Text input Task (the one with the yellow teddy bear) and calculate it.
If you run into an error during the calculation, ensure the excel file is closed, and you have the admin rights to create a new file in the directory.
Step 4: Read from an Excel file/sheet
Ok, we have a new excel file with the value “99” in cell “B1”. Let’s see how we can read it and save it in a variable in CADiNP.
Create a new TEMPLATE programme module block and enter the following input lines. You can copy/paste it to save time.
Let me explain the input to read data from an Excel sheet.
|let#xlsRead||When reading a value, you want to save it in your project. The value gets saved in the local variable “xlsRead”, and you can print or use it otherwise afterwards. You can pick any variable name; it should make sense in the context of your project, though.|
|xls.read(1,2)||To read the data with the xls.read command, only the Row and Column must be entered. In this case, the cell value “B1” gets read.|
You can keep the Excel file open while reading it. But keep in mind to save the Excel sheet to read updated values.
Only one last input line to explain; txe #xlsRead. The print text at the end of the report command will print the value of variable xlsRead in the SOFiSTiK Report.
Now run the whole script and check the report. You should find the chapter “Conclusion” showing the value “99”.
The new write and read commands can be used in any programme module; you are not limited to the TEMPLATE programme module. The advantage of the TEMPLATE module is that it doesn’t perform any analysis, design, or other heavy stuff. So you don’t mess around with the database (CDB)
I just know it; many of you who work with CADiNP and Excel will love this new feature in SOFiSTiK 2023. I can think about the definition of 2-dimensional arrays, which isn’t possible in CADiNP, feeding information to predefined Excel design sheets, or connecting Excel as an input template for project data, just to name a few use cases. And I’m sure there are many more. Another advantage; there is no need to get familiar with VBA, as everything can be done within CADiNP.