preface

My sister asked me to help her write a program to insert an attachment into Excel, otherwise she would have to work overtime during the May Day holiday, so I checked the information during the overtime. At first, I misunderstood the problem, thinking that it was to convert PDF into Excel, but I also thought that I would use Python to write, but later I found that it was to insert an attachment, which could be written using VBA.

Due to the popularity of Microsoft Office software, people’s common Office software Word, Excel, Access, Powerpoint can use VBA to make the application of these software more efficient, for example: through a SECTION of VBA code, can realize the switch of the screen; Can realize complex logic statistics (for example, from multiple tables, automatically generate statistical lists to track production, inventory, sales, inventory by contract number). (Baidu Encyclopedia)

Environment configuration

To use VBA, we need to turn on Excel’s developer tools

File -> Options -> Custom Ribbon -> Main menu (on the right) -> Select Development Tools -> OK

Go back to the home screen and click

Development tools -> macro -> macro name -> Enter batchImportObject -> Create -> Enter Visual Basic code editing area

Copy and paste the code, and select and overwrite the code you just created

Sub batchImportObject()
'Columns(1).Delete On Error Resume Next Dim f As String Dim file() As String Dim i, k, x x = 1 i = 1: K = 1 ReDim file(1 To I) file(1) = InputBox(" ") & "\" Do Until i > k f = Dir(file(i), vbDirectory) Do Until f = "" If InStr(f, ".") = 0 Then k = k + 1 ReDim Preserve file(1 To k) file(k) = file(i) & f & "\" End If f = Dir Loop i = i + 1 Loop For i  = 1 To k f = Dir(file(i) & "*.*") Do Until f = "" 'Range("a" & x) = f
ActiveSheet.OLEObjects.Add(Filename:=file(i) & f, _
Link:=False, _
DisplayAsIcon:=True, _
IconIndex:=0, _
Left:=10, Top:=10 + x, _
IconLabel:=file(i) & f).Select
x = 50 + x
f = Dir
Loop
Next
End Sub

Copy the code

Then save the file. For details about how to save the file, see here

The saved file type needs to be changed to a file with macros

Then go back to Excel and click

Macro -> Select batchImportObject -> enter the folder path of the files you want to import in the input box that pops up -> wait or delay an event -> theoretically done

Pay attention to

  • All files in the folder will be imported, so make sure they are the files you need to import *
  • If the import file is too large or the number of files is too large, the application may not respond, and it should be ok to wait a while *