Suggestions on How to Populate Excel via Keyboard Maestro

Questions & Suggestions
1

I'm wanting to populate an Excel sheet based off of variables in Keyboard Meastro.

For instance, I have a variable %logPath% that is a file path to a .txt file. I'd like to use this variable to build a file path that's used to link to it in Excel that looks something like this:
='/Users/user/Desktop/[logPath.txt]logPath'!$A$1

I have a few easy ideas to create the actual path but the part I'm drawing a blank on is how to input this variable into Excel to a specific location based on the log path (in row 1 if log path is 1, or in row 5 if log path is 5 etc.)

Any ideas/suggestions would be much appreciated!

2

I used KM to interact with Excel via Python before.
You will need the knowledge of Python and install python packages.
Here is a list of some python-excel packages that I found by google: Python Resources for working with Excel - Working with Excel Files in Python (python-excel.org)

3

Hey William,

There is no simple way to do what you want, but it's doable.

Here's a very basic example:

How Do I Copy 6 Fields of Text From an Email & Paste Into My Notes App? - #66 by ccstone

-Chris

4

While you are working on other, better, ways -- you can do this via the UI.

  • F5 (or Func-F5 if your function keys do "special" things)
  • Keystroke Tab
  • Insert text by typing/pasting -- the cell reference you want to go to
  • Keystroke Return
  • Insert text by typing/pasting -- whatever you want
  • Keystroke Return to accept the entry

Example (make sure you have an empty spreadsheet open and frontmost!):
Excel test.kmmacros (5.1 KB)

Image

Excel test
Excel test1578×2354 324 KB

I've left it to "Insert by typing" for the full visual effect -- by pasting will almost certainly be faster.

5

This was very helpful!!

I spent a lot of today working with this as a basis and digging in, but I've grown fatigued with AppleScript's syntax in the process.

I tried to convert some of this into JXA as I'm more familiar with JavaScript syntax but yet I'm having a tough time finding any examples to piggy back off of and going through the library hasn't been fruitful for me in understanding how to go about utilizes the methods available.

Do you have an idea how I would do "set value of cell" in JXA?

6

Nyet.

JXA is a major pain to figure out...

Here's a relevant Google Search: ("JavaScript for Automation" OR JXA) Excel

You can ask on the Script Debugger Forum and/or MacScripter.net

Here's some information about using JXA with Numbers:

Don't expect Excel's syntax will go one to one with Numbers, but you can use it to help get a feel for how the syntax works.

@ComplexPoint is one of our more accomplished JXA users, and he may be willing to help out.

Rob?

1 Like
7

I'll leave JXA to others, but AppleScript is quite easy. I've split the cell you want to add the formula to out to a variable so you can see how to use it in the command:

tell application "Microsoft Excel"
	set cellRef to "B3"
	set formula of range ("[Book1]Sheet1!" & cellRef) to "=B1+B2"
end tell

...and you can include your logPath variable in the "formula" string in the same way.

If you get stuck working that into whatever you already have, post the macro so we can see the terms extracted, variable names used, etc.