Use Excel Workbooks in Origin 2017

In this blog post we’ll be discussing one of Origin’s little known features, the ability to link Excel files to OPJs using the Microsoft DDE protocol. In Origin you can not only import Excel data but rather open Excel files themselves within the Origin workspace. This allows you to edit these files in real time and save changes to your original, or otherwise modify the original Excel file and see these changes reflected in any linked books in Origin. To do this you will need Excel installed on your computer as the program will be opened inside the workspace itself. You can download a sample OPJ file containing linked Excel files here.

One can open an Excel file from within Origin by going to File>Open Excel (Ctrl+E) or create a new Excel workbook by selecting File>New>Excel. In previous versions of Origin these functions also appeared as buttons by default; should you want to add these to your toolbar for easy access, you can do this by going to View>Toolbars (Ctrl+T) and navigating to the the Button Groups tab. In the Standard button group you will find the icons for the New Excel book and Open Excel book functions appearing as they do below-

Let’s begin by opening any external Excel file. Using the Open Excel menu option, navigate to an .xls or .xlsx file and open it. If an error message appears saying “Origin is unable to detect a proper installation of Excel”, go back and double-check that Excel is installed or otherwise if you have correct permissions for it on a shared network.

Once the file is opened, the Excel toolbar will appear within the Origin workspace. You will also notice that the xlsx worksheet’s graphic user interface (GUI) is Excel’s rather than Origin’s standard worksheet GUI because Excel is running within Origin.

The path for this file is listed in the title bar. Every time we change the values in this worksheet and save, the original file will be updated.

When an Excel window is open you may notice there are only 3 menus: File, Plot and Window, available on the top of Origin workspace. This is because the file is being ported through Excel within Origin. To fully utilize Origin’s plotting and analysis features with this data, you can copy it from this Excel window and then select a cell in Origin workbook and choose Edit>Paste link, use the shortcut Ctrl+Alt+V, or right click and choose Paste Link. This will create a DDE link between the Excel data and the Origin workbook so when values in Excel are changed, the linked data in Origin workbook will change as well.

If you save an OPJ containing an Excel workbook, subsequently modify the external Excel file, and reopen the OPJ, its contents in Origin will be updated as well. This feature can be especially useful for users whose instruments automatically output to a Excel files, or for users that have more experience with Excel than Origin.

2. Relative Path Excel Files

The one disadvantage of the previous method is that it is “path dependent”. Whenever the Excel file is moved to a different folder, or the related OPJ is opened on a different computer, the DDE link will be broken and Origin will prompt you to locate that Excel file.

For this reason you may want to save your Excel file in a “relative” path with the OPJ, i.e. the same folder as the OPJ.

If you haven’t saved the opj yet, or the opj was saved to a folder totally different from Excel file location. Right click on the title bar of the Excel book and selecting Properties. In the dialog, click “Switch to OPJ Path upon Saving” button. The next time the OPJ is saved, it will place a copy of the Excel file in that same folder.

If you already saved the OPJ file in the same folder as the Excel file or some folder above the Excel file, you still need to check the “Relative to current project (OPJ) path” checkbox. In such case the “Switch to OPJ Path upon Saving” button is grayed out. No extra copy of the Excel file will be saved in such case when save OPJ.

After closing out of the properties dialog, the Excel window title in Origin will indicate that the file is saved in that relative path. Please make sure give both the OPJ and the relative Excel file and keep their relative path. This way when other users open the OPJ on other computers, the link between the two files will remain unbroken regardless of where they are stored.

Note: In versions of Origin prior to 2017, all OPJs with DDE links (Copy/Paste Links) between Excel and Origin workbooks, would prompt the following message upon opening them-

and the following when closing Origin-

Starting in Origin 2017, any Excel file is saved with relative path to an Origin OPJ file and also DDE linking, will no longer trigger these messages.

3. Internal Excel Files

If you would rather not save your Excel data as a separate file, Origin also gives users the option to save Excels internally in your OPJ like any other workbook. To enable this feature all we have to do is open the Workbook Properties dialog for the Excel sheet, and change the Save As option from External to Internal.

Upon closing this dialog the word [Internal] will appear in the title bar of the Excel workbook, indicating that the Excel file is now internal object of the OPJ without any DDE linking. Once this has been done, any further changes made on the original external Excel file, will have no effect on this workbook. Internal Excel files make it easy to OPJs as no additional .XLS/.XLSX files are needed.