Availability Atom connected to Excel/Access?

All topics on the Atoms in any Enterprise Dynamics Library.
Post Reply
EvelineHermans
Posts: 28
Joined: Tuesday 28 July, 2015 - 10:34

Availability Atom connected to Excel/Access?

Post by EvelineHermans »

Is there a way to connect an Excel-document to the Availability Time Scheduele? I would like to manage these numbers by just one simple document (Excel or Access) so that I can make a change in these tables easily. When I reset the model, I would like to also update the availablities from the document.

Thanks in advance!

Eveline
Bram de Vries
Posts: 60
Joined: Thursday 08 January, 2015 - 13:29

Re: Availability Atom connected to Excel/Access?

Post by Bram de Vries »

Hello Eveline,

There is a way to do what you suggest. You will need a few atoms to achieve this. I made an example model to illustrate my method, which you can find in the attachment. The first atom you need is an ExcelActiveX atom. By double-clicking it, you can connect this atom with an excel file (in the model you will see my test file listed after File Name). As you can see, you can browse for your own file. You can select the Sheet you want to read your information from. Under the Tables tab, you can then select the number of tables you want to include in the ExcelActiveX atom and update the number of Rows and Columns they will have. You can also select the way you want this table to exchange information with your Excel File (read or write), where this exchange should start (row and column number) and how many rows and columns should be read/written. You can also specify when you want this to happen and for you the "On reset" option seems to be what you are looking for. This way the data from your excel file will indeed be written into the table you created when you reset the model.

When this works, there is another thing we need to do. We want to copy the data in your newly created table to a Time Schedule atom. This can also be done on reset of the model and for this you need an Initialize atom. In this atom you can specify lines of code which need to be executed when the model is initialized. You can also check an option in order to have this code executed when you press reset. What I did in the example model is connect the first input channel of the Initialize atom to a Time Schedule atom and define the following code to be executed when you press reset:

Code: Select all

MatrixRepeat(
  nRows(in(1, c)),
  nCols(in(1, c)),
  Cell(mcx, mcy, in(1, c)) := Cell(mcx, mcy, refExcelActiveX2_table1)
)
An explanation of the MatrixRepeat function can be found in the help files of ED. What it does in this case is loop over the rows and columns of the Time Schedule atom (remember this is in(1, c), which you can use to refer to the Time Schedule atom as well as its table). It then updates the cells of the Time Schedule atom to the cells of my newly created table named ExcelActiveX2_table1. I created an alias for this table in its GUI, but you could also of course connect a channel of the Initialize atom to the table you want to read from and refer to it in this way. Now when you press reset, the data from your chosen Excel file will first be copied to the table that you created with the ExcelActiveX atom and then to the Time Schedule atom.

Keep a few things in mind:
- Make sure that the Time Schedule atom has enough rows and columns for the data you want to insert.
- The Initialize atom should be below the ExcelActiveX atom in the model tree. You can update the location of an atom in the model tree by using the function SetRank(x, treeatom), where x is the position you want to assign to the Treeatom (which is the atom you have selected in your model tree).

I hope this helps you along, but feel free to let me know if anything is unclear!

Kind regards,

Bram
Attachments
ExcelAvailability.mod
(6.14 KiB) Downloaded 349 times
EvelineHermans
Posts: 28
Joined: Tuesday 28 July, 2015 - 10:34

Re: Availability Atom connected to Excel/Access?

Post by EvelineHermans »

Thanks a lot for your very clear explaination and your help :)! It works
Post Reply