Multiple Named Clipboard or Variables Not Behaving Appropriately

Questions & Suggestions
1

I have successfully created a macro that will move up and down in an excel file, save text to named clipboards, and then activate acrobat and paste the named clipboards into various fields.

The problem is that it worked and then stopped working. It will paste incorrectly other named variables. I have spent time adding and adjusting pauses with no success.

Is there a better way to go about this? Thank you for any help.
Excel data to Acrobat.kmmacros (47.7 KB)

2

Hey Jason,

Microsoft Excel has a proprietary clipboard that makes copying and pasting with Keyboard Maestro more difficult than it should be.

The best way to get a selection out of Excel is to use AppleScript.

--------------------------------------------------------
# Auth: Christopher Stone
# dCre: 2014/08/02 19:12
# dMod: 2017/11/16 08:21
# Appl: Microsoft Excel
# Task: Value of Selection with multiple cells.
# Libs: None
# Osax: None
# Tags: @Applescript, @Script, @Microsoft_Excel, @Value, @Selection, @Multiple, @Cells
--------------------------------------------------------

tell application "Microsoft Excel"
   set cellValuesOfSelectedRange to value of selection
end tell

--> A single row of data extracted as an AppleScript list:
--> {1234.0, "EF-LG-MB-P", 15.0}

--> Multiple Columns of data extracted as an AppleScript list of lists:
--> {{1234.0, "EF-LG-MB-P", 15.0}, {19, "LG-P", 14.0}}

--------------------------------------------------------

If you want more help you'll need to provide an example worksheet and pdf form.

-Chris

3

Chris, thank you for your help. I have attached 'example 2021-09-28 AR.pdf', 'Report 2021-09-28.xlxs' and the 'Excel data to Acrobat.kmmacros'. I had to put them in a zip file for the forum to accept.

How this macro works:

  1. Start with the full name in excel cell C31
  2. Execute the macro with the trigger ⇧+⌘+N
  3. Goes to the DOS cell C3 and copies to clipboard name DOS, then returns to cell C31
  4. Goes through all excel cells that are relevant to that column of patient information and does multiple named clipboards
  5. Activates Acrobat and click on relative space to tab into the first field.
  6. Paste named clipboards in the correct fields utilizing the tab orders in Acrobat.
  7. Execute a save as with the patient's full name on the system clipboard.
  8. Pause until save and then directs to open the original 'example 2021-09-28 AR.pdf.'

These three files work most of the time. However, if my computer is sluggish, I find myself having to adjust pause times, which sometimes will fix the problem. Other times I close all programs and reboot the laptop, and it will still paste incorrect named clipboards. I'm not sure if I need to delete the named clipboards after some time or what causes this behavior.

I appreciate your time and efforts. Keyboard Maestro needs a donate button. You'll make it possible for so many to become so much more efficient.

KM Forum.zip (705.3 KB)

4

Hey Jason,

This is professional level work, so I'm going to give you the basics and let you continue from there.

The appended macro shows you how to extract data from your Excel worksheet into global Keyboard Maestro variables.

I've only included a few of the cells you're working with, but you have a template to build upon that.

I don't have Acrobat, so I'm not going to get into that part. However – Acrobat can be scripted with JavaScript, so you can directly set text fields and other objects without having to fight with copy/paste. There's a learning curve involved, but it shouldn't be too horrible – and it (along with my example) will make your process much faster and much more bombproof.

https://www.youtube.com/watch?v=wySx6rhK-E4

Extract Data from Daily Report for input into a PDF Form v1.00.kmmacros (8.3 KB)

Macro-Image

This compiled AppleScript is the same as that in the macro, but you can open it in the Apple Script Editor.app or Script Debugger.

I recommend Script Debugger, because even the freeware “Lite” version is light years ahead of the Script Editor.

Extract Data from Daily Report for input into a PDF Form.zip (12.2 KB)

You will want to zero-out or delete your variables at the end of the macro, so old data can't accidentally pollute successive runs of the macro.

-Chris

1 Like
5

Thank you Chris. This information is very helpful. I've run into a few hurdles to complete this task and want to see if you or the community can help me solve some of those intermediate steps.

  1. Can AppleScript reference an excel column based on current cell position? If Cell G31 is selected then ?? below is G

tell application "Microsoft Excel"
tell active sheet of active workbook
set PDOB to value of cell "??11"

  1. How can I set the field values in the active acrobat window to the variables established in KM? (i.e. this.getField("PDOB").value = KM Variable PDOB
6

As I said; I don't have Acrobat. You'll have to research that yourself.

The syntax you have above will NOT work in the AppleScript for Excel.

-Chris