Jump to content
tavbut

vLookup function

Recommended Posts

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 :S.

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.

WS-2.thumb.png.e216cb50c19a03a786c4ea9c5bfb0e42.png

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?

 

Share this post


Link to post

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.

Share this post


Link to post

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.

 

Share this post


Link to post

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?

 

Share this post


Link to post

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.

Share this post


Link to post

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!

Share this post


Link to post

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.

 

 

Share this post


Link to post

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

 

Share this post


Link to post

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

 

 

Screen Shot 2018-10-20 at 9.47.48 PM.png

Share this post


Link to post

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

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

 

7150 Riverwood Drive, Columbia, Maryland 21046, USA   |   Contact Us:   410-290-5114

 

© 2018 Vectorworks, Inc. All Rights Reserved. Vectorworks, Inc. is part of the Nemetschek Group.

×