| Author |
 Topic  |
|
|
helga55
USA
Posts |
 Posted - 11/08/2006 : 08:57:56 AM
|
Origin Version 7.03
Operating System:Windows 2000
I am an Origin newbie so please bear with what might be a stupid question. When I import an Excel file into Origin in the Origin format I am forced to cut and paste the file name into the Origin worksheet either as Column Label or Column Name. I think this could be avoided by importing ASCI files but my Excel file has many worksheets which would cause further problems. Thanks |
|
|
Mike Buess
USA
3037 Posts |
Posted - 11/08/2006 : 1:32:02 PM
|
I assume you use File > Open Excel in which case the file name is saved to string variable %A. The script below runs that command and labels the windows and first columns (take your pick) of all imported worksheets with the file name. You can assign the script to the Custom Routine button as described here.
nn1=0; nn2=0;
doc -e W {nn1++}; // count worksheets
if( run.section(FILE,OpenExcel) ) return; // run File > Open Excel
doc -e W {
nn2++;
if(nn2>nn1) {
wks.col1.label$=%A; // set column 1 label
wks.labels(); // show column labels
page.label$=%A; // set page label
page.title=3; // show page label
};
};
del -v nn*;
Mike Buess
Origin WebRing Member |
 |
|
|
helga55
USA
Posts |
Posted - 11/16/2006 : 12:15:02 PM
|
Hi Mike,
I think I should rephrase my question. When I open an Excel File with File > Open Excel, I am forced to type each and every Column name from that file into the Column Name or Column Label space in the Origin spreadsheet. Is there any way I can avoid doing this?
I said File name instead of Column name in my earlier post. Sorry about that.
Helga |
|
|
|
Mike Buess
USA
3037 Posts |
Posted - 11/16/2006 : 1:35:01 PM
|
Hi Helga,
This should work if the column labels are always imported to the first row. Note that the label is assigned to the Origin column name and Origin column label. That's because there are restrictions to the number and type of characters in column name, but not label. Use whichever you like.
nn1=0; nn2=0;
doc -e W {nn1++}; // count worksheets
if( run.section(FILE,OpenExcel) ) return; // Open Excel
doc -e W {
nn2++;
if(nn2>nn1) {
loop(ii,1,wks.ncols) {
get %(%H,ii) -e nn3;
if(nn3) {
wks.col$(ii).name$=cell(1,ii)$; // set column name
wks.col$(ii).label$=cell(1,ii)$; // set column label
};
};
wks.labels(); // show column labels
mark -d col(1) -b 1 -e 1; // delete row 1
};
};
del -v nn*;
Mike Buess
Origin WebRing Member |
|
|
|
helga55
USA
Posts |
Posted - 11/20/2006 : 09:53:01 AM
|
Hi Mike,
My apologies but my only programming skills are dabbling in basic IDL.
Over the weekend, I have been trying to figure out how to run programs in Origin but I couldnt run the code you sent me to import column names from Excel spreadsheet. Where do I enter the excel file name? Is it instead of FILE in the line: if( run.section(FILE,OpenExcel). Also I am not sure in what format my file should be ie Excel or .OPJ?
I would really appreciate some help.
Sorry for the bother.
Helga |
|
|
|
Mike Buess
USA
3037 Posts |
Posted - 11/20/2006 : 10:53:53 AM
|
Hi Helga,
No need to apologize... that's the purpose of these forums. Run.section(FILE,OpenExcel) is the LabTalk script that runs when you select File > Open Excel. The command runs the scripts in the OpenExcel section of the script file FILE.ogs. It brings up a file dialog for selecting an Excel (*.xls) file and returns 0 if user makes a selection or 1 if not (ie, if user Cancels file dialog).
The script I gave you can be divided into three parts...
1> The first two lines merely count the number of worksheets in the Origin project and save that number as nn1.
2> The next line (run.section) duplicates the File > Open Excel menu command. You must select an *.xls file as usual then select Open as Origin worksheet and the specific worksheets in the resulting dialog. OK'ing both dialogs will import the Excel file as Origin worksheets as usual.
3> The remaining lines loop through all worksheets in the Origin project and operate on any that were created since the initial count (nn1) by promoting row 1 to column labels.
The easiest way to run the script is to assign it to the Custom Routine button as described here.
Mike Buess
Origin WebRing Member |
|
|
|
Mike Buess
USA
3037 Posts |
Posted - 02/08/2007 : 12:28:23 PM
|
Hi Henrik,
quote:
Is the problem that the worksheet name can only be 11 characters long?
The actual limits are on the number of characters of a column name (18 in 7.X) and that of a dataset name (23). The dataset name for a column is wksName_colName so max length of worksheet name depends on the lengths of its column names.
http://www.originlab.com/www/support/resultstech.aspx?ID=1103&language=English
quote:
I use the active data set name in another script for labeling graphs using the string variable %C (active data set). Now, more helpful would be to make the label using the label of the active data set, where the original Excel file name is stored.
Try this with the graph window active...
%W=%[%C,#1,_]; // wks name
%A=%[%C,#2,_]; // col name
ii=%W!colnum(%A); // col number
%A=%W!wks.col$(ii).label$; // get col label
page.label$=%A; // set graph window label
Mike Buess
Origin WebRing Member |
|
|
|
Mike Buess
USA
3037 Posts |
Posted - 02/13/2007 : 2:09:35 PM
|
Hi Henrik,
%[%A,#n,_] returns the nth token of %A using the character '_' as token separator. Look for Substring notation in the index of your Programming Guide.
Mike Buess
Origin WebRing Member |
|
|
| |
Topic |
|
Column labels from Excel