Summary:
- Copy/paste from your Dropbox link to a private/incognito window;
- Select and copy/paste images to an Excel sheet;
- Copy/Paste the code provided under Step 6 to your Visual Basic code and run the Macro
Importing Direct Image Links from Dropbox can be a hassle. We wrote a script to automate this process. It can easily be run in Excel, using the Virtual Basic feature. We'll show you how.
Step 1
Navigate to the Dropbox folder containing your images and copy its Dropbox link.
Step 2
Open an incognito window in Google Chrome browser and paste your Dropbox link (copied in Step 1) into the address bar.
Step 3
Click and drag over all of the images in the folder to select them and press CTRL+C to copy them.
► If you are already using "Visual Basic Module" in Excel jump to Step 6.
Step 4: Access to Visual Basic Module
We will need to use a "Visual Basic Module" in the "Developer" tab.
To enable the "Developer" tab, click on:
File -> Options -> Customize Ribbon and check "Developer" in the list.
Close the window by clicking "OK".
Step 5: Open a Module
Click on the "Developer" tab which should now be visible at the top of the screen. Then Click "Visual Basic".
Click "Insert" at the top of the screen and select "Module" from the list.
Step 6
Copy and paste the entire following code in the Visual Basic Module box that appears:
(This code will perform 3 main actions: Remove the snapshots, Update the links, Update your spreadsheet)
Sub PDSConvertDropboxLinks() 'Runs all following Subs
RemOb
Rep
Rep2
RERs
RemHL
RelHL
Resz
Resp
End Sub
Sub RemOb() 'Removes objects
Dim shp As Shape
Dim sTemp As String
Dim HL As Hyperlink
Dim LastRowIndex As Integer
Dim RowIndex As Integer
Dim UsedRng As Range
For Each shp In ActiveSheet.Shapes
sTemp = ""
On Error Resume Next
sTemp = shp.Hyperlink.Address
On Error GoTo 0
If sTemp <> "" Then
shp.Delete
End If
Next
End Sub
Sub Rep() 'Replace HL
For Each HL In ActiveSheet.Hyperlinks
HL.Range.Offset(0, 0).Value = HL.Address
Next
For Each HL In ActiveSheet.Hyperlinks
fndList = Array("www.dropbox.com")
rplcList = Array("dl.dropboxusercontent.com")
For x = LBound(fndList) To UBound(fndList)
For Each sht In ActiveWorkbook.Worksheets
sht.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True, _
SearchFormat:=False, ReplaceFormat:=False
Next sht
Next x
Next
End Sub
Sub Rep2() 'Correct HL
For Each HL In ActiveSheet.Hyperlinks
fndList = Array("?dl=0")
rplcList = Array("")
For x = LBound(fndList) To UBound(fndList)
For Each sht In ActiveWorkbook.Worksheets
sht.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True, _
SearchFormat:=False, ReplaceFormat:=False
Next sht
Next x
Next
End Sub
Sub RERs() 'Removes Empty Rows
Set UsedRng = ActiveSheet.UsedRange
LastRowIndex = UsedRng.Row - 1 + UsedRng.Rows.Count
Application.ScreenUpdating = False
For RowIndex = LastRowIndex To 1 Step -1
If Application.CountA(Rows(RowIndex)) = 0 Then
Rows(RowIndex).Delete
End If
Next RowIndex
End Sub
Sub RemHL() 'Old Hl still present. Removes HL
ActiveSheet.UsedRange.Hyperlinks.Delete
End Sub
Sub RelHL() 'Insert New HL
ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:=""
End Sub
Sub Resz() 'Resize cells
Worksheets("Sheet1").Columns("A").AutoFit
End Sub
Sub Resp() 'Task Completed
MsgBox "Your Dropbox links are clean & ready for import"
End Sub
After pasting the code in the box, close the Visual Basic window.
Step 8
Run the module you just created by clicking "Macros" under the "Developer" tab, selecting "PDSConvertDropboxLinks" in the box that appears, and clicking "Run".
Now all the links should look like those below and point directly to your images. You can test that they're correct by copying one of the links and pasting it into your browser.
