| Author |
 Topic  |
|
|
nexusnex
Germany
10 Posts |
 Posted - 10/30/2019 : 3:34:06 PM
|
Hello guys,
I have been lurking through the forum but couldn't find a solution so I had to create this post, sorry for this. Also, I am not good at Labtalk so I might not express myself correctly or use the right words, so please bear with me.
My situation and problem and what I want to do:
I have a ton of worksheets in one workbook that are ordered in number. These worksheets have one thing in common: 5 columns and 20.000 rows.
What I want to do is:
1. Create a separate workbook
2. Select cells(17432,5) of N worksheets and add their value to the new workbook at cell(N,1)
For example:
workbook cell(1,1) = worksheet number 1 cell(17432,5)
workbook cell(2,1) = worksheet number 2 cell(17432,5)
workbook cell(3,1) = worksheet number 3 cell(17432,5)
etc.
So I have the data value of each worksheet ordered in the new workbook. My problem is how can I create a new workbook and loop (that means the page needs to be active right??) through the worksheets of another workbook and add their data value of each cell(17532,5) to the new workbook (which is then inactive??)
I am really frustrated because I really dont know how I can do this... hope you can help me and thank you so much in advance.
Best regards! |
|
|
couturier
France
291 Posts |
Posted - 10/31/2019 : 04:07:25 AM
|
Hi,
open Script Window (Menu Window/Script Window) and paste the following script:
range wkb=[%H]; // active workbook, in which you have all your data
dataset ds; // temporary dataset
for (int ii=1; ii<=wkb.nlayers; ii++) { // loop through worksheets
range rcol=[%H]$(ii)!5; // 5th col on wks number ii
ds[ii]=rcol[17432]; // assign cell 17432 to row ii of temp dataset
}
newbook; // new workbook
range rcol=1; // 1st col in new workbook
rcol=ds; // put temp dataset into col
Select all the lines in Script Window and press Enter |
 |
|
|
nexusnex
Germany
10 Posts |
Posted - 10/31/2019 : 08:04:39 AM
|
That works wonderfully! Thank you so so much!  |
|
|
|
nexusnex
Germany
10 Posts |
Posted - 11/01/2019 : 07:01:11 AM
|
quote:
Originally posted by couturier
Hi,
open Script Window (Menu Window/Script Window) and paste the following script:
range wkb=[%H]; // active workbook, in which you have all your data
dataset ds; // temporary dataset
for (int ii=1; ii<=wkb.nlayers; ii++) { // loop through worksheets
range rcol=[%H]$(ii)!5; // 5th col on wks number ii
ds[ii]=rcol[17432]; // assign cell 17432 to row ii of temp dataset
}
newbook; // new workbook
range rcol=1; // 1st col in new workbook
rcol=ds; // put temp dataset into col
Select all the lines in Script Window and press Enter
Hi,
I have another question. If I want to add something to the first example and want to do something like this
workbook cell(1,1) = worksheet number 1 cell(17432,5)
workbook cell(1,2) = worksheet number 1 cell(12455,5)
workbook cell(2,1) = worksheet number 2 cell(17432,5)
workbook cell(2,2) = worksheet number 2 cell(12455,5)
that means I want to add the data value from another cell of the worksheet to the second column, how would I go about this using your script?
Something like this?
range wkb=[%H]; // active workbook, in which you have all your data
dataset ds; // temporary dataset
for (int ii=1; ii<=wkb.nlayers; ii++) { // loop through worksheets
range rcol=[%H]$(ii)!5; // 5th col on wks number ii
ds[ii]=rcol[17432]; // assign cell 17432 to row ii of temp dataset
ds[ii]=rcol[12455];
}
newbook; // new workbook
range rcol=1; // 2nd col in new workbook
rcol=ds; // put temp dataset into col
range rcol=2; |
|
|
|
couturier
France
291 Posts |
Posted - 11/01/2019 : 3:59:42 PM
|
string strBk$=%H; // Assuming active workbook has all data, stores book name into string variable
NewBook;
range wkb=[%(strBk$)];
range rC1=1, rC2=2; // 1st and 2nd col of newly created book
for (int ii=1; ii<=wkb.nlayers; ii++) { // loop through worksheets
range rcol=[%(strBk$)]$(ii)!5; // 5th col on wks number ii
rC1[ii]=rcol[17432];
rC2[ii]=rcol[12455];
} |
|
|
| |
Topic |
|
|
|
Add cell value of one wks to wb in subsequent rows