2.15 Working with Excel

Working-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.runto 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