How Do I Copy Cells From Excel and Paste Contents of Each Cell on a Separate Line?

I'm trying to make this work but it doesn't at all. I'm copying a bunch of cells in Excel, all on the same column. I've tried with Peter's regex and carycrusiau's but neither of those work.
I was using a For each line before but now I need to be able to paste those lines one by one so I don't know how to step through those semi-automatically. Here's a link to my previous poste.

So how can I modify my previous macro to let me paste manually for each line, or how do I modify the grep so it works with my excel clipboard content?
Thanks.

I moved your question to a new Topic since it is really a different question/request, and the last post to the original topic was 4 years ago.

IAC, I think this is quite simple:

  1. Copy Cells from Excel
    • which puts a TAB character between contents of each Cell
  2. Replace all TABs on Clipboard with LFs
  3. Activate Target App/Document
  4. Paste

will this work for you?
If so and you need help implementing it, just let us know.

Thanks @JMichaelTX,

I'm using cells from a single column, so, excels puts a return at the end of each cell, since they are in a column and not a row. The for each line worked well in my previous macro. Now I just need to remove the last part of the automation where it pastes each line so I can do it manually. ON1 photo raw 2019 doesn't behave the same way as 2018 and doesn't allow moving from one photo to the other with the keyboard, so I need to manually select the next photo, click on the keywords entry field and paste the cell from excel, then go to the other photo, paste the next cell, and on and on. See my original post to see the macro in question.
Thanks.

Can you use Keyboard Maestro to move the mouse to the next photo button and click it? Not sure what your existing macro does but it seems like it might avoid having to manually do things.

Thanks for the suggestion @vincent_ardern, it might be possible to some level, but since it's multiple photos in a grid, I don't know how I would go about it to move horizontally for 5 steps, then go to the second row and start over. I'm thinking it will be easier to just to this part manually. I only have about 20 photos each time to do.

You could make it work with a counter variable that counts up to 5 (for going sideways), and actions based on moving the mouse to a position. You could but that doesn't mean you should. On the other hand, if you can fully automate 20 photos and then make a cup of tea in the meantime....

I'm trying to make this work with the For Each Line and I can't get it to work as expected. I get the first and second line, the second time I run the macro, then I get the First, second, Third line the third time I run the macro, Etc,... it gets really messy really fast.


I've tried to set the ExcelLine to "" also, but still not working. How do I make sure that the macro clears the content of the varibable and doesn't just pile on what was there before and append the text?
TIA!

Ok, so, I found part of my problem, I was running multiple instance of the macro at once because I never got the the end so it was pasting from multiple macros at the same time.

BUT, I still have a problem, it seems the macro is skipping the first line completely and starting on line 2. Any reasons why?

I see a few problems with your macro, but let's start with understanding your workflow.

Questions:

  1. It is always best to tell us your manual workflow, step-by-step.
  2. What exactly is your source data? You need to be more specific than just "Excel".
  3. Are you copying cells in adjacent columns, or will it be rows and columns?
  4. When you paste, do you want to paste everything at once, or only when you give it some signal (keystroke)?
  5. What is the target app?

There have been many questions and macros posted in this forum. Have you done a search?

Thanks JMichaelTX, to answer your last question first, yes, I did search the forums, there was always something that wasn't exactly what I wanted and after trying to modify those, ended up with the macro above.

so to answer your other questions.

  1. I have an excel file sheet with two columns, the second column is the one containing all the keywords separated by coma.

    Each cell correspond keywords that go with a photo in ON1 photo RAW 2019.

    So I would like to be able to copy all cells from the second column in Excel, and paste, One at a time, in the Keywords field in ON1.
    Right now, I have to copy the first cell in Excel, go to ON1, go to the first photo, paste the keywords, go to the second cell in Excel, copy, go to ON1, select the second photo with the mouse (because since Photo Raw 2019 you can't move with the arrow keys like in 2018, which is why I have to redo that workflow). Continue 'til they are all done.

I was looking for a way to copy once, and have KM seperate the Clipboard into lines which I would then paste one at a time in ON1 since there is no way in ON1 to do it with the keyboard to activate the next photo after entering the keywords.

Points 2 and 3 are explained in the first part.
Points 4: only when I give it the signal, since I have to select the photo manually in ON1, paste, then manually select the next photo, select the keywords field, paste, enter.
Points 5: ON1 Photo Raw 2019.6
Hope that answers your question and will enable you to help:-)
TIA

Hey Jeff,

That part's simple enough with a little AppleScript.

------------------------------------------------------------
# Auth: Christopher Stone
# dCre: 2019/09/01 17:46
# dMod: 2019/09/01 17:46 
# Appl: Microsoft Excel, Keyboard Maestro Engine
# Task: Extract Value of Selected Cells to a KM Variable
# Libs: None
# Osax: None
# Tags: @Applescript, @Script, @Keyboard_Maestro_Engine, @Excel, @Microsoft_Excel, @Extract, @Value, @Selected, @Cells
------------------------------------------------------------

tell application "Microsoft Excel"
   set cellValueList to value of cells of selection
end tell

set AppleScript's text item delimiters to linefeed
set cellValueList to cellValueList as text

tell application "Keyboard Maestro Engine"
   setvariable "cellValueList" to cellValueList
end tell

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

This particular code requires a partial or entire (single) column of data selected.

It will also work with one or more columns selected in a single row of data.

Each of these produces a 1-dimensional AppleScript list that's easy to parse.

The script goes further and produces a linefeed-delimited text list for ease-of-use in Keyboard Maestro.

Personally I would try to do this whole job with AppleScript (UI-Scripting ON1 if necessary), but that's a chore beyond the skills of most folks (if it's even possible).

I'd look at the task a little further – but ON1 is over 1.5 GB, and I don't want to fool with installing such a monster – particularly without knowing if it will work on my machine.

-Chris

Thanks @ccstone. ON1 doesn't support Applescript so, that's why I was looking at KM to automate this. What is the difference between getting a list of values like in your script vs telling KM to give me each line of the clipboard in my exemple?
Even if I took your script and put it in my macro, how would I then go through one be one, pasting each line of keywords in ON1 manually?
Thanks for all your efforts!

Hey Jeff,

Right. Personally I'd still try to use AppleScript UI-Scripting over KM-actions-only, because if it works you have direct access to objects and text fields.

I'm not suggesting that you'd want to tackle that though.

Getting the selection from Excel with AppleScript is a better, safer method than using the Clipboard due to the number of data-flavors Excel likes to put on it.

Peter has largely worked around this in KM9, but I prefer to never use the Clipboard with macros in Excel unless absolutely necessary.

You'd copy the output of my script to a KM variable and then probably use a For Each Line in a Collection action.

You'd also use a semaphore lock to prevent the macro from running more than once.

-Chris