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

Macro Library
21

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
}
22

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"}
23

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
24

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
25

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.

Bildschirm­foto 2023-01-16 um 21.39.40
Bildschirm­foto 2023-01-16 um 21.39.40838×342 26.1 KB

What is going wrong?

Best Matze

26

No idea.

Send me the new template worksheet.

You are operating on the front Finder window - yes?

27

Hi,

Yes I am.

Here is the template Sheet.

Best, Matze

File Rename Template.xlsx.zip (34.3 KB)

Bildschirm­foto 2023-01-17 um 07.54.16
Bildschirm­foto 2023-01-17 um 07.54.161504×970 183 KB

28

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

29

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).

30

Hi Nige

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

31

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?

32

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
33

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
34

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

Sorry, that this causes trouble.

Best, Matze

35

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