My entire business is run in a large MS Excel workbook. I wish to create KM macros based on data from one active worksheet, I'm new to this so any advice or tutorials would be appreciated.
Can you explain what you want KM to do?
Yes- I have an Excel spreadsheet that produces a worksheet with values. I would like to reference those Excel cell values to use as variables in KM.
So for instance I would like to compose an email body within KM that uses Excel references to provide the values.
So if I have a workbook called:" _2023-52"
Within that workbook is a sheet named: "DATALINE"
Within the worksheet "DATALINE" I have a table named "dateline". That table has two columns: "field" and "data". See pic.
So for instance I would like to have a KM Action that by triggering a keystroke, it would automatically compose the body of the email such as:
Dear ,
I see that your address is:
, IN .
So basically a mail merge. KM has possibilities to use this that Microsoft does not and I just need to learn how to reference Excel data.
Okay, so it sounds like you're only reading from the spreadsheet, not writing back. That makes things much easier.
I can see a couple of ways to approach this.
The first method is to open the spreadsheet, as you have done, then have KM send a keystroke to Excel to select all the table, then use another keystroke to copy the table into a variable. Then I would use a filter to extract the data you need, then I would compose the email.
The second method is to get KM to tell Excel to save the entires spreadsheet as a CSV text file. Then it would be easy to create a filter to extract only the data you want from that file. I can see a couple of different ways to do that extraction.
There's probably also an AppleScript way to find the data you need. I'm not skilled enough to investigate that option. I assume Excel is compliant with AppleScript. The Windows version of Excel also has a Visual Basic ability for doing things like what you want, but I think I heard that VB was removed from Mac versions of Microsoft's apps a while ago. That would probably be a fourth way, if it worked.
Which of these approaches most interests you?
A fifth way would be to send Excel the keys to "Find a string", then use the arrow keys to move to the correct cell, and copy the data. That's also doable.
Welcome to the KM Forum, Mark.
While I'm not going to completely solve your problem for you I will show you how to get data out of an Excell worksheet. To do this I've prepared an example macro for you to examine and play with; it will help you learn how to get data out of Excel and into KM variables. You can then proceed to complete your task...
The macro uses AppleScript to get the data from Excel. In order to make this example useful, I've included an example spreadsheet that I found on the Internet so you can use that file and the example macro without putting any of your own data at risk.
Here's the example spreadsheet: sampledatawinterathletes.xlsx.zip (161.9 KB)
Download it onto your Mac and unzip it.
Here's the macro:
Download Macro(s): Test Excel.kmmacros (7.6 KB)
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.
- The parent macro group is "[Test]"
System Information
- macOS 13.6.1
- Keyboard Maestro v11.0.1
Just follow the instructions in the green-coloured comment action in the macro to get it working.
As is, the macro produces the following output:
If you have questions, feel free to ask but I now won't be around again until Friday.
Wow thank you very much!
Oops - just found a bug in the AppleScript action. Sorry.
Here's the corrected macro for you:
Download Macro(s): Test Excel.kmmacros (7.6 KB)
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.
- The parent macro group is "[Test]".
System Information
- macOS 13.6.1
- Keyboard Maestro v11.0.1
I've turned my macro into a generally-useful KM subroutine. You can find it here:
2 Likes