Running Excel Macro in Origin

This post will show you how to write LabTalk script to directly run a Macro attached to an Excel workbook (XLSM) in Origin. In this particular example, the macro will be used to paste globally fitted results table to the Excel file.

We have put together a Run_Excel_Macro_Sample.zip (13.7 KB) file which includes a script file Run_Excel_Macro.ogs and a Macro-enabled XLSM file Sample.xlsm, you can download this ZIP file to learn how to do this.

In this blog post we will discuss how to:

• Import Excel Data into Origin’s Workbook
• Perform Global Fit with Parameter Sharing
• Run Macro to Paste Results Table to Excel Sheet

 

Run All Sections in an OGS File

If you want to directly run all the sections in Run_Excel_Macro.ogs and see the results, you can follow the steps below:

1. Unzip Run_Excel_Macro_Sample.zip file and copy Run_Excel_Macro.ogs into your User Files Folder. To locate it, go to Start: All Programs: OriginLab: Origin 2015/(the version you have): User Files Folder.

2. Start an Origin session, choose Window: Script Window/Command Window  and run the following script:

run.section(Run_Excel_Macro.ogs, Main)

which will run all later sections as listed in section [Main]:

[Main]

run.section( , Import Excel Data);
run.section( , Perform Global Fit);
run.section( , Paste Results to Excel);

3. In the pop-up dialog, navigate to add the Sample.xlsm file and the script will run automatically till the end. If the attentions prompt from Microsoft Excel appear, you can click Enable Macro 、 OK to continue running the script.

 

Explanation of Code

Import Excel Data into Origin’s Workbook

As shown below, there are three columns in Sample.xlsm: column A (Substrate) for X data, column B (No Inhibitor ) and column C (Competitive Inhibition ) for Y data. 

Code to io import it is in [Import Excel Data] section of Run_Excel_Macro.ogs, which will open dialog to ask you to browse to the XLSM file and then import it into an Origin workbook with first row as column Long Name . See this page to learn how to run sections in an ogs file.

[Import Excel Data]

dlgfile gr:=*.xlsm mu:=1; // Open dialog to browse XLSM file
impExcel excludeempty:=0 lname:=1; // Import Excel workbook into Origin workbook

 

Perform Global Fit with Parameter Sharing

Code to perform global fit on column No Inhibitor 、 Competitive Inhibition (Long Name) is in [Perform Global Fit] section of Run_Excel_Macro.ogs. It will:

1. Plot column No Inhibitor 、 Competitive Inhibition as scatter plot.
2. Select the data plots in active graph window.
3. Start global fitting using function Hill on active graph and output results to tree tt.
4. Share parameter Vmax between two datasets and fix parameter n  to be 1 for both datasets.
5. Fit until convergence is reached.
6. Choose No in appeared prompt to not switch to report sheet and close the prompt.
7. Finish fitting and output results.
8. Re-enable previously turned off reminder message.
9. Copy results table on source graph and store it in a new workbook.
10. Delete “Update” button label in results table worksheet.

[Perform Global Fit]

plotxy (1,2:3) plot:=201 ogl:=[<new>]; // Plot two datasets as scatter
range aa = (1,2); // Select plot 1, 2
nlbegin aa Hill tt; // Starting the fitting on the graph
tt.s_Vmax = 1; // Share parameter Vmax between two plots
tt.f_n = 1; // Fix parameter n in 1st dataplot to be 1
tt.f_n_2 = 1; // Fix parameter n in 2nd dataplot to be 1
nlfit; // Fit till converge
type -mb 0; // Choose No in appeared prompt and close it
nlend 1 1; // Done with fitting and output results
type -me; // Re-enable reminder message
wcopy [Table1]1!; // Copy table in active graph to a new worksheet
label -r update; // Delete "Update" button

 

Run Macro to Paste Results Table to Excel Sheet

Code to copy and paste results table to Excel sheet is in [Paste Results to Excel] section of Run_Excel_Macro.ogs. It will:

1. Select all columns in active sheet (results table).
2. Select menu item Edit: Copy: Copy to copy results table to Clipboard.
3. Open previously imported Excel book in Origin workspace as an OLE instance.
4. Select the starting range in Excel sheet to paste.
5. Run Excel Macro PasteData to paste data from Clipboard to active sheet, then adjust column width to fit the content and add a border to the pasted table.
6. After pasting, it will save the content in the opening Excel book.

[Paste Results to Excel]

worksheet -s 1 0 $(wks.ncols) 0; // Select all columns in active sheet
menu -e 57634; // Run "Copy" command in the Edit menu to copy selection to Clipboard
document -append %(fname$); // Open the XLSM file in Origin workspace
excel.runrange(Sheet1,E2); // Select operation range in Excel sheet
excel.run(PasteData); // Run Excel Macro "PasteData" to paste results table
save -i; // Save the pasted table in the external Excel workbook

As you can see from the code above, in Origin the Excel macro is accessed through LabTalk object Excel . To make use of this Excel object, you have to make sure the XLSM file is opening in Origin workspace and currently active.

Below is a copy of the Macro PasteData attached to Sample.xlsm:

Sub PasteData()

'Paste copied data
ActiveSheet.Paste

'Auto adjust column width
Selection.EntireColumn.AutoFit

'Add a border to copied table
With Range("E2:H12").Borders(xlEdgeLeft)
 .LineStyle = xlContinuous
 .Weight = xlMedium
End With
With Range("E2:H12").Borders(xlEdgeRight)
 .LineStyle = xlContinuous
 .Weight = xlMedium
End With
With Range("E2:H12").Borders(xlEdgeTop)
 .LineStyle = xlContinuous
 .Weight = xlMedium
End With
With Range("E2:H12").Borders(xlEdgeBottom)
 .LineStyle = xlContinuous
 .Weight = xlMedium
End With

End Sub