First, the premise condition
In the Excel cell, you have entered the person's name, and, under the name, leave a blank cell to insert the appropriate picture.
Like the following figure. For example, B1 is the name, and B3 will automatically insert John's photo into B3 according to John's name. others, etc.
This has to be done using VBA.
At the same time, the person's photo is located in the folder, and Excel works thin, in the same path, for example, below the figure position.
In addition, the name of each employee's photo is named according to the employee's name, as shown below.
Problems like this need to have a certain regularity, so you can use VBA to complete.
Ii. Methods of implementation
Open your Excel, and then perform menu actions: "Tools" → "Macros" → "Macros", pop-up the following Figure dialog box.
In the picture above, the macro name there, enter Autoaddpic, then click the "Create" button, pop-up Code input window, the following figure.
Code as shown above, please write complete, otherwise an exception will occur. For the convenience of everyone's study, the following code is written as follows for reference:
' Delete all pictures before inserting pictures automatically
For each Shp in Activesheet.shapes
If Shp.type = msopicture Then shp.delete
Dim Mypcname as String
For i = 1 to ThisWorkbook.ActiveSheet.UsedRange.Rows.Count
If (ActiveSheet.Cells (i, 1). Value = "name") Then
Mypcname = ActiveSheet.Cells (i, 2). Value & ". gif"
The full path to the ' MsgBox ' picture is "& Thisworkbook.path & Employee Photos" & Mypcname
ActiveSheet.Cells (i + 2, 2). Select ' Choose the cell to insert the picture as the target
Dim MyFile as Object
Set MyFile = CreateObject ("Scripting.FileSystemObject")
If myfile.fileexists (Thisworkbook.path & employee Photos & mypcname) = False Then
MsgBox Thisworkbook.path & "Employee Photos" & mypcname & "No pictures exist"
' Insert a picture in the selected cell
ActiveSheet.Pictures.Insert (Thisworkbook.path & "Employee Photos" & Mypcname). Select
After writing the code, click Save in the window, and then close the Code window and return to the Excel window.
Next, perform menu action: "Tools" → "Macros" → "Macros", pop-up as shown below.
Select the macro name autoaddpic that you created above, and then click the "Execute" button, so that Excel will find the corresponding photo according to each name and insert the photo into the corresponding cell for each person.
Third, knowledge expansion
ThisWorkbook.ActiveSheet.UsedRange.Rows.Count This line of code means to get the largest row of valid data in a worksheet.
If (ActiveSheet.Cells (i, 1). Value = "Name" to determine whether the rows in the first column, the contents of the word "name", is the name is to find the picture to insert, otherwise not find.
Mypcname = ActiveSheet.Cells (i, 2). Value & ". gif" gets each person's photo name, such as Castle Peak. gif
Thisworkbook.path & Employee Photos & Mypcname Gets the path to each person's photo, is the complete absolute path, not the relative path.
ActiveSheet.Cells (i + 2, 2). Select ' Selects the cell to which you want to insert a picture as the target, that is, which cell you want to insert into the picture, select which
ActiveSheet.Pictures.Insert (Thisworkbook.path & "Employee Photos" & Mypcname). Select ' Inserts a picture in the selected cell
If Myfile.fileexists (Thisworkbook.path & employee Photos & mypcname) = False Then To determine whether an employee photo exists