Rename Files Using a Simple Microsoft Excel or Apple Numbers Spreadsheet or a Text Table in BBEdit

The target window in the Finder must be open and frontmost.

What system are you running?

Try this on a dummy file or folder – run it from Apple's Script Editor.app:

tell application "Finder"
   set finderSelectionList to selection as alias list
   if length of finderSelectionList = 0 then error "No files were selected in the Finder!"
   set theItem to item 1 of finderSelectionList
   
   set name of theItem to "NUTS!"
   
end tell

It should rename the selected item. Let me know what it does.

1 Like

Hi Chris,

My Mac is running Ventura 13.0.1

Running your Apple Script renames the selected file but only when a file is selected.

I have figured out why your first macro did not work. Excel Sheet was on an external monitor! If both Finder window and Excel Sheet are in the main monitor it works!

Thank you. Matze

Correct. The script operates on the selected item in the Finder:

set finderSelectionList to selection as alias list

That's very odd...

Open Excel – put the template workbook on your external monitor – and run this script in Apple's Script Editor.app.

  • Make sure the template window is frontmost in Excel.
  • Make sure Excel is not hidden.
tell application "Microsoft Excel"
   return name of windows
end tell

What is the result?

Hey Chris,

The result is: nothing happens – no matter which monitor the sheet is frontmost.

Well, that makes no sense at all.

Hmm... Try this:

  • Select Microsoft Excel in the Finder.
  • Run this script.
    • Post the result.
tell application "Finder"
   set finderSelectionList to selection as alias list
   if length of finderSelectionList = 0 then error "No files were selected in the Finder!"
   set theItem to item 1 of finderSelectionList
   return (properties of theItem)
end tell

Pretty printed AppleScript record for Microsoft Excel's properties on macOS Ventura:

{
   class:application file,
   name:"Microsoft Excel.app",
   index:95,
   displayed name:"Microsoft Excel.app",
   name extension:"app",
   extension hidden:true,
   container:folder "Applications" of startup disk of application "Finder",
   disk:startup disk of application "Finder",
   position:{
      -1,
      -1
   },
   desktop position:missing value,
   bounds:{
      -33,
      -33,
      31,
      31
   },
   kind:"Programm",
   label index:0,
   locked:false,
   description:missing value,
   comment:"",
   size:2.004527105E+9,
   physical size:2.102095872E+9,
   creation date:date "Freitag,
   8. Juli 2022 um 09:00:00",
   modification date:date "Mittwoch,
   11. Januar 2023 um 11:23:55",
   icon:missing value,
   URL:"file:///Applications/Microsoft%20Excel.app/",
   owner:"System",
   group:"root",
   owner privileges:read write,
   group privileges:read write,
   everyones privileges:read only,
   file type:"APPL",
   creator type:"XCEL",
   stationery:false,
   product version:"",
   version:"",
   id:"com.microsoft.Excel",
   accepts high level events:true,
   has scripting terminology:true
}

I did not realize that you have meant the result in Script Editor.

This is the result of the script:

tell application "Microsoft Excel"
   return name of windows
end tell

Result --> {"File Rename Template"}

Okay...

So the Excel window shows up as it should – and the macro should work no matter what screen you have the rename workbook on.

With one caveat – when AppleScripting apps it can be problematic if any of their windows are in full-screen mode.

This new macro locks in the rename workbooks name which must be:
⠀⠀⠀File Rename Template.xlsx

When you import it it will be in macro group: Test Group.ccstone – you may move that wherever you want, although I would usually run this type of thing in the context of the Finder.


Rename Files in Front Window Given an Open Rename List Template in Excel v1.50.kmmacros (10 KB)

Macro Image

Keyboard Maestro Export


See if you can figure out why you were (or still are) having problems when the Excel workbook is on an external monitor.

Once this is running properly I'll write a one more version that is straight old-name ⇢ new-name conversion without monkeying with file numbers.

-Chris

1 Like

Hi Chris,

The new macro runs flawlessly even when the Excel sheet is open on an external monitor.

Great, thanks! Have a wonderful Sunday,

Best Matze

1 Like

Hello dear Chris.

I have now received the Excel sheet and the files to be renamed. I have copied both columns from the Excel sheet and pasted them into your File Rename Template.

Unfortunately I now get the following error message.

What is going wrong?

Best Matze

No idea.

Send me the new template worksheet.

You are operating on the front Finder window - yes?

Hi,

Yes I am.

Here is the template Sheet.

Best, Matze


File Rename Template.xlsx.zip (34.3 KB)

Hi there again,

Strangely enough, the macro no longer works with the test setting, too.

„Finder“ hat einen Fehler erhalten: „alias "Macintosh HD:Users:MatzesMini:Documents:FSD Datensätze:1.pdf"“ kann nicht als „"Kroell_1.pdf"“ gesetzt werden.

Num: -10006

The values in the first column are entered into Excel as numbers rather than text, which is why it is returning 3.0 rather than "3". They'll have to be changed to text, either in Excel or in the AppleScript.

Given that you don't have the ".pdf" appended to the "Old File Name" values either, the simplest solution is another column in Excel that concats column A values with ".pdf" to give you your "proper" values -- but that does mean another column to handle in Chris's script (unless you eg do the concatenation, copy the values, paste them over column A, then delete the concat column).

Hi Nige

Thanks for the clarification. problem is that strangely enough, it no longer works with "3.pdf" either.

Doesn't work how? Still throwing the Unicode error, or do you mean the Finder error you last reported?

If it's the Finder error -- is the "old" file present? Can you change the name manually (ie check you have permission to change and that the file isn't locked, or maybe open in another program)? Does the renamed version already exist?

Hi,

I apologise for being imprecise. Yes, I meant the Finder error.

It was my fault again, of course. I had moved the folder where the files were to be changed. Now everything has worked.

All 1300 files have been renamed.

I thank you, Chris and Nige very much for your expertise and patience.

Matze

2 Likes

Hey Matze,

You can't make changes willy-nilly to a system and expect things to continue work properly.

When you changed the file name convention in column one from “1.pdf” to “1” (Number.Suffix to Number) the AppleScript pulled real number values for those items instead of text.

I have updated the script in Post #28 to version 1.51 to forcefully pull string values for the column one items.

BUT – the script is designed to work with Number.Suffix formatted file names in column one.

This was clearly explained in Post #18:


The format for the Excel rename template:

Old Name New Name
1.pdf Kroell
2.pdf Schroeder
3.pdf Vollmer

By happenstance – when I changed value to string value the script actually does work with numerical values only in column one (i.e. without the file suffix .pdf) – but that's just dumb luck.

The devil is in the details, and changing details without understanding exactly what you're doing will often get you burned.

I will post a macro soon that is a complete one to one name swap – OldName to NewName.

-Chris

1 Like

Keyboard Maestro and AppleScript are far too mighty for my limited computing skills.

Sorry, that this causes trouble.

Best, Matze

It happens...

Take a minute and refresh your memory on this (it's changed a bit):

How Do I Get The Best Answer in the Shortest Time to Questions on the KM Forum?

One of the more relevant points:

Tell us your complete workflow along with your end-objective up front – ==do not spoon-feed it to us one post at a time== -- i.e. don't waste your time or ours.

Tinkering with scripts and macros is how we learn – but when you're in the midst of getting help it can be very detrimental when you've tinkered with something and not communicated that clearly to those helping.

This gets very confusing for everyone, and it tends to burn a lot of time.

There's a reason they teach communications courses at University...

1 Like