| T O P I C R E V I E W |
| Clairekun |
Posted - 07/08/2021 : 11:47:01 AM
Origin Ver. and Service Release (Select Help-->About Origin): 2018b
Operating System: Windows 10
Hello,
I hope I can explain this clearly enough. I need to create a workbook to append the first worksheet of a selection of workbooks to Sheet 1 and Sheet 2.
I have created the target workbook and sheets, and stored the different source worksheet names in a string array to append them to the new sheet based on worksheet name.
//New workbook named Samples with 2 worsheets named Atomic and Oxides
newbook name:="Samples" option:=lsname sheet:=0;
win -a "Samples";
string SmpBk$ = page.name$;
newsheet book:="Samples" name:="Atomic" outname:="Atomic" active:=1;
string AtWks$ = wks.name$;
newsheet book:="Samples" name:="Oxides" outname:="Oxides" active:=1;
string OxWks$ = wks.name$;
//String arrays to store the worksheet names to be appended to Atomic and Oxides
stringArray AllAtWks;
stringArray AllOxWks;
//Loop through workbooks to retrieve the name of their first worksheet
doc -e W
{
string AllBk$ = page.name$;
string AllWks$ = layer1.name$;
if (Left(AllBk$, 3)$ == "T1A") //First 3 letters of the workbooks containing Atomic info
{
AllAtWks.add(%(AllWks$)); //Store worksheet names in the array
}
if (Left(AllBk$, 3)$ == "T1O") //First 3 letters of the workbooks containing Oxides info
{
AllOxWks.add(%(AllWks$)); //Store worksheet names in the array
}
else
continue;
}
I checked all names were correctly retrieved and stored in the arrays with the script:
loop(ii,1,AllAtWks.GetSize())
{
string strAt$=AllAtWks.GetAt(ii)$;
strAt$=;
}
loop(ii,1,AllOxWks.GetSize())
{
string strOx$=AllOxWks.GetAt(ii)$;
strOx$=;
}
I am having problems trying to integrate the string array with wAppend function since I have a very basic knowledge of LabTalk. As an example:
wAppend irng:=[%(AllBk$)]%(strOx$)! method:=row ow:=[%(SmpBk$)]%(OxWks$)!
If I put this inside the loop (but outside the Doc -e W funciton), it won't recognize AllBk$.
If I put both the loop and wAppend function inside Doc -e W, I get the following error:
quote:
Failed to resolve range string, VarName = irng, VarValue = [T1O_01]01_all!
Where T1O_01 is one of my workbook names, and 01_all is its first worksheet.
I am certain this is because I am not understanding some concept, because it looks like a pretty straightforward thing to do.
Could you please help me understand what I should do? |
| 6 L A T E S T R E P L I E S (Newest First) |
| YimingChen |
Posted - 07/08/2021 : 3:45:40 PM
Sorry that I didn't clean the code. You are right that you don't need stringArray but a range string as input to wAppend.
James |
| Clairekun |
Posted - 07/08/2021 : 2:50:42 PM
It works! Thank you!
I was trying to guess how I could include the workbook name when I saw your post, which did it.
If I understood it correctly, what this does is:
- Create a parentheses (
- Add the workbook name and first worksheet, including a comma, every time it reads a different workbook
- Close everything with a parentheses )
- Use that whole ( , , , ,....) string as wAppend range
Please correct me if I'm wrong, but I would no longer need to define any string array nor the worksheet name, since it is always the first, right?
Thank you so much! |
| YimingChen |
Posted - 07/08/2021 : 2:26:58 PM
There might be other ways. Here I create the string with range of worksheets which is then used as input to wAppend.
James |
| YimingChen |
Posted - 07/08/2021 : 2:22:59 PM
Can you try the script below:
//New workbook named Samples with 2 worsheets named Atomic and Oxides
newbook name:="Samples" option:=lsname sheet:=0;
win -a "Samples";
string SmpBk$ = page.name$;
newsheet book:="Samples" name:="Atomic" outname:="Atomic" active:=1;
string AtWks$ = wks.name$;
newsheet book:="Samples" name:="Oxides" outname:="Oxides" active:=1;
string OxWks$ = wks.name$;
//String arrays to store the worksheet names to be appended to Atomic and Oxides
stringArray AllAtWks;
stringArray AllOxWks;
//Loop through workbooks to retrieve the name of their first worksheet
string strAllAtWks = "(";
string strAllOxWks = "(";
doc -e W
{
string AllBk$ = page.name$;
string AllWks$ = layer1.name$;
if (Left(AllBk$, 3)$ == "T1A") //First 3 letters of the workbooks containing Atomic info
{
strAllAtWks$ = strAllAtWks$ + "[%(AllBk$)]1!,";
}
if (Left(AllBk$, 3)$ == "T1O") //First 3 letters of the workbooks containing Oxides info
{
strAllOxWks$ = strAllOxWks$ + "[%(AllBk$)]1!,";
}
else
continue;
}
strAllAtWks$=replace(strAllAtWks$, len(strAllAtWks$), 1, ")")$;
strAllOxWks$=replace(strAllOxWks$, len(strAllOxWks$), 1, ")")$;
strAllAtWks$=;
strAllOxWks$=;
wAppend irng:=%(strAllAtWks$) method:=row ow:=[%(SmpBk$)]%(AtWks$)!;
wAppend irng:=%(strAllOxWks$) method:=row ow:=[%(SmpBk$)]%(OxWks$)!;
James |
| Clairekun |
Posted - 07/08/2021 : 2:16:42 PM
quote:
Originally posted by Chris D
What happens if you simplify it considerably:
That is what I first tried, but it's not working. The workbook and worksheets are created, but they appear blank with no errors on the message log whatsoever.
Since the help file for wAppend function specified each one of the worksheets to be appended, I thought about creating the array, but I'm just not knowledgeable enough to make it work.
quote:
From help file:
Command Line Usage
wAppend irng:=([Book1]Sheet1!, [Book1]Sheet2!, [Book2]Sheet1!) method:=row ow:=[<new>]<new>;
|
| Chris D |
Posted - 07/08/2021 : 1:52:30 PM
What happens if you simplify it considerably:
newbook name:="Samples" option:=lsname sheet:=0;
win -a "Samples";
string SmpBk$ = page.name$;
newsheet book:="Samples" name:="Atomic" outname:="Atomic" active:=1;
string AtWks$ = wks.name$;
newsheet book:="Samples" name:="Oxides" outname:="Oxides" active:=1;
string OxWks$ = wks.name$;
//Loop through workbooks to retrieve the name of their first worksheet
doc -e W
{
string AllBk$ = page.name$;
string AllWks$ = layer1.name$;
if (Left(AllBk$, 3)$ == "T1A") //First 3 letters of the workbooks containing Atomic info
{
wAppend irng:=[%(AllBk$)]%(AllWks$)! method:=row ow:=[%(SmpBk$)]%(AtWks$)!
}
if (Left(AllBk$, 3)$ == "T1O") //First 3 letters of the workbooks containing Oxides info
{
wAppend irng:=[%(AllBk$)]%(AllWks$)! method:=row ow:=[%(SmpBk$)]%(OxWks$)!
}
}
Thanks,
Chris Drozdowski
Originlab Technical Support
|
|
|
