Shulou(Shulou.com)06/01 Report--

This article shows you how to write the code for inserting batch pictures in Excel, the content is concise and easy to understand, and it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

What we share today is to insert pictures into Excel worksheets in batches according to the specified name and location.

Take a look at the animation first:

The code is as follows

Sub InsertPic () 'ExcelHome VBA programming Learning and practice by: seeing Starlight Dim Arr, iTunes, Knights, nails, pd& Dim strPicName$, strPicPath$, strFdPath$, shp As Shape Dim Rng As Range, Cll As Range, Rg As Range, strWhere As String' On Error Resume Next 'users choose the folder where the pictures are located With Application.FileDialog (msoFileDialogFolderPicker) If .Show Then strFdPath = .SelectedItems (1) Else: Exit Sub End With If Right (strFdPath) 1) "\" Then strFdPath = strFdPath & "\" Set Rng = Application.InputBox ("Please select the range of cells in which the picture name is located", Type:=8) 'the user selects the range of cells in which the name of the picture needs to be inserted Set Rng = Intersect (Rng.Parent.UsedRange, Rng)' intersect statement prevents the user from selecting the entire column of cells. Cause unnecessary operation If Rng Is Nothing Then MsgBox "the selected cell range does not have data!" : Exit Sub strWhere = InputBox ("Please enter the offset position of the picture, such as top 1, bottom 1, left 1, right 1", "right 1") 'the user enters the offset position of the picture relative to the cell. If Len (strWhere) = 0 Then Exit Sub x = Left (strWhere, 1) 'offset direction If InStr ("up and down", x) = 0 Then MsgBox "you did not enter the offset direction." : Exit Sub y = Val (Mid (strWhere, 2)) 'offset value Select Case x Case "Set Rg = Rng.Offset (- y, 0) Case" Set Rg = Rng.Offset (y, 0) Case "left" Set Rg = Rng.Offset (0,-y) Case "right" Set Rg = Rng.Offset (0 Y) End Select Application.ScreenUpdating = False Rng.Parent.Select For Each shp In ActiveSheet.Shapes' delete If Not Intersect (Rg, shp.TopLeftCell) Is Nothing Then shp.Delete Next x = Rg.Row-Rng.Row y = Rg.Column-Rng.Column 'offset coordinates Arr = Array (".jpg", ".jpeg", ".bmp" if the old picture is stored in the target picture storage area ".png" ".gif") 'record five file formats with array variables For Each Cll In Rng' traverse each cell in the selected region strPicName = Cll.Text 'picture name If Len (strPicName) Then' if the cell exists the value strPicPath = strFdPath & strPicName 'picture path pd = 0 'pd variable tag whether to find the related picture For I = 0 To UBound (Arr)' due to uncertainty about the user's picture format So traverse the picture format If Len (Dir (strPicPath & Arr (I) Then'if there is a related file Set shp = ActiveSheet.Shapes.AddPicture (_ strPicPath & Arr (I), False, True, _ Cll.Offset (x, y). Left + 5 _ Cll.Offset (x, y). Top + 5, _ 20, 20) shp.Select With Selection .ShapeRange.LockAspectRatio = msoFalse 'unlock the picture aspect ratio .height = Cll.Offset (x Y) .Height-10 'picture height. Width = Cll.Offset (x Y) .Width-10 'picture width End With pd = 1' mark finds the result n = n + 1 'accumulates the number of results found [A1] .Select: Exit For' you can exit the file format loop End If after finding the result Next If pd = 0 Then k = k + 1'if the cumulative number of pictures is not found End If Next Application.ScreenUpdating = True MsgBox "co-processing succeeded" & n & "pictures Another "& k &" non-empty cell did not find the corresponding picture. " End Sub

The code has been annotated, so let's explain the running process again.

First of all, the user will be asked to choose the folder where the pictures are stored. Note that you choose a folder, not a picture; after selecting a folder, it is normal not to see the pictures in the folder.

Then, select the range of cells in which the picture name is stored, and you can select the whole column, multiple columns, entire rows, or multiple rows, such as column D in the sample animation, 2:2 row, and so on.

Finally, set the cheap position of the picture relative to the cell where the picture name is located; for example, if the picture name is in column B and the picture is placed in column C, that is to offset 1 column to the right, that is, 1 to the right. Up and down, left and right, represent the direction, and the number represents the amount of offset.

Tips:

This small code supports the following image formats: ".jpg", ".jpeg", ".bmp", ".png", ".gif"

The aspect ratio of the picture is not locked. If you want to lock it, you can comment out the following code: .ShapeRange.LockAspectRatio = msoFalse

The row height and column width of the picture are determined by the row height and column width of the cell in which the picture is placed. Of course, you can also modify the code slightly to set a fixed row height and column width of the picture, and the cell size is determined by the row height and column width of the picture.

The name of the picture must be the same as the name within the specified range of cells. If you need fuzzy matching, use the DIR function to match wildcards.

The code inserts the picture in a non-referenced way, which packages the picture into the excel file as a resource of excel, and the picture in the table still exists even if the picture of the data source has been deleted.

The above is how to write the code for inserting batch pictures into Excel. Have you learned the knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.