Is there an easier way to get text into Lightroom?
Updated video
Coming soon to my ListView plug-in, automatic updating from Excel to Lightroom. Have you ever seen an easier way to get text data into Lightroom?
For the technically-minded, it’s an Excel macro which reads the spreadsheet and communicates directly with the open Lightroom catalogue. In my former life as a financial IT consultant I used to do a lot of work with Excel’s VBA programming language, and the use of a custom formula is very similar to how Excel communicates with many business systems, so I’m confident it’s a solution which will work.
It’s worth adding that I’m not doing anything dubious like hacking into Lightroom’s SQL database or XMP files – the automation is entirely via Adobe’s authorised method, the SDK.
I will need testers – ideally existing ListView users. It will only work with Lightroom 5 and you would need to have one of the very latest versions of Excel – definitely Office 2011 on Mac as the previous version omitted VB. If you want to try it in advance of release, let me know by email or by adding a comment to this post.
Now I’ve tested it and it works great, with one caveat: I don’t know if this is new behavior or old, but when I open listview in a folder with a large number of pictures selected (in this case roughly 1100), when I click on a a field name to get the “Source Field” box, the Source Field window is blank and none of the button elements show up. When I select a subset of the pictures (say 100) then the radio buttons show up just fine and I can change the selected fields.
I was really delighted to see that my spelling corrections were made – now it’s just a matter of going through the different folders and running the macro. I don’t think running it on my entire catalog of 50,000 pictures would be a good idea. 🙂
Thanks, Sara
Thanks Sara, I’ve now seen the same problem with the buttons when hundreds are pictures are selected. I’m not sure why that would have but will fix it.
Hi John,
ok – I see what’s happening… because I watched the video on the old Blog entry “ Is there an easier way to get text into Lightroom? ”, I assumed the video on the new Blog entry was the same and didn’t watch it.
Having just watched it I see that there is a whole set of instructions that I missed. So sorry to have wasted your time!! This afternoon when I’m at home again I’ll watch it and follow the instructions.
Thanks again!
Sara
Aha, I’ll edit the post so no-one else is misled. The new method is very different, but very much faster.
Good Morning John,
still puzzling about this… when I look at the ListView.xlam code in the Excel VBA explorer I see this block:
======================================
Function SendMetadataItem(path, uuid, field, value)
Dim Lr As String
‘ If path “path” And path “uuid” And path “filename” Then
‘ msg = “Is the spreadsheet set up correctly?”
‘ msg = msg & Chr(13) & Chr(13) & “Cell A1 should be uuid, path, or filename”
‘ myError = MsgBox(msg, vbCritical)
‘ End
‘ End If
args = “action=update&matchField=” & path & “&matchValue=” & uuid & “&field=” & field & “&value=” & value
Debug.Print (“———————–“)
Debug.Print (args)
If Application.OperatingSystem Like “*Mac*” Then
script = (“tell application “”System Events”” to open location “”lightroom://uk.co.beardsworth.listview/” & args & “”””)
MacScript (script)
Else
Lr = “lightroom://uk.co.beardsworth.listview/” & args
OpenURL Lr, Show_Hide
End If
End Function
==========================
Am I correct in thinking that the macro is not looking for my Lightroom installation but yours? How do I change this? What am I missing?
I installed the add-in by placing it in the ListView directory in my LR add-ins folder and adding it to excel from there. I exen added the folder location to “Trusted Locations” on the Excel security tab.
Any ideas?
Thanks so much, Sara
“Am I correct in thinking that the macro is not looking for my Lightroom installation but yours?”
No. That URL is the plug-in’s id. Lightroom is listening for any http:// calls beginning with lightroom:// and calls the plug-in specified in the remainder of that url.
Excel security is a nightmare nowadays, for perfectly good reasons. Try restarting Excel, opening the add-in, and then try to run the version info command. This should launch a dialog that asks if you want to enable macros.
OK, see http://lightroomsolutions.com/list-view-1-73-with-writeback-from-excel/
Hello John, any update on this modification to the plug-in? I think it would be fantastic for spell-checking in Excel. If you still need testers I’d be willing – I have a ListView licence. (And I couldn’t live without Search/Replace!)
Cheers, Sara
I’ve radically redesigned it, so maybe today…
Hi John,
Thanks for the quick response and action!
I installed and tested it and unfortunately it doesn’t work for me. I loaded the macro and clicked on the command and nothing happened. Probably due to the fact that I have a 64-bit Windows system. Or does it just have to do with Excel’s security settings? One thing I did notice is that the file that’s exported to Excel has the .html extension. Is this correct?
Cheers,
Sara
Sara
64 bit operating systems are fine, but I referred to a 64 bit version of Excel. The vast majority of people use the 32 bit version – and Microsoft don’t recommend people use the 64 bit version except for some special applications. You would certainly have to enable the macros, so review the security settings. As for html, that’s always been true – it’s how I get pictures and colour into Excel.
John
Hello John – what’s the state of play with this development. I’m eagerly awaiting the time I can put it in practise.
!Maybe over the next day or two
Hi John, a bit late I know but I’m definitely interested in testing or trying it out. I’m especially interested in the ability to export keywords back into LR.
Bob.
I also use ListView to proof my metadata.
The small fonts and cluttered fields in Lightroom make editing, reviewing and correcting metadata very awkward.
If I am going to print or publish a batch of images, I use ListView to export the relevant fields, Title, Caption, Location, Copyright details to Excel. I can now proof the actual text using nicely spaced columns and font sizes of my choice.
I have InDesign templates which I then use to mail merge the images and text to produce high quality finished artwork, which includes all of the above fields.
The new feature looks like it might allow me correct any typos and then reimport to Lightroom. Cool!!!!
It is a great way to use the mail merge features of InDesign and Photoshop to apply metadata to a print. For example, have Title, Location and Date placed under an image with full control of position and formatting (currently a major omission in Lightroom).
I have completely automated the printing of images with the combination of ListView and InDesign.
You can acheive that today by creating a Print module template which has a page and cell size which are equal, then using Print Info to extract the metadata fields you desire. The text will then be laid right onto the output file. Import that file and you have what you need for many uses. Works fairly well, but no control on font for example.
LR/Mogrify 2
This allows you to do even more control for text placement, font etc. using your metadata fields.
This is a really cool project and I love that you have proved that it works. I’m struggling to see how to apply it to my normal work flow that wouldn’t already require me to be in LR. I’d love to hear more about how or why you might use this.
A simple example might be spellchecking – export to Excel, run its spellchecker over the caption column, send info back to LR.
Another might be data migration from an older system.
Another example could be some when you’ve a lot of product shots and need to get into your catalogue some information drawn from an external list. You might have typed a product or SKU code into the title field in LR (maybe more than one), and you need the caption to contain the product description(s) from a file supplied by the client. An Excel VLOOKUP formula could use the title column to find the items in the list. This is more advanced, of course, and you’d have to know Excel pretty well – but some people do.
I would be interested in trying this technique.