Using inputs from Excel with API

Help C#
1

Hi!

I have an Excel sheet with input data related to geometry, loads etc. I have then created a VBA script that generates the C# script that is used to set up my FE model ind FEM-Design. The C# script then runs in VS Code using the .NET console application. I would like to have all this automated so my colleagues only need the Excel sheet and an installation of FEM-Design to use the tool i have created.

Is it possible to compile the script using VBA without the .NET SDK installed? And then afterwards run the .EXE file from Excel?

And in addition, could this process be done in a smarter way? (for example using a C# excel library)

Best regards
Kristian

2

Hi @Kristiandl :wave:

Our suggestion is to detach from VBA inside Excel and to start writing your own application :slight_smile:

The application can than be sent to your colleagues or share it online. We are big fun of open-source :slight_smile:

There are several library to read excel files with pro and cons.

You might want to start having a look at Microsoft.Office.Interop.Excel which is shipped with Excel. It is probably already in your machine.

Your code will have a flavour of the following attached code

//Create link to excel and read parameters to lists:
Microsoft.Office.Interop.Excel.Application oExcelAppication = new Microsoft.Office.Interop.Excel.Application();
 
Microsoft.Office.Interop.Excel.Workbook oWorkbook = oExcelAppication.Workbooks.Open(ExcelFilePath);
Microsoft.Office.Interop.Excel.Worksheet oWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)oWorkbook.Sheets[1];
Microsoft.Office.Interop.Excel.Range oRange = oWorksheet.UsedRange;

var cellValue = oWorkSheet.Cells[2, 1] as Microsoft.Office.Interop.Excel.Range).Text.ToString();

We have a little example that should help you in creating a WindowsApplication that you can share with your colleagues.

@Joni has done a great video where it is highlighting a pretty cool automation between Excel and FEM-Design.

3

Thanks for the quick response - i will look into this! :slight_smile:

4

When i try to run the example i get the following error:

image
image1202×137 11.1 KB

I have version 6 of .NET installed. Do i have to change anything in the example code to make it run?

Company policies makes it a bit daunting to install another SDK so if it is not necessary to install V 4.8 it would be highly appreciated.

5

You should be able to Edit the csproj file;
i.e. from

<TargetFramework>net6.0</TargetFramework>

to

<TargetFramework>net48</TargetFramework>
6

It still throws the same error message… :confused:

7

you should probably try to “clean” your solution.

If you set up correctly the framework, the error should at least change :slight_smile:

This is how my Practical example - Windows Presentation Foundation (WPF).csproj looks like after the modifications.

I have also updated the C# language to avoid other errors.

  <PropertyGroup>
    <OutputType>WinExe</OutputType>
    <TargetFramework>net48</TargetFramework>
    <RootNamespace>Practical_example___WPF</RootNamespace>
    <ImplicitUsings>enable</ImplicitUsings>
    <UseWPF>true</UseWPF>
    <LangVersion>latest</LangVersion>
    <Nullable>enable</Nullable>
  </PropertyGroup>
8

Ehi @Kristiandl

Have you managed to solve the issue?

9

Hi @MarcoPelle

Unfortunately not. I suspect that something might not be properly installed/set-up correctly on my PC with regards to the SDK and VSC. I’m gonna try and use Visual Studio and see how it goes.