Help

Re: BULK IMAGE DOWNLOADER and RENAMER using excel vba

7934 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Vincent_Tang
6 - Interface Innovator
6 - Interface Innovator

I know a lot of you including myself on the forums wish there was a way to attach dropbox image links so you can maintain your digital assets locally such as this post:

Or even renaming uploaded files (in bulk though)

Or even download bulk image assets (which I had posted recommended Extreme picture finder downloader)

Or downloading an entire snapshot of data

Or do advanced image batch processing locally on whatever program you use for editing (Photoshop, ifranview, etc) while using airtable for asset staging

Or using something like http://excel-image-assistant.com/ to create spreadsheets with embedded images / more custom flexibility in printing forms

Well this is a solution for all of those problems! And much more! Because we’re going to bring the power of Excel VBA and windows API to do the work for us (Sorry Mac users this may not apply…)



What exactly is the solution…?

Basically, your going to have the following data in your airtable

  • Col 1: PrimaryKey
  • Col 2: ONE image link attachment

Here’s a data sample of it

Then you run a macro. The end result to look like this on your computer:

6c1fe82bbc8af717ab9b770574f78aa1101079e4.png

Where one row of data in airtable = one picture

And each picture is based off the name in the first column

Problem with current software out there to do this (you can skip this)

Before I get into the excel VBA macro, I should highlight what things I tried out before hand and why they don’t work

To do this properly, you need to have two different things

  • A bulk renamer
  • A bulk downloader

I tested out practically almost every software program out there for bulk renaming (Bulk rename utility, Advanced Renamer, Renamer) and bulk downloading (bulk image downloader, ExtremePictureFinder) but the problem is the following:

Say we go back to the original data. With pictures of dogs and stuff. Say you downloaded the CSV for that sample data

9919d311d8efe73abbd7e0eee258935e45fd7e63.png

The data looks like this in excel

79a60cc6e4724cd6673c7e6b37fd63557fbf5701.png

If you look at column B, that URL (HTTPS) is the image server where we’re going to download our image assets

BUT, with any of those above programs (bulk renaming / downloading) is that it doesn’t really let you save duplicated copies, because what’s going to happen is on your computer you will have 3 files with the same name, making renaming VERY difficult if not impossible.

Not only that you have to download , setup / run multiple programs which is a lot of work

Trust me here in that I’ve tested this out thoroughly. There’s only 2 ways to accomlpish this problem:

  1. Either with python
  2. Excel VBA

Most people won’t have python installed on their computer, myself included, but everyone knows how to use excel. Getting access to excel VBA and running a macro isn’t all that difficult, and I will explain how



#The solution

What does the solution even look like?

e28804fb1cfe4f1e6f5efe075f6b04e83ba1bf0a.gif

That all it is. It literally did the following all in 10 seconds

  • Asks if you want to run the macro and for a subfolder name
  • Iterated through each row
  • Extracted out the airtable download link
  • Fixed any image naming problems (e.g. if the image had in name)
  • Downloaded each image link
  • Created a windows .bat file to do all the renaming [this is for windows only sorry :frowning: ]
  • Showed whether the file downloaded successfully
  • Duplicated the new image names into a subfolder
  • Show whether the images successfully renamed into subfolder in command prompt

And this is the result!

abcb4cf682f19a086d7936ca416a13231a736857.png

When you look at the data you’ll see the following folder information:

7c1ee811c9164127a56cc413a6a1731c650b7cee.png

When you look at the excel CSV file after, you’ll see the following columns of data

  • Col A: Original Column 1 in airtable
  • Col B: Airtables Image link
  • Col C: Fixed formatting of the airtable’s image link
  • Col 😧 Batch file commands for duplicating image
  • Col E: Whether the image downloaded successfully or not

You’ll also see a file called “newcurl.bat” . Its just a random default name for a windows batch file I gave it. It does all the duplicating + renaming of assets. It basically has all of column D’s files in there



Cool where can I get the script and how do I run it?

Go my github page where I wrote macro. I put the latest iteration there

The file is called “DataStager.VBS”

  1. Copy the contents of it
  2. Download and Open your excel file from Airtable if you haven’t (don’t copypaste cells, use the builtin -download CSV button)
  3. Press ALT+F11
  4. Insert code into a module
  5. Run the submodule airtableCleaner()

ALTERNATIVELY

  1. just download my macro enabled workbook (don’t worry there’s no viruses on it)
  2. Paste data in the first sheet (exactly the data you’d get from download as CSV from airtable)
  3. Home → view → view macro → airtableCleaner() → run

^ download link above

Image links where I can use this script?

You can use this script on any image link that doesn’t require a proxy (e.g. any website that highly discourages webscraping)

Basically

  • Anything on imgur.com works (which uses AmazonS3) works
  • Anything hosted by AmazonS3 works
  • Anything on reddit would work as well
  • Anything on airtable.com (which uses AmazonS3) works
  • Flickr, GoogleImages, etc

Applications of this excel macro

If you have a program that only integrates with an excel file (which is mostly everything) and not touch airtables API, well then this is for you

Downloading and renaming all your image assets could be useful in a number of ways.

  • Ecommerce FTP staging and uploading
  • Bulk uploading to ANKI flash cards for studying
  • Digitally managing assets (Digital Photography, photoshop, etc)
  • Catalog design using AdobeIndesign + Database integrations
  • Customized mailing templates using Microsoft word, etc
  • Customized excel file using http://excel-image-assistant.com/ to port in the images after
  • Bulk import contacts + image links into an email client
  • ERP (Enterprise resource planning) , PoS (point of sales system), etc
  • Push image data into an actual database (MySQL) for storage efficiency
  • and much more…

Those are just some of the examples on why you would want to use airtable to stage data + image assets, and then bulk process download later to integrate in whatever platform you want

You can use it to cross collaborate with remote teams this way as well, each person can easily download all image assets that they need, etc

Support for newer features / Issues?

I wrote the excel VBA code rather poorly here (But it works) and there’s still lots of room for improvement. I put it all on the issues section in github

you can also recommend features that you want (like multi image support for one cell)

Mac users unfortunately this will probably not working and I don’t have any intentions of making a MacOS equivalent (run bootcamp for windows instead)

A list of improvements that I might look into found here: Issues · vincentntang/AirtableExcelVBAmacro · GitHub

Examples include PDF download support, multiple image URLs per cell support, proxy support, etc

Other useful scripts

Since this goes from (airtable → excel), you can just go the other way (excel→airtable) with my BULK IMAGE UPLOADER here

In conclusion…

Using both this excel VBA macro + the other macro in the link, you have full control over reproducing any airtable you want and having a solid backup of all your assets

Not only by having the ability for your data to go both ways 100%, you can tap into powerful ways to transform data using excel, excel VBA, and excel plugins

Not to mention the ability to bulk process images as well (EXIF data remover, Resize images, apply backgrounds, watermark, etc) and then reupload it → use airtables API to push it into a wordpress site for instance.

The applications of using both macros I laid out here, + Airtable’s API gives pretty much the flexibility of doing anything you want really



TL ; DR (Too Long , Didn’t Read)

this is a bulk downloader and renamer

On your airtable, have the following data format

  • Column 1 = new image name to rename to
  • Column 2 = ONE image attachment link

airtable.com → find your table → “Download as CSV”

Download this excel macro enabled workbook below

Paste the contents of the airtable’s CSV into sheet 1 of the “AirtableBulkDownloaderRenamer.xlsm” workbook

On excel ribbon menu → Home → view → view macro → airtableCleaner() → run

You should now have all your image assets downloaded and renamed.

15 Replies 15

To fix the issue with images no longer renaming properly and being put in the new folder directory. Try replacing this code:

    'Image downloader to source folder
Call dlStaplesImages

'Make the batch files using row data col D
Call ExportRangetoBatch

With this new code:

    'Image downloader to source folder
Call dlStaplesImages

'Fix Column D urls
Columns("D:D").Select
Selection.Replace What:=".attachments*/*/*/", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

'Make the batch files using row data col D
Call ExportRangetoBatch

That fixes the issue for me. I didn’t dig into it deeply but maybe the download urls changed slightly. The urls in the batch file no longer match Column D so I pulled out the offending part of the url before doing the rename.

Moe
10 - Mercury
10 - Mercury

For less technical people, we’ve built an extension that allows you to download all your attachments as a one zip file with a few clicks.

Roman_Luks
4 - Data Explorer
4 - Data Explorer

I was getting errors with the VBA solution (both v1 and v2) so I’ve put together a simple python script to download all attachments.

The name in the first column is used as a name for corresponding attachment. File type of attachment is preserved. The names in the first column should to be unique otherwise they will get overwritten if it’s the same file type.

Your db:
Name | attachment
Bob | 4as3fd523sf.jpg
John | screenclip.png

Script downloads these files:
Bob.jpg
John.png

^^Feel free to fork and make changes

Dmitry_Z
4 - Data Explorer
4 - Data Explorer

Hi, thank you for the guide, I try to use it for a little different purpose, but I do have 2 columns with names and url’s. I run the macro, it creates the folder. Says downloaded successfully but the folder is empty. The image links are direct non protected links. Could you guide me on what am I doing wrong :slightly_smiling_face:

some time ago i did bulk attachment downloader - it downloads all attachment in a base adding 8-digit code to filename from link which can be used then for uploading, if needed.
Should be saved as .vbs Backup should be renamed to backup.csv and exist in the same folder

Option Explicit 
Dim fs,objTextFile,arrString,attach,attachEs,pos,pos2,arrStr,fName,fExt
Dim fnameWithCode,eightDigCode,downloadLink

set fs=CreateObject("Scripting.FileSystemObject")
set objTextFile = fs.OpenTextFile("Backup.csv")
Do while NOT objTextFile.AtEndOfStream
arrStr = split(objTextFile.ReadLine,",")
attachEs=Filter(arrStr,"https://dl.airtable.com")
    For each attach in attachEs
    pos=InStr(attach,"(https") '// extract filename
    fName=Trim(Left(attach,pos-6))
    fName=Replace(fName,Chr(34),"_") '// remove bad chare from fname
    fName=Replace(fName,".","")
    fExt=Mid(attach,pos-5,4)
    pos2=InStrRev(attach,"/")
    eightDigCode=Mid(attach,pos2-8,8)
    fnameWithCode=fName&"("&eightDigCode&")"&fExt
    downloadLink=Mid(attach,pos+1,Len(attach)-pos-1)
    Call Download(fnameWithCode,downloadLink)
    Wscript.Sleep 1000
    Next
Loop

objTextFile.Close
set objTextFile = Nothing
set fs = Nothing

Sub Download(filename,link)
dim xHttp: Set xHttp = createobject("Microsoft.XMLHTTP")
dim bStrm: Set bStrm = createobject("Adodb.Stream")
xHttp.Open "GET", link, False
xHttp.Send

with bStrm
    .type = 1 '//binary
    .open
    .write xHttp.responseBody
    .savetofile filename, 2 '//overwrite
end with
End Sub


This is a terrific idea. However, when attempting to use the macro, it encounters a problem with the subfolder name code. Error says “Run time 52” and the debugger goes to " If Dir(strDir, vbDirectory) = “” Then" It appears to be line 68 in your macro.

I have tried using a simple subfolder name as you suggest, ie, batches and also tried using the full path. Neither works. Both version one and version 2 of your macro get stuck at the same place. I used both my own macro-enabled spreadsheet and your downloaded spreadsheet.

I am using windows 10 (10.0.19044 Build 19044) and Excel build 2203.

Can you provide any suggestions for that line of code?