How Excel VBA automatically inserts photos by name

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.

Computer Tutorials

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

Next

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"

Else

' Insert a picture in the selected cell

ActiveSheet.Pictures.Insert (Thisworkbook.path & "Employee Photos" & Mypcname). Select

End If

End If

Next I

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