Running Excel calculation on a server remotely, as a calculation engine.

Hi, welcome to the forum.
If it's a spreadsheet and you need the code in it, you will need to address it (in Excel) to use it.
You speak of over a period of some twenty tears so may I assume that the Excel version it was created in is previous to Excel 2007?
That would mean that the Excel file has the extension xlsx?
I would suggest you first open it and save it as a new version macro embedded file (extension is xlsm)
You can use other file's code if you open it in the background, and if you just need it to read data you can open it as read-only so that others can access it too, but with the minimal explanation you have given and no idea whatsoever the 'intricate' file looks like and what kind of calculations you want to carry out it's difficult to say.
Another thing that would be good to know for us is what is your level of macro knowledge (VBA).
It might also be a project which is a lot more that just help but design and rewrite.

Originally Posted by Keebellah

You speak of over a period of some twenty tears so may I assume that the Excel version it was created in is previous to Excel 2007?
That would mean that the Excel file has the extension xlsx?

It's been developed continuously over twenty years, so it has been re-saved with the .xls extension at some point. It currently has the .xls extension.

Originally Posted by Keebellah

You can use other file's code if you open it in the background, and if you just need it to read data you can open it as read-only so that others can access it too, but with the minimal explanation you have given and no idea whatsoever the 'intricate' file looks like and what kind of calculations you want to carry out it's difficult to say.

I think that you misunderstand what I am trying to achieve here. A do literally want to "[Run the] Excel calculation on a server remotely, as a calculation engine". As the file is hugely complex it's not possible to re-implement in a reasonable period of time. It contains a lot of intricate engineering calculations. So rather than try to re-implement it in a programming language, I want to treat the whole excel sheet as a function; i.e. I take some input data from a user via a javascript application running in a webpage, send that to my code on the server, supply the input parameters to the spreadsheet (there are approximately 300), allow it to calculate the results (it calculates 14 separate model scenarios and each has a couple dozen output parameters), then read out all those output parameters and send them back to the application running in the webpage and display them.

While I appreciate that "rewrite it in a programming language" is a potential default response here, I can assure you I've tried and the complexity of the task is simply too high.

Originally Posted by Keebellah

Another thing that would be good to know for us is what is your level of macro knowledge (VBA).

I'm fully familiar with macros, but this is rather a red herring as I'm not trying to do anything within the sheet itself - I want to run the sheet externally via code that I write. FYI, I'm a software engineer.

Originally Posted by Keebellah

It might also be a project which is a lot more that just help but design and rewrite.

It's definitely a massive project that I am in the middle of. But I only have a narrow question for you here: what are the options for taking an Excel spreadsheet and running it remotely as a calculation engine?

The thing is that if you want to use the Excel file as a 'calculator' it still has to be addressed and opened even if it's for a moment, and that will required a script (vbs) or Excel itsself.
If it's still in as xlsx then let me tell you that the filesize will be twice that of an xlsm file saved in Excel 2007 or newer format, I'm not sure but is 2011 a MAC version?
You should look at vbs scripting tools to access / read Excel files and perform calculations using the created functions or macros, the formulas is altogether an other option.
If you want the Excel file to be loaded in memory then you should save it as an AddIn (xlam) file , but leave it on the server, NOT in the predefined Add-In folder.
You then add code the the Excel files (macro embedded then) that will load the xlam file on Opening and close it when finished, that way ALL the code in that file is available so you can address it while calculating
It may all sound like gibberish but its harder to explain the use.

I'd like to take a shot at it, could you attach a sample file with a mock up of one of the calculations you have in this old excel file and also a sample of the data wihich you want to process and where the result must be sent to
Verify, please, if it's a Windows or MAC.
No promises, but maybe I can offer an idea which you as system engineer will probably be able to solve.
I'm a retired system's engineer myself and many time resorted to external scripts to get data from various data sources, even MS Access was an option to store large amounts of data

Just to be sure.
You DO work with Excel and this file will serve as input to calculations made on this other 'old' file you mention?

Having read your later comments, spreadsheet gear may do what you want. It's essentially a calculation engine for Excel workbooks, as an example https://www.spreadsheetgear.com/supp...e=amortization

They have a free trial and the full licence is only 800 quid if it works

Yeah, I agree with Kyle123, you're looking for something that uses the large file you have as a stored procedure to calculate parameters/values passed and the source can be anything.
Hope you find it in the links Kyle123 posted, It's above my knowledge but I'll try and think of a way, maybe even passing all the data to ms access and use that, then you can address it easier with sql and the advantage of ms access is the the formatting is respected at all times contrary to Excel which 'interprets' the format based upon the data passed and sometimes messes it all up