2.15 Working with ExcelWorking-with-Excel
Origin can use Excel Workbooks directly within the Origin Workspace. The Excel Workbooks can be stored within the project or linked to an external Excel file (*.xls, *.xlsx). An external Excel Workbook which was opened in Origin can be converted to internal, and an Excel Workbook created within Origin can be saved to an external Excel file.
Open Excel Workbook
Internal Excel Workbook
To create a new Excel Workbook within Origin ..
window -tx;
The titlebar will include the text [Internal] to indicate the Excel Workbook will be saved in the Origin Project file.
External Excel Workbook
To open an external Excel file ..
document -append D:\Test1.xls;
The titlebar will include the file path and name to indicate the Excel file is saved external to the Origin Project file.
Save Excel Workbook
Internal Excel Workbook
Though the internal Excel workbook can be saved automatically with Origin project, you can save this internal Excel Workbook as an external file at which point it becomes a linked external file instead.
// The Excel window must be active. win -o can temporarily make it active
window -o Book5 {
// You must include the file path and the .xls extension
save -i D:\Test2.xls;
}
External Excel Workbook
You can re-save an external Excel Workbook to a new location creating a new file and link leaving the original file on disk ..
// Assume the Excel Workbook is active
// %X holds the path of an opened Origin Project file
save -i %XNewBook.xls;
Update Origin When Excel Workbook Changes
When you type or paste data into an Excel workbook sheet, you can update Origin by set -ui . For example:
//Select File: New: Excel from Origin menu
//Enter 123 in Row1 ColumnA in Excel worksheet
set Book2 -ui; // Update Origin
col(A)[1]=; //Get value in Row1 ColumnA
| In Origin's GUI, you can also update Origin by right clicking on the title bar or Excel workbook and then selecting Update Origin... in the context menu.
|
Connect Excel Workbook
If you want to link Excel files to Origin project, you can use the Microsoft DDE protocol by dde command. For example:
// Before running the following example, launch Excel workbook manually or use the run -e command.
dde -c Excel|[Test1.xls]Sheet1 id; //Connect to Excel worksheet [Test1.xls]Sheet1
if(id>=0) //Check if the connection is successful
{
// Send data in columns A through F and rows 12 through 25
// of Excel worksheet to Origin worksheet and start from column 1 and row 1.
dde -rc id R2C1:R11C2 [Book1]Sheet1!R1C1;
}
dde -d id; //Disconnect
See also dde command.
Run Excel Macro
Origin uses an excel object method excel.run to run Excel macros from Origin.
//SheetName is the name of the Excel worksheet containing the macro
//No more than five arguments
excel.run(SheetName.MacroName, Arg1, Arg2, Arg3..., Arg5);
Or
excel.run(ModuleName.MacroName,Arg1, Arg2, Arg3..., Arg5);
You may need to select and activate a range for running Excel Macro, try the method excel.runrange of excel object. For example:
//It will activate Sheet2 of the (active) Excel workbook
//and select columns A through F and rows 12 through 25.
excel.runRange(Sheet2,A12:F25);
See also excel object
Invoke Visual Basic Function
In Origin, the excel object also provides script access for invoking Visual Basic application functions. Similar to running excel macro in Origin, you can invoke Visual Basic function by:
Excel.Run(FunctionName, Arg1, Arg2,..., Arg5)
For example, there is a Visual Basic function as below:
Sub Hello()
MsgBox ("Hello, world!")
End Sub
You can invoke this function by:
//Keep the workbook containing the function above active
excel.run(Hello); //A message box will show up
See also excel object
|