GET pictures from a url and then rename the picture

Here is a sample which will help you.

I am assuming that your Excel file will look like this. Please amend the code as applicable.

Option Explicit

Private Declare Function URLDownloadToFile Lib "urlmon" _
Alias "URLDownloadToFileA" (ByVal pCaller As Long, _
ByVal szURL As String, ByVal szFileName As String, _
ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

Dim Ret As Long

'~~> This is where the images will be saved. Change as applicable
Const FolderName As String = "C:\Temp\"

Sub Sample()
    Dim ws As Worksheet
    Dim LastRow As Long, i As Long
    Dim strPath As String

    '~~> Name of the sheet which has the list
    Set ws = Sheets("Sheet1")

    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

    For i = 2 To LastRow '<~~ 2 because row 1 has headers
        strPath = FolderName & ws.Range("A" & i).Value & ".jpg"

        Ret = URLDownloadToFile(0, ws.Range("B" & i).Value, strPath, 0, 0)

        If Ret = 0 Then
            ws.Range("C" & i).Value = "File successfully downloaded"
        Else
            ws.Range("C" & i).Value = "Unable to download the file"
        End If
    Next i
End Sub

Rename Multiple Files using Excel in Windows

Ever been in the situation where you need to rename a bunch of files without giving them then same name with a number extension? Ever needed to rename a long list of images so they're search engine friendly? Well if the answer is yes, then your in for a real treat. There's a quick and easy solution to renaming files using Microsoft Excel (or any other spreadsheet software) and the windows command prompt.

For this example, I'll be renaming a folder of images so the name replaces spaces with dashes and adds a keyword to the end, making them more SEO friendly.

Here's how to go about doing this...

Step 1 - Generate Your File List

First thing is to get a list of all the files you want to rename. Start by opening the command prompt by clicking Start then typing cmd (in XP, hit run and then type cmd). This will open up the command prompt.

Then we need to change the current directory to the one we want. An easy way of doing this is to open the folder and copy the location. Got the command prompt, type cd and then right-click and hit paste. Hit enter and the directory we're working within will change to the one with all our images in.

Now we need to generate the list of images. Type this:

dir /b > _list.txt

This will create a list of all the files in the folder and export them to a new file called _list.txt. You can call this file what you want.

Step 2 :

  The fun starts now. Fire your copy of Microsoft Excel or Google Docs Spreadsheets and paste those file names into the first spreadsheet column.

Step 3:

In the blank adjacent column, add a corresponding Excel function – for instance, use SUBSTITUTE to change specific text in the filenames, use CONCATENATE() with DATE() if you want to add date to the filename, etc.

*For more complex criteria, you may want to put the file extensions in a separate column by splitting the file name using period (.) as the delimiter.

Step 5: Your source filenames are now in column A while the Destination files are in column B – we will now convert this into a DOS rename command.

In Column C, type the following Excel formula and your command is ready to be executed.

=CONCATENATE(“ren “,A1, ” “, B1)

Copy paste the same formula across all cells in column C for which you have corresponding values in Column A or B.

We are almost done. Copy all the values from column C to the clipboard and paste them inside the DOS window or better still, put them inside a new text file and give it a name like rename.bat.

Execute and all files that match the criteria are renamed instantly.

And that's it tadaa all renamed.

How to extract images from comments in Excel?

If you want to extract and save the images from comments into a folder, please do as follows:

1. Copy the sheet which contains the comments with images that you want to save into a new blank workbook.

2. And then click File > Save As to save this new workbook as Web Page format in a specific folder, see screenshot:

3. Then click Save button to save the file, then go to the specific folder to view the saved images, two files are generated in the folder, one is the html file, and another is a folder that contains all the images and other files, see screenshot:

---

Extract images from comments into cells with VBA code

If you want to extract the images from comments into cells as following screenshot shown, here, I can create a VBA code for you, please do as this:

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Extract images from comments into cells:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

Sub CommentPictures() 'Updateby Extendoffcie 20161207     Dim cmt As Comment     Dim xRg As Range     Dim visBool As Boolean     Dim cmtTxt As String     Dim jpgPath As String     Dim shpHeight As Integer, shpWidth As Integer     Application.ScreenUpdating = False     For Each cmt In ActiveSheet.Comments         With cmt             cmtTxt = .Text             shpHeight = .Shape.Height             shpWidth = .Shape.Width             .Text Text:="" & Chr(10) & ""             visBool = .Visible             .Visible = True             On Error Resume Next             Set xRg = .Parent.Offset(0, 1)             .Shape.CopyPicture _               Appearance:=xlScreen, Format:=xlPicture             xRg.PasteSpecial             Selection.ShapeRange.LockAspectRatio = msoFalse             Selection.Width = xRg.Width             Selection.Height = xRg.Height             .Visible = visBool             .Text Text:=cmtTxt         End With     Next cmt     Application.ScreenUpdating = True End Sub

3. Then press F5 key to run this code, and all the images of the comments in current worksheet have been extracted into the next column cells, see screenshot: