Converting VBA code / .NET libraries to work with xls

[renatoab]

Hi,

I have a program written in C# that comunicates with a VBA macro in an
Excel xls file. The macro creates a worksheet, imports data from a
text file to that worksheet, organizes the data in the cells, makes
some formatting, and save the new xls file.
The problem is: most of the users have Excel installed, but some of them only have alternative softwares to work with spreadsheets.
Those softwares can view the xls files generated, but Excel is still needed to run the macro and generate the file.
I'm searching for the best way to do this:
- Create an OO version of the program
It seems to be very complicated, because the macro language is completely different.
- Convert the code to a compiled VB.NET or C# application.
I know those languages have libraries that allow us to create an Excel file and do some operations on it. And the code can be easily converted. I still have doubts whether the libraries do the entire job or do they need to use Excel installed on the machine?
- Run Excel from a server

Has anyone dealed with this problem before?
As VBA programmers, what do you think is the best option?

 

[jimrward]

i have just completed an exercise for extracting data from Active directory and all the examples I could find were using VB scripts, so I created the scripts and this in turn created the excel files, I did however produce another version which creates a CSV file, which any spreadsheet package or application could open and did not rely on excel.

I have since converted the VB script to VBA so that it runs quicker

However unless you can manipulate the datafile using a script you are going to have to rely on excel or the 3rd party spreadsheet at some point in the process

 

[renatoab]

Thank you, jimrward,
I'm afraid this would not work in my case, because my program is completely based on Excel commands, like merging, formatting, copying and pasting, etc.

 

[RoryA]

The Interop assemblies that are generally used with .Net do require Excel to be installed.
There are third party libraries for creating Excel workbooks without using Excel. For versions from 2007 onwards, you can create an Open XML format workbook without needing Excel at all (if you know what you are doing).

 

[renatoab]

Thank you, rorya,
I think generating an xml file is a good idea, because it is easy to read it from many applications, specially spreadsheet ones.
But this would be a big job, and at first I need an easier solution, whithout needing to change many things on my VBA program.

 

[jimrward]

can you elaborate more on what you are trying to achieve and maybe post your code thus far

 

[renatoab]

My code has about 13000 lines and is completely based on Excel commands.
It basically receives a text report, pastes in a new worksheet, and apply many formatings on it, like text to columns, changes the positions of data, make charts, format colors, group lines, merge cells, format numbers, search and replace, separate data in different tabs. And the report have about 30 sections, which have to be treated separately, diferently, and dinamically, because the structure and size of the report can vary.
That's why the code is dificult to translate, and I'm looking for the best way to maintain the largest part of the original code.