Can excel-dna open a new worbook in manual calculation

[Paul Poupet]

Hi everybody,

 

i have well advanced with my program, but i can't find some informations (i have searched for a while now) : my program need to open a new workbook with the functions and the ribbon i have created. But the document which is opened has to be in manual calculation since its openning until its closing. I have the functions, but i do not succeed to automatically disply the automatic calculation.

I have seen many things with 'xlCalculationManual' or other things, but i don't find the same in C# and i do not know if the document is directly opened in manual calculation or if it is switched after.

I hope i made myself clear, i am as bad in english as i am in programmation.

Thx,

 

Paul

[Patrick O'Beirne]

Excel's calc mode is set from the FIRST workbook opened in a session.
To open a workbook in manual calculation mode, you need to first save it with calculationmode set to manual in the Excel options
then before opening it either have no workbook open or if any is open (eg blank book1) set calculationmode=xlcalculationmanual
THEN open the workbook you want to be in manual calc mode.

see charles williams' site decisionmodels.com site for all about excel calculation secrets.

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Paul Poupet]

Hi Patrick, thanks for the tip,

So if i send the program to someone who is in automatic calculation on his session, i cannot force the manual calculation of the worbook.

By the way, my refresh button (on a ribbon) does not work, do you have any idea why ?

 

 if (control.Id == "RefreshWorkbook")   // this is the name oh my button
        {
        Excel.Application oXL;
        Excel.Workbook oWB;
        oXL = (Excel.Application)Marshal.GetActiveObject("Excel.Application");
        oXL.Visible = true;
        oWB = (Excel.Workbook)oXL.ActiveWorkbook;
        oWB.RefreshAll();
        MessageBox.Show("Refreshed!");
        }

 i am in manual calculation and when i press the button, nothing happens..

 

Cheers and thanks for your help

[Patrick O'Beirne]

You cannot *force* it but you can check in your XLL addin whether any workbook is open *before* you open the workbook.
If so, set calc manual. If not, just open it, assuming it has manual calc set. Do you control how it is saved? If so, ensure calc is manual before it is saved.

Refresh .. I can't answer that at the moment but I think you should be using the Excel application object from Excel-DNA rather than GetActiveObject, which might return some other instance of Excel  - I'm guessing.  Govert will know, I'm a bit out of touch with Excel-Dna at the moment.

--

[Patrick O'Beirne]

Oh, it just occurred to me - are you refreshing Pivottables? That's what RefreshAll does.
If you mean to recalculate,  then it's owb.calculate
or .CalculateFullRebuild for everything - see decisionmodels.com for details.

On 17/07/2014 13:28, Paul Poupet wrote:

--

[Govert van Drimmelen]

Hi Paul,

Patrick is right - instead of 

    oXL = (Excel.Application)Marshal.GetActiveObject("Excel.Application"); 

you should rather call

    oXL = (Excel.Application)ExcelDnaUtil.Application; 

This ensures you get hold of the Excel instance that your add-in is running under, rather than another one or a new instance.

-Govert

On Thursday, 17 July 2014 18:53:17 UTC+2, Patrick O'Beirne wrote:

Oh, it just occurred to me - are you refreshing Pivottables? That's what RefreshAll does.
If you mean to recalculate,  then it's owb.calculate
or .CalculateFullRebuild for everything - see decisionmodels.com for details.

On 17/07/2014 13:28, Paul Poupet wrote:

Hi Patrick, thanks for the tip,

So if i send the program to someone who is in automatic calculation on his session, i cannot force the manual calculation of the worbook.

By the way, my refresh button (on a ribbon) does not work, do you have any idea why ?

 

 if (control.Id == "RefreshWorkbook")   // this is the name oh my button
        {
        Excel.Application oXL;
        Excel.Workbook oWB;
        oXL = (Excel.Application)Marshal.GetActiveObject("Excel.Application");
        oXL.Visible = true;
        oWB = (Excel.Workbook)oXL.ActiveWorkbook;
        oWB.RefreshAll();
        MessageBox.Show("Refreshed!");
        }

 i am in manual calculation and when i press the button, nothing happens..

 

Cheers and thanks for your help

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.

To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+unsubscribe@googlegroups.com.

[Paul Poupet]

 

I will try this thanks !

to patrick : the fact is that i want  to send my program, and most users won't save anything, they just click on the program, it runs the excel sheet, then they do their stuffs, then they close it without saving it. And for many functions i need to put manual refresh (because my database is always having new datas), and in the ribbon i want to create 3 buttons : refresh all (the code i posted), refresh sheet and refresh cell... The last one seems tricky !

I try your idea and i come back.

 

Paul

[Patrick O'Beirne]

If you control the sheet then that's easy Paul, set manual calc before working with it.

Bear in mind you are not refreshing sheets or cells, but refreshing pivottables
and for that you don't need your own buttons, they are already there in the Data tab of Excel.

--

[Paul Poupet]

Hi patrick,

The problem is that if i send the program, i have to ask people to set excel in manual calculation before they use it..if they don't do that there will be too many recalculation. And some are not welcomed. That's why i would like to do it automatically. Keeping in mind that they won't save the sheet so i can't use the path to refresh it. That is the trick. And even though you can refresh using the datatable, i would like to add it to the ribbon with very simple buttons to do that.

Maybe i don't make myself clear, as i said my english is not perfect neither are my skills in programmation , sorry ..

 

Paul

[Patrick O'Beirne]

Paul,
You don't have to ask them to do anything.
You set manual calc mode in the addin.

ExcelDnaUtil.Application.Calculation= -4135 '  xlCalculationManual

Then, you either open an existing workbook or create a new one. As you do not save the file, it is not there to be opened, so you add a new workbook with a new sheet, eg

oNewWorkbook = ExcelDnaUtil.A pplication.Workbooks.Add( -4167 )    ' xlWBATWorksheet ' new book 1 worksheet

oNewWorksheet = oNewWorkbook.Worksheets(1)

Then I imagine your addin creates pivottables and formats them as the users want.

and before your code exists, you should set calc back to automatic as the users expect.

ExcelDnaUtil.Application.Calculation= -4105 '  xlCalculationAutomatic


You can your own buttons, but the existing ones look very simple to me:

Data > Refresh All
which has a dropdown menu of other options.

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.

To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.

[Paul Poupet]

Hi govert,

when i put the line you gave me i still have an error : 'System.__ComObject' does not contain a definition for 'RefreshAll'

i must be missing sthg...

 

[Paul Poupet]

It works, thx. I have just changed small things to adapt the code, but it is a great thing !

for the button, the problem is that most of users don't know how to refresh only one cell or only one sheet.. So i want to simplificate it using only one button.

But my refresh code does not work i have this error with " .refreshAll()"  i do not see why...

 

Anyway, thanks a lot, you helped me a lot with this !

 

[Govert van Drimmelen]

Hi Paul,

It looks like "RefreshAll" is a method on Workbook, and not on Application: http://msdn.microsoft.com/en-us/library/office/ff838648(v=office.15).aspx

It "Refreshes all external data ranges and PivotTable reports in the specified workbook."

It's not clear from your discussion whether you mean to specifically refresh PivotTables, or want to recalculate a book or sheet - in which case you'd use some .Calculate or .CalculateFull method, instead of RefreshAll.

-Govert

[Paul Poupet]

this is not a pivot table, i want to recalculate the book or the cell selected.

I am sorry but it is hard for me to explain this in english.

 

Calculate works for the book and the sheet (thank you for this), but not for the cell...

 

To sum up and to try o explain my point :

1) How to refresh the selected cell,
(    Excel.Application oXL;
        Excel.Workbook oWB;
        oXL = (Excel.Application)ExcelDnaUtil.Application; 

        oXL.Visible = true;
        oWB = (Excel.Workbook)oXL.ActiveWorkbook;

        oWB.ActiveSheet.calculate();   ) works for the book, but how to extend it to a cell

 

 

2) If there is manual calculation on exxcel, and have a function, i calculate it, then i have my button "refresh book", is there any cache (i mean, will excel search in a cache and return an answer without re-requesting my database) or no?  If there is a cache, how can i force the re-request..

 

If sthg is not clear tell me.

 

 

Thanks,

 

Paul

 

[Patrick O'Beirne]

Paul, no problem about English, that's my job to understand you. My French ( I guess?) is nowhere near as good as your English!
Google Translate will not help with Excel code!

There are two quite separate concepts:

Re-Calculation is done by:
Application.Calculate or Application.CalculateFull

You refer to a cache, which is the term used for a PivotTable cache, and that is what is refreshed with .RefreshAll
Yet you say you do not use a pivot table.
What data connection are you using? How are you actually getting the database data into the sheet?

Don't bother trying to calculate a cell. Just do Application.Calculate, it's safer. That recalculates every Excel formula that *needs* to be recalculated.

If you *really* want  to,  then re-enter the formula
cell.formula=cell.formula
but I strongly suggest don't do that, be safe in case there are dependencies.

You say "I and have a function, i calculate it, " is that a function in the sense of a User-Defined Function (UDF) used in a formula like

=MyFunc(A1,12.3)

which cannot affect any cell other than the one it is in;

or is it simply a function procedure in your code that you call to perform some calculations and update some cells?
In which case, Excel will never call that, you have to do it in your code.

--

You received this message because you are subscribed to the Google Groups "Excel-DNA" group.

To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.

[Paul Poupet]

Thanks for your reply patrick it is really nice from you ! (and yes i'm french)

So : here is my goal :

I have a server, i want to create an addin to use datas and stored procedures on an excel spreadsheet. For instance, I have a stored procedure "getHisto('id') which do the request with an sql connexion, and return a table with the history of the prices of my object (i am simplificating but the idea is there).

The fact is that every data can be modified and a particular user may want datas of the moment he entered the function OR datas of the moment he press the refresh button. (it depends)

So the program already set the manual calculation.

Then i have my functions (stored procedures or simple fonctions (SQL requests)).

A user canuse a function, then use it elsewhere, which creates too many connections.

 

The first problem is to understand how the excel cache works, so that i can prevent useless connections (there are many datas and many users).

So i want to manage the cache : if the user presses the recalculate button, the program has to check if the value is in the cache to return it immediatly (i do not know how many datas we can store in the cache and how long we can store them). So that i have less requests. (i call it recalculate)

But i need a button that forces the connection, so if i press it, i don't want to use the cache. (i call this refresh)

And the other difficulty is that a user can want to have the refresh on one cell but the re-calculation on the other.

 

 

I do not know if i have a pivot table, i don't know what is it :-)

 

Tell me if you understand my point. (i can send you a part of the code if you want to understand how my function work...)

 

Thanks a lot for your time !

 

[Paul Poupet]

Paul

[Paul Poupet]

http://decisionmodels.com/calcsecrets.htm

A lot of answers on recalculation/refreshing are there.

Thanks again for your help, i am still working on it.

Paul