Passing variable from MS to VBA in excel

Hints, tips and tricks for newbies

Moderators: Dorian (MJT support), JRL

Post Reply
nodochau
Pro Scripter
Posts: 131
Joined: Wed Jan 16, 2019 12:59 pm

Passing variable from MS to VBA in excel

Post by nodochau » Fri Apr 10, 2020 6:42 pm

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

User avatar
Dorian (MJT support)
Automation Wizard
Posts: 1348
Joined: Sun Nov 03, 2002 3:19 am
Contact:

Re: Passing variable from MS to VBA in excel

Post by Dorian (MJT support) » Sat Apr 11, 2020 1:31 pm

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.
Yes, we have a Custom Scripting Service. Message me or go here

Post Reply
Sign up to our newsletter for free automation tips, tricks & discounts