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
2

I appreciate everything you have done here but I am not figuring it out to actually work in my case. I have an active workbook path.

File Path:
/Users/markperrine/Library/CloudStorage/OneDrive-Personal/MS Docs/2025-06.xlsm

Worksheet Name:
emailFields

Named Excel Table:
emailFields[#Data]

Here is the image of the table from where I want to extract the exact data to use in KM variables.

Screenshot 2025-06-02 at 12.34.28 PM
Screenshot 2025-06-02 at 12.34.28 PM1048×1260 305 KB

Is your solution more than one macro - or are different subroutines meant to go together?

I appreciate any clarity on this. Also if there was a YouTube video that already has done this- that would be awesome.

3

Hi Mark. Your question puzzled me since it's about 18 months since you originally asked for help. It makes me think that perhaps you're a bit unsure about how to use a KM subroutine. Is that correct or is there some other issue going on? your answer will help me to help you better :grinning:

4

Correct I gave up 18 months ago and now taking another run at understanding. I don't understand subroutines

5

Here in another method I almost have working which copies Excel range into clipboard
PENDING- Sellers.kmmacros (7.0 KB)

6

I can see from your macro that basically you're telling KM to mimic what you would do on the keyboard to get the data out of Excel. This is using KM to "drive" the app's User Interface. There's nothing wrong with that in principle and in fact that's sometimes the only way to achieve the end goal.

One problem with this approach is this: if you (accidentally) use the keyboard or mouse while the macro is running, it can mess things up causing the macro to fail to do what you intended.

A bigger problem is by doing it this way, your macro becomes very inflexible so for example in future if you wanted to get a different range of data you would have to change the way the macro manipulates the cursor which could turn out to very difficult if you also wanted to use a different spreadsheet.

I'm now going to post an answer to your question that refers to the use of the subroutine...

7

Mark this is a long post and used up a fair bit of midnight oil, so I apologise if there are errors in it...

I assume you've read about KM subroutines in the KM wiki: Subroutines [Keyboard Maestro Wiki] but found it a bit confusing so let me try and explain in terms of your actual problem.

My approach to getting the data range A2:B30 from the worksheet called emailFields in your spreadsheet file /Users/markperrine/Library/CloudStorage/OneDrive-Personal/MS Docs/2025-06.xlsm is this demonstration macro:

image

First look at the coloured actions:

  1. (Green) tells you what to do.
  2. (Red) type in the pathname of your spreadsheet file.
  3. (Orange) type in the name of the worksheet you want to get data from.
  4. (Yellow) type in the range of data you want from your worksheet.

The next (5th) action is an Execute Subroutine and it tells KM to give those values you've just specified in the previous 3 actions to the KM subroutine called [SUB] Read Data From Excel Spreadsheet

This KM subroutine (which is defined elsewhere - we'll come to that in a minute) takes the values just given to it and produces a result which is stored in the variable Local__CellData. In this case this variable will hold the data extracted from your worksheet and you can verify that because the last (6th) action in this macro displays the contents of the variable in a window. In a real situation, how you then go about using that data (which is in the form of a KM array) is up to you.

I hope you'll see that the important thing about the subroutine is that when you give it any 3 values (spreadsheet file, worksheet name, data range) it will give back to you the data you want. It doesn't matter how the subroutine does its work it just matters that there is just one subroutine that you could call in any number of your macros that you want.

Now to the subroutine...

First off - a KM subroutine is just a KM macro that uses as a trigger, the Subroutine trigger like this:

image

and this one requires 3 values to be supplied to it as you will recognise from the previous description.

Subroutines are not intended to be run by themselves but are designed to be run by other macros using the Execute a Subroutine action. You only ever need one copy of a subroutine no matter how many times it is executed by other macros. The advantage of that is if you want to tweak what the subroutine does in the future, or you find a bug in it, you need only edit the one subroutine macro, and then all those other macros that execute the subroutine will benefit from the tweak!

By the way - whenever I produce a subroutine, I'll give it a name that starts with [SUB] to make searching in KM easier. Obviously you can give them any title you like.

Finally, I'd like you to try out the macro and subroutine I've been telling you about.

Here is the file:
wfcperrine13 Macros.kmmacros (27.0 KB)

after downloading to your Mac, double-click the file in Fibder and they will be placed in a macro group called wfcperrine13. Then in KM enable the macro group and you should be able to execute Demo for wfcperrine13 - Read Data From Excel Spreadsheet by trying it from the KM editor.

If you have any problems let me know.

Good luck!

1 Like