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.

Run_Excel_Macro_02
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:

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

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 and 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.  

Run_Excel_Macro_01

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.

 

Perform Global Fit with Parameter Sharing

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

  1. Plot column No Inhibitor and 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 to be 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.

 

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.

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:

Leave a Reply