Loop is working but 1 action in it is not

[hnt007]

Hi! Thanks for taking the time to help me out.

Trying to simply my case to explain what I'm trying to do.

I have an XL with the name of 2 composers, A in 1 cell, B in another cell.
I have filenames that are named after these composers in my Finder.
I'd like to copy the name of the composer from the XL in order to rund a macro that will select the right file in Finder.
If my loop reaches a cell named STOP, then it stops.

The loop is working great the first time (before looping), then it loops correctly BUT my action to use the name of the copied composer from XL doesn't work, so it doesn't select composer B once it loops...

I don't understand why because it works great for composer A! And if I paste my clipboard in a random place, it does paste composer B, so it has been able to copy, but my action to select the right file based on the composer doesn't work once it loops...........

HELP.kmmacros (16.2 KB)

[peternlewis]

The macro is too complex to try to debug visually. I suggest you use the Macro Debugger user manual section, perhaps add a Debugger Breakpoint This Macro action at the start of the loop and step through?

[Nige_S]

@hnt007

Trying to simply my case to explain what I'm trying to do.

Start by simplifying your macro -- if nothing else, that'll make it easier to debug!

You do a simulated-key Copy and, almost immediately, a "Copy" action. Is there a reason for that? And the "Copy" action includes a pause until the clipboard has changed before continuing the macro -- so you don't need to save the clipboard seed or do the comparison.

Why are you putting the clipboard into a variable rather than using it directly?

Now we get to the actual problem -- you're checking whether the variable Filename pattern is empty and if it is then you fill it with your TempVariable (which is also your clipboard contents). But Filename pattern is a Global and it persists across executions of your macro. The very first time you ran the macro it was set to the value you copied -- every run after that is empty is false and so the value isn't changed.

It probably only keeps working for Composer A because Composer A was the first value ever assigned!

You next check the clipboard for your "STOP" condition, which would be better done at the top of the loop after the Copy action. (Also, "contains " is case-insensitive and doesn't mean "exactly matches" -- so you'd also cancel the macro if there was a composer named "Stoppard", which probably isn't what you want...)

You then run an AppleScript inside a Shell Script, doubling the overhead for no apparent reason, to do something that looks like "select all the items in the currently active Finder window whose name begins with the Composer name".

This looks like a good first attempt at quite a complex macro, but I can't help but feel that in trying different things to make it work you've made it over-complicated and inconsistent (and the two often go hand-in-hand). Have a go at simplifying things yourself, and if you explain what you are trying to do perhaps someone can right their own version for you to compare against.

(I know you have explained what you are doing at the top of your post, but it isn't complete. For example, "select the right file in the Finder" -- you can't do that with just a name, there needs to be an indication of where the file is. I'm assuming it's "the right file in the active Finder window", but I'm usually wrong when I guess!)

[hnt007]

Nige, thank you so much for this great and very detailed feedback, I'll check everything you mentioned.

So I have a folder with word docs named after composers, what I'm trying to do is " select the item (because there is just 1 item per composer) in the currently active Finder window whose name contains the Composer name copied from my XL doc"

[Nige_S]

@hnt007

So I have a folder with word docs named after composers, what I'm trying to do is " select the item (because there is just 1 item per composer) in the currently active Finder window whose name contains the Composer name copied from my XL doc"

Name "contains"? "starts with" is less (but not guaranteed) error prone, "is" is even better. But only you know your data and the chances of a name clash.

Is it always the same folder in the Finder, or does that vary? Again, an error reduction thing -- if it's always the same folder you can include the path in your macro, avoiding the problem of the wrong active window when you run it.

[hnt007]

You're totally right about "Starts with" and "is" but my filenames for these word docs included other txt and I have to follow a certain format and this one doesn't start with the composer name

I like the idea of always using the same folder to run this macro, can def go that way. Thank you!!

[Nige_S]

@hnt007

I have to follow a certain format

And that format is...? If it's the same for every file, we can make the file name up from that and the composer's name. If it varies we still might be able to do so, depending on how it is constructed. Otherwise, if there's multiple documents that match the "contains" condition do you want to open them all (maybe with a warning alert) or open none and pop an alert up?

[hnt007]

So there won't be multiple docs that will match the composer names, just 1 Word doc per composer.

Format is Catalogue_Project_Schedule A_Composer_x Cues (variables are Catalogue, Project, Composer and x)

Thank you!!

[Nige_S]

@hnt007

So there won't be multiple docs that will match the composer names, just 1 Word doc per composer.

In which case you can loop through the files in the folder checking for a match, and when one is found open that file and exit the loop.

@hnt007

Format is Catalogue_Project_Schedule A_Composer_x Cues

It's probably good enough that the "Composer" name is delimited by _ -- it'll certainly separate your Bach from your Bachman-Turner Overdrive!

So, putting all the above together:

set the parent folder
repeat until stopped
    copy value from excel
    if value is "STOP" then
        cancel the macro
    else
        for each file in parent folder
            if file name contains ("_" & value & "_") then
                open the file in Word
                exit for each loop
            else
                do nothing
            end if
    end if
    activate excel
    go to next cell
end repeat

Which almost directly translates into KM as:
HELP test.kmmacros (8.4 KB)

Image

I won't pretend to understand how you've laid out your spreadsheet, so I tested with a single column of 4 values then "STOP" -- the Excel-targeted actions at the end reflect that, so change those to suit. And, obviously, you'll need to set your parent folder path in the first action.

There's only one tricksy bit here, and that's the %Variable%Local_theFile[-1]/% construct. Local_theFile is the full path to the file currently being considered in the "For Each" action and the [-1]/ bit means "treat the path as an array, split on the / character, and return the last item". So "give me everything after the last /", which is the full file name -- which we can then check to see if contains our clipboard value wrapped in _.

Tweak to suit how you want to work, and see how you get on.

[hnt007]

Thank you so much, this looks very promising, I'll try it asap.

[hnt007]

It's working great!! Thank you!