tavbut Posted February 17, 2018 Share Posted February 17, 2018 Hello, I'm trying to write a python code to run inside a worksheet's(WS) cell that works as the VLOOKUP function available in EXCEL or GOOGLESHEETS. I'm a python beginner and I have the basic idea of how to get the basic code structure going... but I haven't been able to write a simple "Hello World" into a Vectorworks(VW) Worksheet's cell . I'll try to explain what the VLOOKUP function does: it is about cell referencing from one WS to another. I can do that in VW by typing something like this inside a WS's cell: ='referenceWS':cellAddress but instead of having cellAddress be "collumName,rowName" it would be "stringInCollumA,stringInRow1". Ok, I'll try and explain it better with an image, this is getting me confused already. This image shows WS-2 What I would like to do is type in any cell of WS-1 something like this: =RUNSCRIPT('vLookup.py', 'concreteCollum', 'VOLUME') ...and get value 8. So the script would have to loop through all the rows in collum 'A' until it finds the string I pass as first argument, store that 'rowLocation', then loop through all the collums in row '1' and store that collumLocation and then return the content of the cellAddres = (collumLocation, rowLocation). I have several steps missing in that description of the code... but in my head that is basically what it does :P. I want to do this so I can add to or change or re-arrange WS-2 and still get the right reference in WS-1. Anyone can point me in any direction with this crusade? Quote Link to comment
Pat Stanford Posted February 17, 2018 Share Posted February 17, 2018 The pseudo code as you describe it is correct. It just becomes a couple of While Not loops or Repeat Until loops to compare the passed parameters to the read data. If you are going to to the trouble of writing it, I would also pass the name of the worksheet to lookup in and possible the range (row and column) to compare. Also note that as of 2018 SP3, there is no was to get a reference to a database subrow, so this is only possible in actual spreadsheet rows, not in a database. Quote Link to comment
Sam Jones Posted February 17, 2018 Share Posted February 17, 2018 Should you need to reference the database, conceivably, you could just access the database in the VS command. In the screen shot you show, you don't seem to need to access a database subrow. Quote Link to comment
tavbut Posted February 19, 2018 Author Share Posted February 19, 2018 Hey guys, thanks for the quick reply! I was testing a bit with the basic vs.functions I think I'll have to use to get the basic code running(without the searching and stuff) and ran into a particular issue. I used VS:GetTopVisibleWS to get a handle of the active WS, VS:GetWSSelection to get handles of the current cell's address, and then VS:SetWSCellFormula to write '=2+4' in it. When I run it like this =RUNSCRIPT('vLookup') I get this error: #OPCODE? written in the cell, but in the formula bar the =2+4 appears. How could I work around this? I was hoping not to lose the formula so if I change the reference value it would update when I hit recalculate. I can only think of having a duplicate WS so I run the codes in 1 WS and write the value in the other one but that workflow would duplicate the amount of WS I was planning on managing :(. I am currently doing this by exporting information from VW to GooGLE SHEETS and working my numbers there but I was really hoping it could be done inside VW. It seems to me that only if vLookup is a native VW function It will work. Am I right? Is it possible to get the vLookup function working as a Plugin? Quote Link to comment
Pat Stanford Posted February 20, 2018 Share Posted February 20, 2018 I am not sure you can use RunScript to set a formula into the cell. The RunScript IS the formula. Do all the calculations in the script and then use WSScript_SetResXXX functions to return the proper value. I suppose RunScript could be used to set a formula in a DIFFERENT cell, but I don't think it will work to change the formula in the cell the script is in. I don't think there is any way to extend the functions available in the worksheet. If RunScript is not enough you need to submit an Enhancement Request and hope the VW gets around to adding it. Quote Link to comment
tavbut Posted February 21, 2018 Author Share Posted February 21, 2018 I understand, I will finish the code and upload it to see if it can be added to VW. Thank you (Pat and Sam) for your replies! Quote Link to comment
Peter Vandewalle Posted April 22, 2018 Share Posted April 22, 2018 I recently created my own vlookup function. See attached file. Check the remarks at the beginning for the way to use it. WSVLookup.py.zip Quote Link to comment
Pat Stanford Posted April 22, 2018 Share Posted April 22, 2018 Nice work. Does your version work in a database row or only in a worksheet row? For those not a deeply into worksheet and scripts, to make this work, you would need to make a subfolder in the [Application or Workgroup or User] folder in the Libraries/Defaults/Reports_Schedules folder and store the WSVLookup.py file in that folder. In the worksheet (as it says at the top of the script) you would use script by using a RunScript function. The 4 parameters that need to be passed are: A cell containing the value to be searched for. The range in which to search. The offset from the found location to return The type of object you are searching for: 0=String, 1=Integer, 2=real, 3=image. Quote Link to comment
Peter Vandewalle Posted April 23, 2018 Share Posted April 23, 2018 I only used it in a database row. But I think it should also work in a spreadsheet row. Thanks for the manual! Quote Link to comment
Pat Stanford Posted April 23, 2018 Share Posted April 23, 2018 I have not actually tried the script, but was the range you were pulling data FROM in a database? Or was the FROM data stored in spreadsheet cells? I ask because I have not been able to find a way to access the row that a database subcell is in. I have a request in for a function that will return the cell address that a worksheet script is running from. I have a number of scripts that could be very interesting if only I knew WHERE the script was running. Pat Quote Link to comment
Peter Vandewalle Posted April 23, 2018 Share Posted April 23, 2018 The from data is in spreadsheet cells. I didn't need it from database rows... Sounds interesting though. Quote Link to comment
tavbut Posted April 23, 2018 Author Share Posted April 23, 2018 Thanks for sharing Peter!, I'll try this soon. 1 Quote Link to comment
RubenH Posted October 21, 2018 Share Posted October 21, 2018 Hello All! I"m trying to use the fabulous script shared by @Peter Vandewalle, but get the error in the image. I'm using Vectorworks 2019. Any Clues? Thanks, Rubén Quote Link to comment
Peter Vandewalle Posted October 23, 2018 Share Posted October 23, 2018 Sorry, I forgot to upload the latest version of the VLookup script. I'm attaching it now. The beginning of the script contains the example line to insert in a worksheet: =RUNSCRIPT(120; 'Rapporten Architectuur/WSVlookup.py'; A1; 'A1..A5'; 2) You can change the formula to: =RUNSCRIPT(120; 'WSVlookup.py'; A1; 'A1..A5'; 2) Where: 120: path to script folder (Library:Defaults:Reports_Schedules) 'WSVLookup.py': script file A1: Reference to the lookup cell 'A1..A5': Reference to the lookup range, you can reference to another worksheet using 'sheetname:A1..A5' 2: Column number to return WSVLookup.py.zip 1 Quote Link to comment
RubenH Posted October 23, 2018 Share Posted October 23, 2018 Thanks a lot Peter! 1 Quote Link to comment
tavbut Posted April 9, 2020 Author Share Posted April 9, 2020 Sorry for the slightly late reply, but, thanks @Peter Vandewalle ! 2 Quote Link to comment
KingChaos Posted August 18, 2023 Share Posted August 18, 2023 (edited) hi there, i try to read out cell data out of a spreadsheet with the data stamp is not working at all. is it general possible in vwx 2022 to use such a lookup for my stamp, which finds a value in a spreadsheet range and return same rows (subrows in this case) different columns value? This may help me for my problem, if i need a script which can use vlookup or else. Edited August 18, 2023 by KingChaos Quote Link to comment
KingChaos Posted August 18, 2023 Share Posted August 18, 2023 (edited) deleted Edited August 18, 2023 by KingChaos 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.