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.
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, 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:
Start with the full name in excel cell C31
Execute the macro with the trigger β§+β+N
Goes to the DOS cell C3 and copies to clipboard name DOS, then returns to cell C31
Goes through all excel cells that are relevant to that column of patient information and does multiple named clipboards
Activates Acrobat and click on relative space to tab into the first field.
Paste named clipboards in the correct fields utilizing the tab orders in Acrobat.
Execute a save as with the patient's full name on the system clipboard.
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.
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.
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.
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"
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