List View 1.84 Excel to Lightroom
Post updated 5 March 2016: I’ve just uploaded the next version of ListView and will release it officially when I’m happy it works correctly. If you want to try it though, I would welcome any feedback. Lr 5+ users get more standard-looking scrolling, and I’ve also introduced an option to create tab-separated export. But potentially the most exciting addition is an Excel add-in (included in the zip file) that can send metadata directly from Excel into Lightroom.
It’s important to say that this is a bonus feature only and that it is not suitable for everyone. You will need better-than-average Excel skills such as, for example, knowing how to edit macros. If you only have basic Excel skills, this tool is probably not for you and I cannot support you.
The Excel add-in works by adding a cell function =ListViewValue() to Excel. In your spreadsheet you just create cells with the =ListViewValue formulae and run a “Send to Lightroom” macro. The ListView plugin in Lightroom then receives the formulae values, searches for the photo (using the folder path or UUID) and updates the field in Lightroom. At present not every field is available, but I’m testing it with these important ones – caption, title, headline, country, state, city, location, label, rating, job, instructions, GPS, and keywords.
- It will only be a bonus feature
- It’s only for those with good Excel skills and I won’t offer step by step instructions or support it
- Current versions of Excel are required
- I do not plan to make it work on older Excel versions
Important limitations
- It will not run under the Excel 64 bit version which Microsoft don’t recommend for general use
- It won’t work in El Capitan or Sierra
- Unfortunately Apple broke the method used to communicate from Excel to Lightroom
- I will see if I can find an alternative, but it won’t be so sweet and easy
Some tips:
- Keywords will be slower to import than other fields. As guidance:
- 2 mins to update 1765 photos’ keywords where all keywords were already in the catalogue
- 5 mins to update 1765 photos’ keywords where all the keywords also needed to be added to the catalogue
- 25 secs to update 1765 photos’ ratings
-
The Activity Center shows the import process’s detailed progress
If sending keywords, try closing the Keyword List
- Updating may be faster if you don’t have any images selected
- Updating may be faster if the library Filter panel is not displayed
The idea comes from my former life as a financial IT consultant where I used to do lots of work with Excel’s VBA programming language and encountered a number of similar Excel add-ins which read and wrote to large business systems. So I’m confident it’s a workflow which could be popular, at least among Excel-savvy users. So try it, and let me know it works.
Hi John,
This looks like a very useful utility. I downloaded the plugin to try it out. At present, I want to generate a simple listing with a thumbnail image, filename, folder or path, and keywords. I can’t seem to make it export listview to excel with thumbnails. When I click on the Excel icon, it generates an html file and jpgs, and loads the data to an open Excel spreadsheet, but fails to load the thumbnails in the ‘Picture’ field. Does this feature only work on the registered version, or am i doing something wrong? I’m working on LR6 on a Mac.
It’s a problem with the latest version of Excel on Mac, which fails to display the JPEGs from correctly-formatted HTML. Earlier versions of Excel on Mac were fine, as is Excel on Windows, so I am hoping for a bugfix from Microsoft.
Thanks John.
I have tried the process in Windows to see how it works. The tool generates an excel output, linked to images generated with a simultaneous html. Is that right? It seems that the pictures within the excel file are linked to these html thumbnails. Is there a way of embedding them within the excel spreadsheet itself, so that it can be transported independently?
It is HTML with images, and Excel is then told to open that file. I don’t think it would be possible to create a true Excel file from code within Lightroom – if so, I am not that smart!
If I pay for registration, does one registration cover both Mac and Windows use?
The version on Photographer’s Toolbox is 1.68. If I buy through that page, I assume it will work for later version?
Thanks again for a great tool.
Damian.
Yes, it covers Mac and PC and all updates.
Hi John,
Been trying to get my spreadsheet data into LR CC classic with LV in 64 bits (office 365) but although no errors are shown the transfer to LR does not happen. I see metadata titles occur with * * around it but also these fields stay empty. Is 64 bits still not supported? 32 bits is not supported by the latest LR versions any more really. Help is appreciated whilst I have 5200 lines to import. Thanks, Louis
Sorry, Louis, it still won’t work with 64 bit Excel. I think you would find it better to use LrTransporter for the import process.
Hello John,
I just downloaded and installed the 1.84 version of the plugin (I’ve had a license for a few years)and now the plugin doesn’t show up on the Plugin Extras list, so I can’t use it. Windows 10, LR CC, up-to-date. What have I done wrong?
I’d appreciate any help.
You may have looked at the wrong Plugin Extras menu, Sara. ListView is in Library > Plugin Extras.
Well thank you very much! I’m embarrassed to not have known that. It is indeed now there. Strange that it was previously on the other menu.
Thank you very much!
Great, love it,
But how do I re-import the Spreadsheet (once I have modified it) back into my catalogue ? I want to modify the Title and Comment fields for about 27,000 images
Thanks
ListView has never been promoted as a way to get metadata back into LR – you would need the Excel add-in to do that. And as stated above, this is only a bonus feature which depends on you having strong Excel skills and, sadly, is limited to Windows and earlier versions of Mac OS.
Happy New Year, John….
I am gearing up to use LV again this winter for the GTCCC Competition in Toronto. I am wondering if you can give me a quick update on where you are with the incompatibilities arising from new MAC OS’s, and Mac EXCEL versions.
I am still running ver 1.83 on MacOS Sierra, using EXCEL:mac2011, ver 14.7.7 which is from Sep 2017. That’s working fine today.
When I try to move to Ver 1.84 on the same system I am also getting runtime errors – you note this in your post below .. and if I try to move forward to EXCEL:2016 1.83 will also not run and, I guess the communications mechanism is ‘broken’ as you say.
So, for this year I can stay with 1.83 on EXCEL:mac2011 but I am a little worried about the future since at some point we are going to want to move to the more recent software and OS’s. Can you let me know your thinking about this and what may happen.
Best regards, jc
John, thanks for ListView. Can you let me know where I could find a copy of the listview.xlam file for sending data from Excel to Lightroom? I am not seeing it in the current zip for v1.86 and the dropbox for the v1.84 referenced in this post is no longer active. Thanks again for your work.
Hey John, just finished doing our GTCCC competition this year and LV worked fine – 1.83 And I was running it on El Capitan.
I do see you have 1.84 available – Can you tell me specifically what you have changed over 1.83 ?
Also, I was trying the thumbnails feature and no matter what size I set, I got very small thumbnails in the Excel spreadsheet.
cheers, jc
What changed? It’s mainly the mechanism to read metadata back from Excel. There are also changes to make the thumbnail process much faster, relying on whatever thumbnails have already been generated rather than building its own. Obviously some refinement is needed.
Hi John, I’m running the trial to see if my system is compatible with your plugins because if they work, it will save me an immense amount of time when I purchase them. In the trial however, when I try to send metadata to Lightroom (using Lightroom 5, Excel 2011 for Mac and running Mac OSX 10.9.5) I get a:
Run-time error ‘429’:
ActiveX component can’t create object
Within VBA, the run-time error occurs when creating the import file specifically:
Set fso = CreateObject(“Scripting.FileSystemObject”)
Is there a workaround to this?
Thank you
At the moment, there’s no workaround. I do want it to work, but it involves redesign of the process.
When using V1.83 of LIstView, Choosing “Full Monty” leaves only SCV or Tab delimited exports. This are plain text exports, which causes fields such as exposure to write unreadable values. This is not the case in V1.68 where export to excel writes a fully formatted file so that exposure values are readable.
Thanks John.The plugin version v1.83 works beautifully for my purposes. This is the first tool that I have found to allow me to copy tens of thousands of keywords from a current database (old Ulead PhotoImpact Album program) to thousands of photos transferred to my Lr catalog.
I appreciate your work on this.
John, Thanks for working on this. I’m anxious to give it a try. However, when I attempt to download the file at:
https://dl.dropboxusercontent.com/u/7678369/ListView.zip
I get a warning from Google Chrome that the file is malware. Give me some reassurance that your site hasn’t been hacked and the URL compromised and I’ll ignore the warning and download anyway. I’ll not hold you liable for any damages.
Thanks and let me know if I can be of help on this.
I do look out for evidence of hacking and I don’t believe there is any problem with the zip file.
John, this may be the Holy Grail that I have been seeking for a couple of years if it can update whatever field LR uses for “Capture Date.” I have 35,000 images (one-third of them video), most of which are digital conversions of legacy media like film negatives and VHS video. To sort by Capture Date in LR has entailed editing the Capture Date for every image, and using Edit Capture Date by selecting multiple images having the same date typically causes LR to increment the Capture Date it applies to the images rather than writing the same date across all of them. Part of the problem seems to be that LR uses different EXIF fields as Capture Date for different file types, but I’m not sure. In any case, will ListView manipulate the Capture Date field?
Well, it can’t write to the EXIF fields because Adobe don’t allow that. But by coincidence (or maybe not) someone else recently asked about the same task, and another of my plugins is probably more helpful – Capture Time to Exif. But Excel certainly helps. Tell me, where are the dates currently stored? And have you ever used Exiftools?
Hi John, found this tool through a post in Victoria’s LRForum (username AMG). Installed the LR plugin and the XLAM file. Selected a few pics to apply the green label as per your video example. checked and double checked the LVV() formula in excel. Pressed the send to LR. Switched to LR but none of the green labels were applied. Im running Excel 2011 on a Mac Yosemite. FYI no errors (VBA) appeared.
I had a quick look in VBA and noticed that part of the code was changed to comments. The part where it checks if the code is running on a mac or not. I’m not a VBA expert but can up to a point read it. Should I enable that part?
Thanks, Rob. It looks like an oddity (or bug?) on Mac where the formula can change to listViewValue in the spreadsheet but the SendMetadata code is looking for ListViewValue with a capital L. I’ve just updated the xlam file so it’s case-insensitive and tested with Excel 2011. So I suggest trying that. Let me know how it goes.
I’ve seen a PM at the forum and will respond probably tomorrow.
John, downloaded the latest version, stepped through the Send to LR code, also ran it a couple of times, tried with green and Green but alas so far no joy. Tried it with 5 tiff files, Tried it with 5 CR2 files, restarted Excel and LR to try again. Still no errors or warning messages but no labels applied either.
Weird that Excel does show ListViewValue but when typed in it uses lowercase L. Even tired a variation of the VBA by changing all instances to lVV instead of LVV but alas.
Impressive piece of coding though.
IF I get a chance to borrow a Win machine I’ll install LR and try it with Win/Excel. Though I’d prefer to use Mac.
Cheers,
Rob
Rob, I’ve run it a few times today on both Mac and PC and believe it’s working. As an idea, in the VBA view you can look at the Immediate Window and see what instructions are being sent to LR.
Hi, I’m trying to run this plugin but I keep getting an error. Can you help?
./menu_list_5.lua:513: attempt to call global ‘presetsDefault’ (a nil value)
Thanks for reporting the error. I’ll investigate.
The official version of the plugin is at http://photographers-toolbox.com/products/jbeardsworth/listview/
Rachel, I think I have fixed this error. So you can use this version if you wish – it’s now version 1.80. Thanks again.
Just tried version 1.84 and get a similar error when selection the CSV export:
An internal error has occurred: menu_list_6.lua:510: attempt to call global ‘ExportCSV’ (a nil value)
What version of Excel / operating system?
Hello John,
I just used it again to spellcheck captions for a folder containing over 1000 photos. The plug-in works great as does the Excel add-in. One thing I did notice is that in the “send to ListView” the quotes that I’d used within a caption were not reverse imported although they were correctly displayed in the spreadsheet. Is that because Excel sees test within quotes as a string?
Thanks again for your work.
Cheers,
Sara
The video is excellent – great explanation. I’ll play later. Thanks for providing this resource John.
Roger