Jump to content

Update specific worksheets


elepp

Recommended Posts

Hi everyone,

I am new to scripting but would really like to learn it, because I see its potential in helping us with small tasks in the office.
I watched some tutorials about python online and understand what the different data types are and what a functions is.

 

I also have a problem to solve and I couldn't find a readymade script for it here in the forum.

 

I would like to update a specific range of worksheets in a drawing.
We do a lot of multi-residential buildings with a lot of apartements. Each apartment has its own worksheet that is placed next to it on the design layer. The worksheets have a standardised name. Before we issue plans I would like to update them, but not some other worksheets. Afaik there are only two commands available atm: 'Update all' or 'Update active'. So that is not helping me.

 

All the python tutorials stretched the idea that you first need to understand the problem and I think I might do in this case. The other important part was to write the algorithm first in plane english before starting to code. So that what I am trying to attempt here:

 

1. Get all worksheets
2. Get worksheet names
3. Filter worksheet for specific names
4. Get worksheets with specific names
5. Update specific worksheets


Any input or comment is more than appreciated!

Regards!

  • Like 1
Link to comment

The command you are looking for is:

 

def  vs.RecalculateWS(worksheet):

   return None

 

Pass a handle for each worksheet you want to recalculate and only those will recalculate.

 

I am a Vectorscript guy instead of Python, but I would probably use a ForEachObject line if you can specify a criteria that will identify only the worksheets you want done.

 

The procedure to be executed by the ForEachObject would just be the single RecalculateWS function.

 

Ask again if you need more information.

 

HTH

  • Like 1
Link to comment

Do you have a pre-determined list of worksheet names, or are you finding names based on a pattern?  If the former, you should be able to use vs.GetObject() to get a handle to each worksheet by name. If the latter, then use Pat’s suggestion of ForEachObject  to process each worksheet, testing the name with your search pattern. 
You can also the ? and * wildcards in your ForEachObject criteria if the name matching pattern is fairly simple. 

  • Like 1
Link to comment

I tried a little Python script for that issue

(this script recommends at least one worksheet with a series of worksheet names which you want to recalculate in the actual file)

 

# this script reads out ther first column of a worksheet
# in the first column are written all worksheet names which you might want to recalculate
# so you can create more worksheets like more sets of worksheets to recalculate (like publisher sets in VW are possible, too)

WSSet = vs.StrDialog(‘Which set of worksheets do you want to recalculate: ‘,’’);
# for loop, which reads out the first column of the "recalculate set"
for row in range (1,10):
	WSSetH = vs.GetObject(WSSet)
	formula=vs.GetWSCellFormulaN(WSSetH, row, 1)
# maybe "WSName=vs.GetWSCellFormulaN(WSSetH, row, 1)" works, too
	WSName=formula
	WSNameH = vs.GetObject(WSName)
# recalculate a Worksheet    
	vs.RecalculateWS(WSNameH)

 

Edited by matteoluigi
  • Like 1
Link to comment
# this script reads out ther first column of a worksheet
# in the first column are written all worksheet names which you might want to recalculate
# so you can create more worksheets like more sets of worksheets to recalculate (like publisher sets in VW are possible, too)

WSSet = vs.StrDialog(‘Which set of worksheets do you want to recalculate: ‘,’’);
# for loop, which reads out the first column of the "recalculate set"
for row in range (1,10):
	WSSetH = vs.GetObject(WSSet)
	WSName=vs.GetWSCellFormulaN(WSSetH, row, 1)
# yes, it works ;-)
	WSNameH = vs.GetObject(WSName)
# recalculate a Worksheet    
	vs.RecalculateWS(WSNameH)

 

  • Like 1
Link to comment
  • 2 weeks later...
On 2/15/2022 at 10:33 PM, JBenghiat said:

Do you have a pre-determined list of worksheet names, or are you finding names based on a pattern?  If the former, you should be able to use vs.GetObject() to get a handle to each worksheet by name. If the latter, then use Pat’s suggestion of ForEachObject  to process each worksheet, testing the name with your search pattern. 
You can also the ? and * wildcards in your ForEachObject criteria if the name matching pattern is fairly simple. 

I have a pattern for the worksheets "TAB_WE_H3/0/1". If I can use a wildcard after TAB_WE_H, i could find all the worksheets I need. How would I add the search criteria to the foreachobject line?

 

Thanks for the help!

 

 

Link to comment

I tried this:

import vs

def callback(h):
    vs.RecalculateWS(h)
        

crit = ("N = TAB_WE_H*")
vs.ForEachObject(callback, crit)

import vs



def callback(h):
	vs.RecalculateWS(h)
		

crit = ("N = TAB_WE_H*")
vs.ForEachObject(callback, crit)

But it still does not work. I don't get an error message, but it's not updating the worksheets. Regarding the criteria, I was using the criteria builder, though I can't see the worksheet listed under Name. Somehow the worksheets don't have names that you can use.

Link to comment

@elepp,

   Unlike my fellow scriptographers, except for the simplest of cases, I spend too much time trying to get <criteria> to work cleanly, so I usually take a more blunt approach. Here is a Vectorscript that looks at the Name List, which includes worksheet names, and decides which names are Worksheets, then tests if the name matches a predefined pattern, namely, it starts with N-characters in common. I know you are working in Python, but it's late, I'm tired, so I'll let you pythonize it.

 

PROCEDURE xxx;
{ Script to recalculate all worksheets whose names start with the string in WSName. }
{ 25 Feb 2022 - R. Mullin }
CONST
	WSName = 'TAB_WE_H';
	WSType = 18;
VAR
	ObjH :Handle;
	I, J, NameCnt :Longint;
	aName :String;

BEGIN
	J := 0;
	NameCnt := NameNum;
	for I:=1 to NameCnt do begin
		aName := Index2Name(I);
		ObjH := GetObject(aName);
		if (GetTypeN(ObjH) = WSType) & (pos(WSName, aName) = 1) then begin
			RecalculateWS(ObjH);
			J := J + 1;
		end;	{ if }
	end;	{ for }
	
	Message(J, ' worksheets recalculated.');
END;
Run(xxx);

 

Raymond

 

PS - Feel free to beat on the <criteria> beast if you so choose.

 

PPS - I think the problem with using ForEachObject to test for worksheet names is that worksheets are not ON the drawing, and I think ForEachObject only tests objects that exist on Design Layers, Sheet Layers, in Viewports, and inside Symbols. @Pat Stanford, @JBenghiat, please verify.

 

  • Like 1
Link to comment
9 hours ago, MullinRJ said:

PPS - I think the problem with using ForEachObject to test for worksheet names is that worksheets are not ON the drawing, and I think ForEachObject only tests objects that exist on Design Layers, Sheet Layers, in Viewports, and inside Symbols. @Pat Stanford, @JBenghiat, please verify.

That sounds interesting. If I don't have the worksheet on the design layer, I can't see it in the criteria builder. But even if I do that my script is not running, even though I don't get an error message. But I am able to get the Index of the worksheet.

Link to comment

I'm actually not sure if FEO will only look at drawing objects. That may be the case, in which case Raymond's method might be the best. 

 

@elepp The code example won't work, regardless.

You have:

crit = ("N = TAB_WE_H*")

 

This is a tuple, containing a string. Not to be confused with:

crit = "(N = 'TAB_WE_H*')"

 

The main difference here is that the parentheses are inside the double quotes. The quotes are what define the variable as a string in python. () {} and [] will define tuples, dicts, and arrays.

 

Also, criteria are finicky about key words versus values. Non-numeric values always need to be enclosed in single quotes (which is actually much easier in python than vs). Having each keyword / value test in parentheses is also good form, though I think less important for a single selector.

Link to comment

@JBenghiat Thanks for your kind reply.

 

31 minutes ago, JBenghiat said:

 The code example won't work, regardless.

You mean my code example above will not work?

I just noticed that your reply before didn't have the parentheses around the string. I removed them and used single quotes for them. Still it's not updating my worksheet.

Somehow I feel like I have a knot in my brain. What am I missing or is the whole script wrong?

Link to comment

@MullinRJWe are all halfway right.

 

ForEachObject will find a worksheet, but it does not accept the wildcard.  Using the wildcard returns nothing. Using the full name of a worksheet does process that worksheet.

 

So two work arounds.

 

Either use something Like Raymond's brute force processing, or wrap ForEachObject in a loop that will generate the full worksheet name and then call ForEachObject using the full name. The savings of doing that is that ForEachObject will automatically find the handle and you won't have to code that step.

 

The script I used to test was:

 

Procedure Test;

Procedure Execute(Hd1:Handle);

BEGIN
	SetWSCellFormulaN(Hd1, 1,1,1,1, 'Updated');
End;


BEGIN
	ForEachObject(Execute, ((N='TAB_WE_H*')));
End;

Run(Test);

 

 

 

  • Like 1
Link to comment

@elepp What @JBenghiat is saying is that your script did not define a string which is what ForEachObject requires.

 

Having the parentheses on the outside makes the variable type a tuple instead of a string.

 

The second version Joshua gave you should work, except as I showed in the post above the wildcard does not work with the N= criteria and ForEachObject. At least not with worksheets.

 

crit = "(N = 'TAB_WE_H*')"

 

The double quotes on the outside force it to be a string which is what ForEachObject expects.

 

The parentheses on the inside define it as a criteria to VW.

 

The single quotes define what is between them as a VW string.

 

Change what is inside the single quotes to the exact name of one of the worksheets and it should run and recalc.

  • Like 1
Link to comment
  • 2 weeks later...

I have found with worksheets that it's really handy to use BuildResourceList to build a quick and dirty list of all worksheets in a drawing (since ForEachObject with type set to 18 will only pick up worksheets placed on a layer), which can then be easily processed with a for loop.  If you pass in listID, numItems = vs.BuildResourceList(18, 0, ''), you can then use vs.GetNameFromResourceList to pull the worksheet name and vs.GetResourceFromList to pull the handle needed for vs.RecalculateWS.

 

I'm not as well versed in Python as I am in Vectorscript, but I've attached an example below.  It should be pretty easy to translate over to Python.

 

PROCEDURE WorksheetTest;

VAR

	listID,numItems:LONGINT;
	numRecalc:INTEGER;
	
FUNCTION ProcessList(ID,num:LONGINT) : INTEGER;

{Goes through list of items and if the name contains target string, the worksheet is recalculated}

	CONST

		kTargetString = 'TAB_WE_H';
		
	VAR
	
		i,counter:INTEGER;
		testStr:STRING;

	BEGIN
		counter:=0;
		
		FOR i:=1 TO num DO
			BEGIN
				testStr:=GetNameFromResourceList(ID,i);
				IF(Pos(kTargetString,testStr)=1) THEN
					BEGIN
						RecalculateWS(GetResourceFromList(ID,i));
						counter:=counter+1;
					END;
			END;
		
		ProcessList:=counter;
	END;

BEGIN
	listID:=BuildResourceList(18,0,'',numItems);
	numRecalc:=ProcessList(listID,numItems);

	AlrtDialog(Concat(Num2Str(0,numRecalc),' worksheets recalculated.'));
   	ReDrawAll;
END;

Run(WorksheetTest);

 

Best part is that it completely bypasses the need of having criteria, letting you forgo using the criteria builder.  Keep in mind that it uses the Pos function to determine if the worksheet name starts with the target string, but this has more to do with having to kludge something together with Vectorscript/Pascal, I'm sure there are more elegant string processing solutions built into Python.  Also keep in mind that you will need a ReDrawAll call to reset the recalculated worksheets, otherwise any worksheets placed on the active layer may not show the updated information.

 

Essentially, the code

  1. Builds a list of all worksheets found in the drawing and gets a count of the number of worksheets
  2. Uses a for loop to pull the name of each worksheet from the list one at a time
  3. Uses Pos to determine if the name of the worksheet contains the target string (TAB_WE_H in the example above) and that it's at the front of the name
  4. If the worksheet name contains the target, the handle is pulled using GetResourceFromList and used in a RecalculateWS call.  A counter is also increased.
  5. Once complete, the counter value is returned with an AlrtDialog call to inform the user how many worksheets were recalculated.
  6. ReDrawAll is used to force any worksheets on the active layer to redraw, reflecting any possible changes from the recalculation.

 

EDIT:

Looking back through this thread, my solution is almost exactly the same as @MullinRJ's, all the way down to the same alert message at the end.   I didn't initially read his post, I wrote this primarily as a way to avoid using the ForEachObject call since it won't see the actual worksheet definitions.  The only major difference is that my solution only processes on the list of worksheets rather than every named object in the drawing.

Edited by Jesse Cogswell
Noticed that a previous post contained an identical solution.
  • Like 3
Link to comment

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...