Change cell color in Exceltable

Hello everyone,

is there a command or any possibility to change the cell color or the font color in an exceltable using a Flexsim-Command?

Thanks for your help
S. Jerenz

Why don't you run a VBA macro in Excel after exporting data from Flexsim?
As far as I know, there is no direct functionality to access Excel-functions from Flexsim, because this would require a special interface (like an Excel-API).

Hi,

I tried to solve the problem with a VBA-Macro and I'm still trying it.
But the excelworksheet has too much data (100.000 * 24), so first it takes too much time and second (and more important) Excel crashes very often when it calculates or colors the cells.

Explanation:
I have a table with 6 different simulation runs in it. Every run consists of 100.000 records (rows), each record consists of StartingTime - EndTime - used machine - Duration. So my table has a size of 100.000*24.
My task is to calculate the current mean processing time for each record in a 5th column for each run. After that I have to compare the mean processing times of the 2nd to the 6th runs with the ones of the 1st and mark the differences by coloring the cells.

Because of the instabality of Excel and because of the lower speed I outsource the calculation to Flexsim and now I'm looking for a possibility to outsource the coloring, too.


Thanks and best regards
Stefan Jerenz

Hello Stefan,

If you are placing your results always in the same cells in excel, you could try to use conditional formatting in the excel files. Then the cells will colour as soon as you have added the values to them.
I am not sure why you talk of the instability of excel, because it seems to me that you have just some straight forward calculations that have to be done and excel should be able to cope with that. But even if you do your calculations in Flexsim and paste the values in Excel where the conditional formatting is defined, you should still be able to use the colours without any trouble.

Esther

Hello Esther,

thanks for your answer.
Conditional formating is a possibility and probably the best (or maybe the sole) solution for me.

Concerning the instability of Excel: You are right I have only straight forward calculation but it's hardly not possible to do my VBA-macros (Comparing and coloring) in a reasonable time.
And after two hours I think I can await that Excel has finished it. So in this cases I speak about instability.



Stefan

Hello Stefan,

It is quite easy to create very slow macros with the amount of cells you have. If you disable the updating of the screen and calculations at the start of your macro and enable them again at the end this might speed up your file.
But if you have a lot of cells with a calculation, this can make excel quite slow, because by default excel keeps recalculating the cells.
Also 'accidentally' adding a lot of unused rows or columns with your macro can make excel slow, if your excel file is quite large in size, this might be the case.

Esther

Thanks for your advices Esther,

The recalculating is already disabled.
I will check disabling the updating of the screen and I will delete a unsed rows - I think I have a lot of them in my excelsheets.


Stefan