kilya

insert multiple Images using macro

May 27th, 2020
137
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Sub AddOlEObject()
  2.     Dim mainWorkBook As Workbook
  3.     Dim Folderpath As String
  4.     Dim fso, NoOfFiles, listfiles, fls, strCompFilePath
  5.     Dim counter
  6.    
  7.     Set mainWorkBook = ActiveWorkbook
  8.     Sheets("Sheet1").Activate
  9.     Folderpath = "your_path_here"   '<-- Change path to your images here
  10.    Set fso = CreateObject("Scripting.FileSystemObject")
  11.     NoOfFiles = fso.GetFolder(Folderpath).Files.Count
  12.     Set listfiles = fso.GetFolder(Folderpath).Files
  13.     For Each fls In listfiles
  14.        strCompFilePath = Folderpath & "\" & Trim(fls.Name)
  15.         If strCompFilePath <> "" Then
  16.             '// include image extensions here \\
  17.            If (InStr(1, strCompFilePath, "jpg", vbTextCompare) > 1 _
  18.             Or InStr(1, strCompFilePath, "jpeg", vbTextCompare) > 1 _
  19.             Or InStr(1, strCompFilePath, "png", vbTextCompare) > 1) Then
  20.                 counter = counter + 1
  21.                 Sheets("Sheet1").Range("B" & counter).ColumnWidth = 25
  22.                 Sheets("Sheet1").Range("B" & counter).RowHeight = 100
  23.                 Sheets("Sheet1").Range("B" & counter).Activate
  24.                 Call insert(strCompFilePath, counter)
  25.                 Sheets("Sheet1").Activate
  26.             End If
  27.         End If
  28.     Next
  29. 'mainWorkBook.Save
  30. End Sub
  31.  
  32. Function insert(PicPath, counter)
  33.     With ActiveSheet.Pictures.insert(PicPath)
  34.     '// change image sizes here \\
  35.        With .ShapeRange
  36.             .LockAspectRatio = msoTrue
  37.             .Width = 50
  38.             .Height = 70
  39.         End With
  40.         .Left = ActiveSheet.Range("B" & counter).Left
  41.         .Top = ActiveSheet.Range("B" & counter).Top
  42.         .Placement = 1
  43.         .PrintObject = True
  44.     End With
  45. End Function
Add Comment
Please, Sign In to add comment