Automatic Recalculate Worksheets

[Sir_Dan_Fortesque]

Hi Everyone ,

I need to recalculate all present worksheets with VectorScript. I do know that the command for this operation is DoMenuTextByName. However I tried all combinations but still that command does not work.

Can you give me the exact syntax for recalculating every worksheet in a document? We use VectorWorks 9.0.1 Dutch edition. According to the reference manual of VectorScript, the language does not matter.

Thank you very much for your help and I wish you a nice day.

Friendly greetings,

StarGate

[Fuge]

Try something like this. You'llhave to add worksheet name constand handle for each worksheet in yourfile or more advanced create a worksheetlist and have the procedure repeat foreach worksheet.

HTHDave

PROCEDURE WS;

CONST WksName = 'worksheet name here';VAR WksH : HANDLE;BEGIN WksH := Getobject ( WksName ); ShowWS(WksH,True); RecalculateWS(WksH);END;RUN ( WS );

[Sir_Dan_Fortesque]

Hello Dave,

Thank you for your reply and your solution. Indeed it works that way in VectorWorks. However as you said you have to build a worksheet list to automatic calculate every worksheet. The problem to build such a list is that VectorScript has no way of knowing what worksheets are present in your drawing. I have such a procedure which I use.

At the start of the script I connect a handle variable to every worksheet in the drawing. For every worksheet I have to establish a handle manually. Then I can recalculate all those worksheets without opening them first. I will give you the code later today. But when I add a new worksheet, I also have to add a new handle. When I forget, that worksheet will not be calculated.

So I like to find out how I can add those new worksheets automatic. Chances are high that it is impossible.

Your code is very usefull to me because it shows me a different approach from which I was thinking. Maybe when you see the procedure I used, you also see a possible solution.

In anyway I thank you very much for your help. I appreciate that very much. I wish you a very nice day and many success with VectorScript.

Friendly greetings,

StarGate(Chrissy)

[Sir_Dan_Fortesque]

Hello Dave,

As promised in my former post, I give you here the code of my procedure. For every worksheet in the drawing you will find a handle. It is a little stupid.

On this moment I am thinking to use your procedure specifying the names of the worksheets in constants. However I have to find a way to find out how many constants are present and use one handle to calculate every worksheet in those constants. I do not know if an array can be used as a constant. I will try. In the meantime I put my script code here.

With friendly greetings,

StarGate

Code :

procedure ReCalc; { Recalculate every worksheet in drawing }

procedure Calc; { This procedure recalculate every worksheet in your drawing. } { No worksheet has to be visible or open to get this work. } { The only important matter is that you specify all your worksheets } { in your document, otherwise two things can happen; } { 1. in case the forgotten worksheet is open you have the chance } { that it still will be calculated; } { 2. when the forgotten worksheet is not open it will not calculate } { Care has to be taken to that you include all your worksheets } var hndWsA1:handle; hndWsC1:handle; hndWsD1:handle; hndWsF1:handle; hndWsF2:handle; hndWsF3:Handle; hndWsG1:handle; hndWsH1:handle; hndWsL1:handle; hndWsM1:handle; hndWsP1:handle; hndWsT1:handle; hndWsY0:handle; hndWsY1:handle; hndWsY2:handle; hndWsY3:handle; hndWsY4:handle; hndWsY5:handle; begin { Establish link to all your worksheets } hndWsA1 := GetObject('A1. Afwerking'); hndWsC1 := GetObject('C1. Chappe en Vloer'); hndWsD1 := GetObject('D1. Binnendeuren'); hndWsF1 := GetObject('F1. Betonplaat'); hndWsF2 := GetObject('F2. Kruipkelder'); hndWsF3 := GetObject('F3. Kelder'); hndWsG1 := GetObject('G1. Gyproc'); hndWsH1 := GetObject('H1. Houten structuur'); hndWsL1 := GetObject('L1. Sanitair'); hndWsM1 := GetObject('M1. Metselwerken'); hndWsP1 := GetObject('P1. Pannen en goten'); hndWsT1 := GetObject('T1. Trappen'); hndWsY0 := GetObject('Y0. Basisprijzen'); hndWsY1 := GetObject('Y1. Forfaits'); hndWsY2 := GetObject('Y2. Kermit'); hndWsY3 := GetObject('Y3. Co?fficienten'); hndWsY4 := GetObject('Y4. Informatie'); hndWsY5 := GetObject('Y5. Fozzie'); { Recalculate all connected worksheets } RecalculateWS(hndWsA1); RecalculateWS(hndWsC1); RecalculateWS(hndWsD1); RecalculateWS(hndWsF1); RecalculateWS(hndWsF2); RecalculateWS(hndWsF3); RecalculateWS(hndWsG1); RecalculateWS(hndWsH1); RecalculateWS(hndWsL1); RecalculateWS(hndWsM1); RecalculateWS(hndWsP1); RecalculateWS(hndWsT1); RecalculateWS(hndWsY0); RecalculateWS(hndWsY1); RecalculateWS(hndWsY2); RecalculateWS(hndWsY3); RecalculateWS(hndWsY4); RecalculateWS(hndWsY5); end; { End of procedure Calc }

begin { Start Main Program } Calc;end; { End of script ReCalc }run(ReCalc);

[Sir_Dan_Fortesque]

Hello Dave,

Finally I found the solution for this problem. I reduced the codelines from 78 to 43.

First I create a constant where I specify the total worksheets that has to be calculated.

Then I create an array (of type string) with the size equal to the total worksheets.

I filled all the array indexes with the names of the worksheets.

Then it is simple, I use a For loop for browsing throught the complete array and with each step a handle to the specified worksheet is connected and the second instruction recalculate the specified worksheet.

Despite we created a very nice workaround together, it should be Nemetschek who has to solve the final problem. All those lines could be replaced by one instruction "DoMenuTextByName". There is only one problem, this instruction doesn't work. Whatever you tried WSRecalculate to the local language translations, nothing works. Maybe someone of Nemetschek can explain why this is not working.

I am sure we helped a lot of other users too, who will find this technique very usefull. In the meantime, we have to wait until Nemetschek solves this problem.

I thank you very much and I wish you a nice day.

With friendly greetings,

StarGate(Chrissy)

FINAL CODE :

procedure ReCalc; { Recalculate every worksheet in drawing } const intTotWorksheets=18; { Total (exact) worksheets in document }

procedure Calc; { This procedure recalculate every worksheet in your drawing. } { No worksheet has to be visible or open to get this work. } { The only important matter is that you specify all your worksheets } { in array strWS. Also make sure that intTotWorksheets contain the correct total worksheets. } { Thanks Dave (Fuge), your solution brought me on this idea. } var hndWS:handle; { Handle to worksheet to be calculated } strWS:ARRAY[1..intTotWorksheets] OF STRING; intIndex:integer; begin { Specifying worksheets in array } { New worksheets can be added at any position in this list } strWS[1] := 'A1. Afwerking'; strWS[2] := 'C1. Chappe en Vloer'; strWS[3] := 'D1. Binnendeuren'; strWS[4] := 'F1. Betonplaat'; strWS[5] := 'F2. Kruipkelder'; strWS[6] := 'F3. Kelder'; strWS[7] := 'G1. Gyproc'; strWS[8] := 'H1. Houten structuur'; strWS[9] := 'L1. Sanitair'; strWS[10] := 'M1. Metselwerken'; strWS[11] := 'P1. Pannen en goten'; strWS[12] := 'T1. Trappen'; strWS[13] := 'Y0. Basisprijzen'; strWS[14] := 'Y1. Forfaits'; strWS[15] := 'Y2. Kermit'; strWS[16] := 'Y3. Co?fficienten'; strWS[17] := 'Y4. Informatie'; strWS[18] := 'Y5. Fozzie'; { Recalculate every specified worksheet } For intIndex := 1 To intTotWorksheets do begin hndWS := GetObject(strWS[intIndex]); RecalculateWS(hndWS); end; end; { End of procedure Calc }

begin { Start Main Program } Calc; end; { End of script ReCalc }run(ReCalc);

[MullinRJ]

Chrissy, You're gonna like this! There IS a way to get a HANDLE to all the Worksheets (WS) in an active document. The trick is to get the first (or last) handle of the WS list so you can step through the entire list.

In this approach, I create a dummy WS and get a handle to it. This precludes having to know the name of any WS in your document. Once you have the handle to a new WS (creating it places it at the end of the list), stepping backward through the list is quite easy.

The IF statement is necessary to screen out the WS's since the list also contains objects that are not WS's. (It is left to the reader to determine what objects these might be). I tested this code (with some minor modifications) on VW 8.5.2, so I was not able to run the 'RecalculateWS' command which is new to VW 9, but I was able to determine the number and name of each WS in my drawing.

If you like this method, drop me a line. If you REALLY like it, send money. Happy Recalculating.

Ray MullinMullinRJ@aol.com

*****

PROCEDURE RecalcAllWS;CONST aWorkSheet = 18;VAR WSHnd, hndDummyWS :Handle;

BEGIN { Get handle to a Dummy Worksheet which gives you a handle in the WS list } hndDummyWS := CreateWS ('DummyWS', 1, 1); { New to VW9 }

WSHnd := hndDummyWS; { save to temporary variable } while (WSHnd<>nil) do { Traverse the WS List } begin if (GetType(WSHnd)=aWorkSheet) then RecalculateWS(WSHnd); { New to VW9 } WSHnd := PrevObj(WSHnd); { Next Obj on WS List } end;

DelObject(hndDummyWS); { delete when done }END;Run(RecalcAllWS);

******

If you need to step through WS's in VW8.5.2 or earlier, replace :

hndDummyWS := CreateWS ('DummyWS', 1, 1); { New to VW9}

with :

NewSprdSheet ('DummyWS', 1, 1, 1, 1, false, false); hndDummyWS := GetObject('DummyWS');

RecalculateWS() won't work in these versions, but you may have other things to do to your WS's.

[StarGate]

Hello Ray,

Thank you very much, indeed I like your solution very much. It is just what I was looking for. I will try it out first thing in the morning and I send you a message.

Have a nice day and very much VectorScript programming. What should I do without you all!!!

Friendly greetings,

StarGate(Chrissy)