|re: "I tried your second formula in macros "|
Just to make sure we are using the correct terminology, my second suggestion is called a "macro", not a "formula in macros"
re: "it only opened a new spread sheet"
If you didn't use the correct path to the files or if the files were not jpg files then the code will indeed only create a new sheet and not do anything else. Well, it will actually do other things, such as look for the jpg files in the specified path, but if they are not found, nothing will be inserted.
As for your request, the following code inserts each picture from the specified path and places it in a cell in Column A.
The exact name of the file, without the extension, must be in Column B.
As written, the code loops through B1:B4, picking up the file name, appending .jpg to the name and then inserting the picture into the corresponding row in Column A. It also resizes the picture to sort of fit in the cell. By “sort of” I mean that the pictures end up to be the size of a standard Excel cell. You’ll have to play with those settings to get you pictures to be the size that you want.
If you paste the code into the VBA editor, change the Path to match the path to your folder and then use F8 to single step through the code, you should be able to see each picture as it is added and then resized.
I suggest that you experiment with just a few pictures instead of having it run through 100 pictures and then find out that they are all the wrong size, etc.
A tip: If, during your experimenting, you want to delete a number of pictures all at once, press F5, choose Special, then Objects. This will select all of the pictures and you can press Delete to get rid of all of them.
Here’s the code:
'Loop through Rows 1 - 4
For nxtPic = 1 To 4
'Insert jpgs based on text in Column B
("\\pcg\root\Users\user_name\My Pictures\" & _
Cells(nxtPic, 2) & ".jpg").Select
' Move the picture to the cell and resize
.Left = Cells(nxtPic, 1).Left
.Top = Cells(nxtPic, 1).Top
.ShapeRange.LockAspectRatio = msoFalse
.ShapeRange.Height = 20#
.ShapeRange.Width = 40#
.ShapeRange.Rotation = 0#
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.