elepp Posted February 11, 2022 Share Posted February 11, 2022 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! 1 Quote Link to comment
Pat Stanford Posted February 11, 2022 Share Posted February 11, 2022 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 1 Quote Link to comment
elepp Posted February 12, 2022 Author Share Posted February 12, 2022 Thanks for the first pointers. I will see how far I get and update my progress here. 🙂 Quote Link to comment
JBenghiat Posted February 15, 2022 Share Posted February 15, 2022 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. 1 Quote Link to comment
matteoluigi Posted February 17, 2022 Share Posted February 17, 2022 (edited) 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 February 17, 2022 by matteoluigi 1 Quote Link to comment
matteoluigi Posted February 17, 2022 Share Posted February 17, 2022 # 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) 1 Quote Link to comment
elepp Posted February 25, 2022 Author Share Posted February 25, 2022 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! Quote Link to comment
elepp Posted February 25, 2022 Author Share Posted February 25, 2022 I tried this: import vs crit = vs.GetName("TAB_WE_H*") def callback(h): vs.RecalculateWS(h) vs.ForEachObject(callback, crit) 1 Quote Link to comment
elepp Posted February 25, 2022 Author Share Posted February 25, 2022 I tried this: import vs crit = vs.GetName("TAB_WE_H*") def callback(h): vs.RecalculateWS(h) vs.ForEachObject(callback, crit) I does not give me an error message but it's also not working. What am I doing wrong? Quote Link to comment
matteoluigi Posted February 25, 2022 Share Posted February 25, 2022 Try to move the line „crit = vs.GetName("TAB_WE_H*")“ above the foreach-command. that should do it imho Quote Link to comment
JBenghiat Posted February 25, 2022 Share Posted February 25, 2022 The criteria builder in the script editor will help you. The criteria needs to be a string. crit = “N=‘TAB_WE_H*’” 1 Quote Link to comment
elepp Posted February 26, 2022 Author Share Posted February 26, 2022 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. Quote Link to comment
MullinRJ Posted February 26, 2022 Share Posted February 26, 2022 @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. 1 Quote Link to comment
elepp Posted February 26, 2022 Author Share Posted February 26, 2022 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. Quote Link to comment
JBenghiat Posted February 26, 2022 Share Posted February 26, 2022 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. Quote Link to comment
elepp Posted February 26, 2022 Author Share Posted February 26, 2022 @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? Quote Link to comment
Pat Stanford Posted February 26, 2022 Share Posted February 26, 2022 @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); 1 Quote Link to comment
Pat Stanford Posted February 26, 2022 Share Posted February 26, 2022 @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. 1 Quote Link to comment
elepp Posted February 27, 2022 Author Share Posted February 27, 2022 (edited) Thanks @Pat Stanford, @JBenghiat and @MullinRJ for your patient and your explenation. With one specific worksheet it's working now. I will start on the looping now. If I have questions, hope you don't mind me coming back here. 🙂 Edited February 27, 2022 by elepp 1 Quote Link to comment
Jesse Cogswell Posted March 9, 2022 Share Posted March 9, 2022 (edited) 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 Builds a list of all worksheets found in the drawing and gets a count of the number of worksheets Uses a for loop to pull the name of each worksheet from the list one at a time 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 If the worksheet name contains the target, the handle is pulled using GetResourceFromList and used in a RecalculateWS call. A counter is also increased. Once complete, the counter value is returned with an AlrtDialog call to inform the user how many worksheets were recalculated. 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 March 9, 2022 by Jesse Cogswell Noticed that a previous post contained an identical solution. 3 Quote Link to comment
Recommended Posts
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.