| Author |
 Topic  |
|
|
Peter9312
12 Posts |
 Posted - 07/29/2020 : 03:16:39 AM
|
Hi,
I have a working script for renaming an existing column and the creation of a new column at a specific place in the current workbook:
//Turn off "Spreadsheet Cell Notation" (SCN)
page.xlcolname = 0;
// Rename column 5 "start" to startraw
wks.col5.name$ = startraw;
// Set column 6 to be the current column
wks.col = 6;
// Insert 1 column before column 6, with the specified column name "start"
wks.insert(start);
I need this now for all workbooks in my project folder except of a workbook called "Dataraw" but it seems to be tricky to use the loop in labtalk. Can somebody give be a hint how to do this? I tried it with the documentation but failed.
Wishes
|
|
|
Chris D
428 Posts |
Posted - 07/29/2020 : 09:43:33 AM
|
The following modification uses the document command to loop over all worksheets in the project (except those in Dataraw book) to do your work.
See: https://www.originlab.com/doc/LabTalk/ref/Document-cmd#-e_object_.7Bscript.7D.3B_Execute_the_given_script_for_all_objects
I hope this helps.
doc -e LB {
// Check if matrix book and skip.
if(exist(%H,2)==0)
continue;
// Ignore Dataraw book.
if ( %H == "Dataraw")
continue;
//Turn off "Spreadsheet Cell Notation" (SCN).
page.xlcolname = 0;
// Rename column 5 "start" to startraw.
wks.col5.name$ = startraw;
// Set column 6 to be the current column.
wks.col = 6;
// Insert 1 column before column 6, with the specified column name "start".
wks.insert(start);
}
Thanks,
Chris Drozdowski
Originlab Technical Support
|
 |
|
|
Peter9312
12 Posts |
Posted - 07/30/2020 : 02:19:08 AM
|
Hi Chris,
thanks for the reply. I tried this "doc -e LB" command before and was not able to get it working over all workbooks. Then, I tried your script but it is not working correctly either.
When I use your script, I get what I need in the currently opened workbook but all other workbooks and worksheets in my project folder are completely not affected. The 2 columns are only available in the current book but not in the others.
I do not understand why, can you help me?
Wishes,
Peter |
|
|
|
Chris D
428 Posts |
Posted - 07/30/2020 : 3:12:31 PM
|
And all other worksheets in the project have the same number of columns as the current one?
Are the essentially structural duplicates of the current one?
Thanks,
Chris Drozdowski
Originlab Technical Support
|
|
|
|
Peter9312
12 Posts |
Posted - 07/31/2020 : 03:06:24 AM
|
Yes, they have the exact same number of columns. The workbooks have differnt namens but the structure of the sheets are exacly the same.
It's like you have duplicates of one workbook but the data in the rows/columns are different as well as the long and short names. |
Edited by - Peter9312 on 07/31/2020 05:03:21 AM |
|
|
|
Chris D
428 Posts |
Posted - 07/31/2020 : 08:51:37 AM
|
What version of Origin are you using because I set up a scenario same as the one you describe and the script I provided worked properly. I am using Origin 2020b but the script should work in older versions but perhaps not really old versions.
Thanks,
Chris Drozdowski
Originlab Technical Support
|
|
|
|
Peter9312
12 Posts |
Posted - 07/31/2020 : 09:02:50 AM
|
I'm using origin 2019, so it's not that old compared to your version.
Wishes |
|
|
|
Chris D
428 Posts |
Posted - 07/31/2020 : 2:12:34 PM
|
I agree but honestly I am at a loss as to what is going wrong. The only thing I can think to do is to inject this
sec -p 0.1;
right after
page.xlcolname = 0;
It gives a slight delay after turning off SCN.
I have no evidence that will work but, based on experience, sometimes a little strategic delay might solve the problem.
Thanks,
Chris Drozdowski
Originlab Technical Support
|
|
|
|
Peter9312
12 Posts |
Posted - 09/06/2020 : 07:01:37 AM
|
Hi,
sorry for the long delay, corona and plans are not a good combination.
Thanks for your help.
I tried your work around, but doesn't work either. It still doesn't loop over all books it just stays in the current one and does what I want.
I get a error now without changing anything:
Error:WKS.COL5.NAME
Do you know something about it? Why is it not going from book to book? |
|
|
|
cpyang
USA
1406 Posts |
Posted - 09/06/2020 : 10:44:38 AM
|
Yes, error will stop the script.
sec -p 0.1 is not needed, key is to fix the script error.
From Script Window, type
ECHO=1
and it will print the line that generated the error.
Also, if there is only one sheet in each book and you really meant to loop over books, use "doc -e W' instead, which will loop using the creation order of the books, while "doc -e LB" will start from the active sheet, and if there is an error, it will not proceed to other sheets.
CP
|
|
|
|
Peter9312
12 Posts |
Posted - 09/07/2020 : 09:13:06 AM
|
Hi CP,
thanks for reply.
First, I get nothing with ECHO=1 to identify the reason for the error. Interesting, sometimes the error comes, sometimes not.
When I change the script and use "doc -e W" I get: #Command Error!
It doesn't matter if I have "sec -p 0.1" or not.
The script
doc -e LB {
// Check if matrix book and skip.
if(exist(%H,2)==0)
continue;
// Ignore Dataraw book.
if ( %H == "Dataraw")
continue;
//Turn off "Spreadsheet Cell Notation" (SCN).
page.xlcolname = 0;
// Rename column 5 "start" to startraw.
wks.col5.name$ = startraw;
// Set column 6 to be the current column.
wks.col = 6;
// Insert 1 column before column 6, with the specified column name "start".
wks.insert(start);
}
does work and do loop over all sheets in the activated/current book but will not loop over all other books/sheets in the project after finishing the current book. I need the script for all books in the project excluding the dataraw book.
So, it should check if the activated book ist data raw, if so then proceed. If the activated book is not dataraw, then do the stuff with renameing and adding columns and proceed. Proceed means to go to the next book in the project and check if it's data raw or not and so on....
Can you help me?
wishes,
P
|
|
|
|
cpyang
USA
1406 Posts |
Posted - 09/08/2020 : 03:44:55 AM
|
Can you show the whole script with doc -e W ?
CP
|
|
|
|
Peter9312
12 Posts |
Posted - 09/08/2020 : 04:46:46 AM
|
I just changed "doc -e LB" to "doc -e W":
doc -e W {
// Check if matrix book and skip.
if(exist(%H,2)==0)
continue;
// Ignore Dataraw book.
if ( %H == "Dataraw")
continue;
//Turn off "Spreadsheet Cell Notation" (SCN).
page.xlcolname = 0;
// Rename column 5 "start" to startraw.
wks.col5.name$ = startraw;
// Set column 6 to be the current column.
wks.col = 6;
// Insert 1 column before column 6, with the specified column name "start".
wks.insert(start);
} |
|
|
|
cpyang
USA
1406 Posts |
Posted - 09/08/2020 : 1:23:26 PM
|
I have no idea why so is it possible for you to send the project to me so I can check?
CP
|
|
|
|
Peter9312
12 Posts |
Posted - 09/09/2020 : 03:47:33 AM
|
| Thanks, I send you the file. |
|
|
|
cpyang
USA
1406 Posts |
Posted - 09/09/2020 : 3:23:44 PM
|
I put in echo=1 and see the error.
1. open script window
2. type echo=1 and enter
then run your code, and you see error coming from wks.col5.name line, since there is a book "Book1" in another folder that does not have col5.
So who did you use echo=1 before?
CP
|
|
|
|
Peter9312
12 Posts |
Posted - 09/10/2020 : 12:34:30 PM
|
Oh, I thought I need to run the code first and use then echo=1
ok, now without this book1 everything is working correctly. Thanks for all. Is there a chance to just skip those books with less collumns? |
|
|
|
cpyang
USA
1406 Posts |
|
|
Peter9312
12 Posts |
Posted - 09/11/2020 : 03:20:24 AM
|
| ok, thanks. It's now working. |
|
|
| |
Topic |
|
Loop for creation of columns in all workbooks