| Author |
 Topic  |
|
|
cpeterbeng
Germany
Posts |
 Posted - 08/06/2012 : 12:23:47 PM
|
Origin Ver. 8.6.0 32 bit; WinXP latest build
Hi,
Using a formula in the set column values, is it possible to use a string variable to insert a workbook name into a range variable formula? I want to use a list of workbook names included as a text column in the current workbook. This would allow me to gather statistics from a large number of workbooks.
Something like:
max(r1)
Before Formula Scripts:
string workbookname$ = col(workbookname);
range r1 = [workbookname$]sheet!Col(Column);
C. Peter Bengtson
Interdisziplinäres Zentrum für Neurowissenschaften (IZN)
Universität Heidelberg +49-(0)6221-546484 (lab)
Im Neuenheimer Feld 364 +49-(0)6221-546700 (fax)
69120 Heidelberg, GERMANY |
|
|
Penn
China
644 Posts |
Posted - 08/06/2012 : 8:57:42 PM
|
Hi,
Yes, of course, you can use the string variable, such as:
Before Formula Scripts:
string workbookname$ = book1; // workbook name is book1 here
range r1 = [workbookname$]sheet1!col(1); // use the string variable
Penn |
 |
|
|
cpeterbeng
Germany
Posts |
Posted - 08/10/2012 : 03:57:07 AM
|
The value of your string "workbookname$" remains fixed as "book1". I am trying to assign the entries in a column to a string variable. Something like:
string workbookname$ = col(X); // column(X) contains a text list of the workbook names.
Is it possible to feed text from a column into the value of a string variable somehow?
thanks, Peter |
|
|
|
Penn
China
644 Posts |
Posted - 08/10/2012 : 04:55:48 AM
|
Hi Peter,
If I got your meaning right, the book name is listed as the following image shows, right?
If so, please try the following script.
range ra = [book1]sheet1!col(A); // column A
range rwks = [book1]sheet1!; // sheet1
for(ii = 1; ii <= rwks.maxrows; ii++) // loop to get all the book names
{
string strbookname$ = ra[ii]$; // get the iith book name
strbookname$ = ; // print the book name
}
Penn |
|
|
|
cpeterbeng
Germany
Posts |
Posted - 08/13/2012 : 10:05:43 AM
|
Hi Penn,
Your script can generate a list of booknames. I want to use the booknames in a formula. For example in column fxColA of book1 I would like to calculate the maximum value in each of all the Col(A) in books 2 to 5 and have them listed in book1 column fxColA in each row next to the corresponding book name which is listed in column A of book1.
I know I could generate a new statistics output sheet with max values but this is just a starting point example. I want to use more complex equations than just the maximum function and I want to generate these results according to a column of book names.
thanks,
Peter
C. Peter Bengtson
Interdisziplinäres Zentrum für Neurowissenschaften (IZN)
Universität Heidelberg +49-(0)6221-546484 (lab)
Im Neuenheimer Feld 364 +49-(0)6221-546700 (fax)
69120 Heidelberg, GERMANY |
|
|
|
Penn
China
644 Posts |
Posted - 08/13/2012 : 9:11:49 PM
|
Hi Peter,
I think you can merge the two example scripts I provided in my two replies. For example:
range rBook1ColA = [book1]sheet1!col(A); // column A in book1 sheet1
range rBook1fxColA = [book1]sheet1!col(fxColA); // column fxColA in book1 sheet1
range rBook1Wks = [book1]sheet1!; // sheet1 in book1
for(ii = 1; ii <= rBook1Wks.maxRows; ii++) // loop to get book names for calculation
{
string strBookname$ = rBook1ColA[ii]$; // get the book name
range rr = [strBookname$]sheet1!col(A); // define the corresponding column A
rBook1fxColA[ii] = max(rr); // calculate the maximum and put to column fxColA
}
Penn |
|
|
|
cpeterbeng
Germany
Posts |
Posted - 08/14/2012 : 11:29:56 AM
|
Problem solved. Thanks alot for your help.
Peter |
|
|
|
cpeterbeng
Germany
Posts |
Posted - 04/15/2016 : 05:23:55 AM
|
Hi,
I would like to expand on this to generate a sheet of column labels from the workbooks listed in a column. I tried the following script but it doesn't like the string variable. (string expression error! BOOK:failed to add variable to local stack!)
What am I doing wrong?
Peter
// copy column long names from columns 1 to 50 from Sheet1
// in each of 35 books whose names are listed in col("booknames")
// in the active sheet
loop (j,1,35)
{
string book$ = col("booknames")[j]$;
loop (i,1,50)
{
range r1 = [book$]Sheet1!wcol(i); // Point to the source column
range r2 = wcol(i); // Point to the target column in the active sheet
r2[j] = r1[L]; // place the column long name into the target sheet
}
} |
|
|
|
SeanMao
China
288 Posts |
Posted - 04/19/2016 : 06:03:52 AM
|
Hi Peter,
Variable "i" and "j" are Origin reserved built-in variables, i denotes row index and j denotes column index. You should at least try to avoid using those two names as variable names.
r1[L]=>r1[L]$ // it is a string
Regards!
Sean
OriginLab Tech.
|
|
|
|
patricktenore179
USA
1 Posts |
Posted - 05/11/2016 : 11:48:28 AM
|
What kind of home do you want and need? This is a crucial step in the process of buying a property. Select your ideal location, including suburban, downtown or rural. Then, what kind of home are you looking to buy (townhouse, apartment, condo or co-op)? Upon determining it, you should also set a price range, determine the size of the house and select the architectural built and style of your choice.
http://patrick-tenore-real-estate-group.blogspot.com
[url=http://patrick-tenore-real-estate-group.blogspot.com]Patrick Tenore[/url] |
|
|
| |
Topic |
|
using string variables within workbook addresses