The AimmsXLLibrary doesnâ€™t require Excel to be installed in order to read from or write to an Excel (.xls/.xlsx) file.

You first need to add the system library `AIMMSXLLibrary` to your model, and then you can use the predefined `axll::` functions.

Read more...

The last sentence of the provided link says:

Similarly, you can use the

**code:**

AIMMSXLLibrary

I am looking into ways how I can write the multi-dimensional results of my model to Excel, but I haven't been succesful. Which comments is that sentence referring to? Because I think I have not seen it yet, but I can't seem to find it.

Kind regards,

Koen

Please follow https://how-to.aimms.com/C_Developer/Sub_Connectivity/sub_excel_csv/index.html !

Hope this article helps

Yes I have! I actually have that link open as we speak. However, this is not something I have done before, and from what I am actually reading on that page, a lot is about reading data, instead of writing it. I have used the XLL library several times in my project to read data with the functions ReadSet and ReadTable. Now I am actually looking to do the reverse of a ReadTable function. Is there possibly another web-page or guide that shows how this is done?

Kind regards,

Koen

Thanks for your feedback, we will update it.

You may in the meantime check in the library. Comments in the library itself are a rudimentary documentation:

I have been trying to use the comments in the library to make sure that I can generate my own Excel file with results. However, I seem to be unable to do so. If I use the example code from the library, then I get errors such as "the range A4:C14 is not expected here". I have attached my model in this comment, with the procedure "WriteTable". Could you point out what I am doing wrong, or perhaps provide a template that I could fill in along the specifications of my model? FYI: with the code in the model, I tried to get results of the Variable Transport(w2,c,p,t).

Kind regards,

Koen

You unfortunately forgot the library prefix "axll::". Please replace

**code:**

WriteTable( TransportToCustomer(w2, c, p, t), "A4:C14", "D1:G1", "D4:G14", 1, 1)

by

**code:**

axll::WriteTable( TransportToCustomer(w2, c, p, t), "A4:C14", "D1:G1", "D4:G14", 1, 1);

Please mind also to replace ResultWorkbookName in the following:

**code:**

if axll::WorkBookIsOpen(ResultWorkbookName) then

axll::SelectWorkBook(ResultWorkbookName);

else

axll::OpenWorkBook(ResultWorkbookName);

endif;

It's working fine now ! :)

please see attached

Thank you very much! The Excel file generation works flawlessly now. :)

I hope you don't mind, but I do have a follow-up question regarding my model. If I manually check the costs from the Excel files (see files "Results" and "ResultsStock", the manual checks are in there), then these costs do not correspond with the proposed solution of AIMMS.

I have the suspicion that this is caused by either my StockBalancingConstraint and/or my objective cost function TotalCosts.

The code for the StockBalancingConstraint is:

**code:**

if ord(t) = 1 then

StockAtWarehouse(w2,p,t) = StartingStock(p,w2) + sum[f, ProductionAllocation(f,p,t) * ProductionRouting(f,w2)] - sum[c, TransportToCustomer(w2, c, p, t)]

else

StockAtWarehouse(w2,p,t) = StockAtWarehouse(w2,p,t-1) + sum[f, ProductionAllocation(f,p,t) * ProductionRouting(f, w2)] - sum[c, TransportToCustomer(w2, c, p, t)]

endif

For this constraint I think that the sum(f, ProductionAllocation) may be wrong, because I want the full production of a certain factory, so that would be the sum of p. However, when I replace the f by p, I get the error remark "the index p already has a scope".

The code for my objective function TotalCosts is:

**code:**

sum[(w2, p, t), StockCostsPerPeriod(w2) * StockAtWarehouse(w2,p,t)] + sum[(p,w2,t,c), TransportCosts(c,w2) * TransportToCustomer(w2,c,p,t)]

Is there perhaps something wrong with the order in which I present the indices in each of the summation formulae? Or could you possibly point me out to anything that may be wrong in these declarations? In terms of syntax they don't cause any errors, but I think that there is perhaps still a logical mistake in here that I cannot see.

Kind regards,

Koen

Hi**Quick answer:**

Indeed, because p is used in your StockBalance Constraint index domain, you can't use this particular index in a sum. You will need to declare another index, from the same set, and replace it in the sum. AIMMS will use it to run this nested sum .**Explanation:**

The index domain of your constraint (or any AIMMS identifier) could be seen as a big "for" statement, running over each index of the index domain. Each index in your index domain will get a particular element value, and then the constraint will be generated.

Obviously, when you will generate the constraint for w2 = 'w2_1' p = 'p1' and t='t1' for example, you don't want to do a trivial sum on p = 'p1', but a full sum on all the p's! The way to rigorously specify that to AIMMS is by declaring another index (over the same set as p, in our case the set named Product). Let's give it a name, index 'other_p' Then your formulation will look as follows:

`StockAtWarehouse(w2,p,t) = `

`StockAtWarehouse(w2,p,t-1) + `

`sumsother_p, ProductionAllocation(f,other_p,t) * ProductionRouting(f, w2)] - `

`sumsc, TransportToCustomer(w2, c, p, t)] `

**Error explanation:**

AIMMS is throwing an "index has already a scope" error because when trying to compile your "sum" statement, AIMMS requires an index as first argument, and not a simple element, or "an index that already has a scope". Et voila ! Of course , this error is not specific to the sum, but also the "average" function for example, or every intrinsic function that needs an index to run on.**Note:**

You still can use p in the sum numerical expression (2nd argument of the sum), but not in the first argument (binding domain of the sum). That is why AIMMS **has to** throw an error, and can't correct your statement, guessing that you wanted to make a local sum. Otherwise, how would AIMMS know if p is for the sum, or for the constraint generation ?..

### Reply

## Sign up

Already have an account? Login

**Please use your business or academic e-mail address to register**

## Login to the community

No account yet? Create an account

Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.