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:
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!
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)
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
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)
- Insert text by typing/pasting -- the cell reference you want to go to
- Insert text by typing/pasting -- whatever you want
Return to accept the entry
Example (make sure you have an empty spreadsheet open and frontmost!):
Excel test.kmmacros (5.1 KB)
I've left it to "Insert by typing" for the full visual effect -- by pasting will almost certainly be faster.
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.
Do you have an idea how I would do "set value of cell" in JXA?
JXA is a major pain to figure out...
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.
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"
...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.