Passing variable from MS to VBA in excel

[nodochau]

Hello All,
I have multiples pictures and they need to be inserted to cells in excel. So I created a vba code within my sheet.
Here is my code:
Sub insert_pics()
Dim ws As Worksheet
Dim picture1 As Picture
Dim i As Integer
Set ws = ActiveSheet
For i = 2 To 6 // 6 will be set by value by MS
Set targetCell = ws.Cells(i, 1)
Set picture1 = ws.Pictures.Insert("C:\Users\nodoc\Pictures\OIP.bmp") // this file name will be set by MS

With picture1
.Height = targetCell.Height - 15
.Top = targetCell.Top + (targetCell.Height - .Height) / 2
.Left = targetCell.Left + (targetCell.Width - .Width) / 2
End With
Next i
End Sub
The question is how to pass the number of pictures and and the picture file name from MS (found by looping through the Picture Folder) to the vba loop ?

Thanks for your help

[Dorian (MJT support)]

The best way is to record an Excel Macro so you have the VBA code. Then edit it to use variables the same way we did in your other thread.

Code: Select all

Set picture1 = ws.Pictures.Insert("C:\Users\nodoc\Pictures\OIP.bmp") // this file name will be set by MS

Taken directly from your other thread :

Code: Select all

Set picture1 = ws.Pictures.Insert("%ImageLocationIn%\%FileNameOnly%")

And if you want to post text (file name, or count) to a cell, simply use XLSetCell - no vba required.