Thursday, 2 May 2013

Getting Images in an Excel Sheet from a folder

Now moving a bit towards programming .....

So somebody asked me "If he has to get hundreds of pictures from a folder and arrange them in a column" when the names of pictures are already mentioned in the file ... should he spend his entire day doing that??

The answer is NO... Why not to automate the entire process .. Yes It can be , the solution is as below:

Step1 : List the names of Pictures in a single column (See depiction below for example)


Step2 : Now at the back-end, write this code


Sub Get_Images()

Dim FolderName As String
'statements to select Folder
With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
    If .Show = -1 Then
 
        FolderName = .SelectedItems(1)
    End If
End With

LstData = Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))

For i = 2 To LstData
nm = Sheet1.Range("a" & i) 'statement to get the names of images from sheet
Rows(i & ":" & i).RowHeight = 111
ActiveSheet.Pictures.Insert(FolderName & "\" & nm & ".jpg").Select
Selection.ShapeRange.Width = 100
Selection.ShapeRange.Height = 95
Selection.Cut
Range("C" & i).Select         'Pictures will be arranged in column C
ActiveSheet.Paste
Next i

End Sub

Step3 : Run the code by pressing F5

And the Task is accomplished.....


Keep Rocking !!! All the best ....