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:
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:
- Plot column No Inhibitor 、 Competitive Inhibition as scatter plot.
- Select the data plots in active graph window.
- Start global fitting using function Hill on active graph and output results to tree tt.
- Share parameter Vmax between two datasets and fix parameter n to be 1 for both datasets.
- Fit until convergence is reached.
- Choose No in appeared prompt to not switch to report sheet and close the prompt.
- Finish fitting and output results.
- Re-enable previously turned off reminder message.
- Copy results table on source graph and store it in a new workbook.
- 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:
- Select all columns in active sheet (results table).
- Select menu item Edit: Copy: Copy to copy results table to Clipboard.
- Open previously imported Excel book in Origin workspace as an OLE instance.
- Select the starting range in Excel sheet to paste.
- 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.
- 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