Saving Tons of Time with Automator (Mac)

I was recently working on a project that was a bit of an odd one. A company had a bunch of product images that were high resolution, but they were embedded on one of the most unconventional file formats… and Excel Spreadsheet. Each file ended up being 3-5MB each, and could take anywhere from 20-30 seconds to open on a newer model Macbook Pro. If I was dealing with 10-20 product images, I would normally just deal with it, and wouldn’t consider Automator. Unfortunately, this project clocked in at over 500 images. If that wasn’t a tough enough situation, I had another problem to deal with. When I managed to open the file, copy the product image, and paste it into Photoshop, I encountered an odd situation where a stroke was added around the border of the image.

So now, I had to deal with a long open time, combined with tons of images that all needed editing, due to a formatting problem. Would I just trudge through this job and spend an entire day trying to find a solution? No. I am the type of person that refuses to work hard, because I would rather work smart.

So the idea came to me that I could export each excel file as a pdf, which would open much quicker, and then I could single click on the image, and paste directly into Photoshop. I wanted to do this, because there was product information text in the excel sheet, and I didn’t want to flatten it, having to crop out the text for each pdf.

With a plan in mind, I had to figure out how to convert 500 Excel sheets to pdf files quickly. After searching for a while, I discovered that some people could use Acrobat 9 to batch convert Excel Sheets to pdfs. There was a problem, though – I am using the newest version of Acrobat, where the Excel options are oddly missing. I can’t add them via preferences, and I can’t even select them from with Acrobat and force them to open. Acrobat just would not open those Excel files.

Automator is the mini-program on your mac that looks like a little robot. He looks like Apple’s version of the robot from the movie Short Circuit. With Automator, you can set up a Custom Workflow, which is fantastic. Double-click Workflow, and seemingly nothing will happen. That is fine, because now we need to decide what we want to do. To Mass convert Excel files to PDF, click on Documents in the left column. Look for Convert Format of Excel Files.

Click and drag it to the right column, where a dialog box will appear, asking you which format you’d like to convert your Excel files to. Choose PDF from the drop down menu.

Then, go to File>Save. You can save the Automator App icon anywhere, such as a folder, or your desktop. When I drag and drop the files onto the icon, it will save the results in the original folder that the documents came from.

I ran all 500+ files through the Automator function that I’d created, and walked away to perform another task for a while (maybe 10-15mins), and when I came back, Automator had saved each file as a pdf in its own folder with the same name as the document. This was handy, because each document was already named the part number of the product. Each pdf was named the corresponding part number as well. To select all of the pdf files, I chose the main folder that they were in, and in the search bar in the finder window, I typed .pdf. Above the list of files were options to narrow down the search. I chose just the main folder and chose to sort the list by kind, which grouped all of the pdf files together. Then I moved all of the pdf files into one main folder.

From there, I could type the part number, bringing me  right to the pdf that I needed. When I opened it, I could single-click on the image, copy it, and then paste it into Photoshop. The best part is that after all of this, when I pasted from Acrobat into Photoshop, there were no longer any strange borders on my images. They were all at full size on white backgrounds, just as in the pdf files.

If you think about the time it would have taken me to manually open the Excel files, copy the image, paste, crop or remove the borders, and further edit each image, I saved roughly 3 minutes per image. Multiple that by 500 images and that is 1500 minutes. That translates into 25 hours I would have spent on the job, worst-case scenario. Talk about a real time saver! That is 25 hours more I could spend making money doing something way less boring than opening Excel spreadsheets.