Subroutine to Read Data From Excel Spreadsheet

Macro Library
1

Purpose

This subroutine extracts the data from an Excel spreadsheet. The data can be either a cell or a range of cells from a specific worksheet or whatever worksheet the spreadsheet opens to by default.

Inputs

  • Local__SpreadsheetPathName - the name of the file containing the Excel spreadsheet. The name should be the pathname in full or use the tilde (~) character as a substitute for the user’s home directory.
  • Local__Worksheet - the name of the worksheet containing the cell(s) to be extracted. If this is left empty, then the worksheet used is the one that the spreadsheet opens to when Excel is run.
  • Local__CellToRead - the cell or cells to read. A single cell is specified by its row and column in the worksheet such as A1. Multiple cells can be specified by using a range expression, for example A1:C4.

Output

The subroutine returns the cell as a string, and multiple cells as a comma-delimited KM array.

Operation

The subroutine uses AppleScript to extract the data.

Error Checking

Error checking is performed to ensure that

  • the spreadsheet file exists
  • the cell or range of cells is not left empty

If either of these checks fails or an error occurs in the AppleScript

then the subroutine will fail with a notification.

Credits

This subroutine was derived from the solution provided in the KM Forum discussion thread Pull data from Excel to use in creating macros

Here is the subroutine:

Download Macro(s): [SUB] Read Data From Excel Spreadsheet.kmmacros (19 KB)

Macro-Image

Keyboard Maestro Export

Macro-Notes
  • Macros are always disabled when imported into the Keyboard Maestro Editor.
    • The user must ensure the macro is enabled.
    • The user must also ensure the macro's parent macro-group is enabled.
System Information
  • macOS 13.6.1
  • Keyboard Maestro v11.0.1

To test the subroutine please download the example spreadsheet and test macro.

Example spreadsheet: sampledatawinterathletes.xlsx.zip (161.9 KB)

Test macro:

Download Macro(s): Test [SUB] Read Data From Excel Spreadsheet.kmmacros (6.6 KB)

Macro-Image

Keyboard Maestro Export

Macro-Notes
  • Macros are always disabled when imported into the Keyboard Maestro Editor.
    • The user must ensure the macro is enabled.
    • The user must also ensure the macro's parent macro-group is enabled.
System Information
  • macOS 13.6.1
  • Keyboard Maestro v11.0.1
2 Likes