Jump to content

vLookup function


tavbut

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?

 

Link to comment

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.

Link to comment

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?

 

Link to comment

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.

Link to comment
  • 2 months later...

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.

 

 

Link to comment

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

 

Link to comment
  • 5 months later...

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

  • Like 1
Link to comment
  • 1 year later...
  • 3 years later...

hi there,

 

i try to read out cell data out of a spreadsheet with the data stamp

image.png.6f09bcede803aaf28c0e9340f4be4401.png

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 by KingChaos
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...