VBA-Insert multiple PDF files

[DoubleEntry]

Hi

I am new to VBA and I was hoping you could help me with the following.

I am trying to write a code to help me insert multiple (about 100) pdf files to different worksheets and name the worksheets as the pdf file they contain. Also create a linked index as the main page with the filenames linked to the worksheets.

All the pdf files are in one folder.

Hope you can help.

Kind regards,
DE

 

[ranman256]

not a good idea to insert pdf into a workbook, but how about inserting LINKS to the 100 pdfs?
and you can open the pdf with just a click.

Public Sub grabAllFilenames()
Dim xFileSystemObject As Object
Dim xFolder As Object
Dim xSubFolder As Object
Dim vFolderName As String, sLongFilename As String
Dim xFile


vFolderName = "c:\temp\"


Set xFileSystemObject = CreateObject("Scripting.FileSystemObject")
Set xFolder = xFileSystemObject.GetFolder(vFolderName)


Range("A1").Value = "File"
Range("A2").Select
For Each xFile In xFolder.Files
   if instr(xfile.name,".pdf")>0 then
   ActiveCell.Value = xFile.Name
   ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=xFile, TextToDisplay:=xFile.Name
  endif


   ActiveCell.Offset(1, 0).Select   'next row
Next


Set xFolder = Nothing
Set xSubFolder = Nothing
Set xFileSystemObject = Nothing
End Sub

 

[DoubleEntry]

Thank you ranman256. I need to embed the files as objects as the excel workbook will be sent to audit. But I will try this way.
Many thanks again.

DE

 

[JPM]

I made the provide code work:

Public Sub grabAllFilenames()
Dim xFileSystemObject As Object
Dim xFolder As Object
Dim xSubFolder As Object
Dim vFolderName As String, sLongFilename As String
Dim xFile

vFolderName = "C:\Users\Audit\Samples"
Set xFileSystemObject = CreateObject("Scripting.FileSystemObject")
Set xFolder = xFileSystemObject.GetFolder(vFolderName)

Range("A1").Value = "Files"
Range("a2").Select
For Each xFile In xFolder.Files
   If InStr(xFile.Name, ".pdf") > 0 Then
      ActiveSheet.OLEObjects.Add(Filename:= _
        xFile _
        , Link:=False, DisplayAsIcon:=True, IconFileName:= _
        """C:\Program Files (x86)\Adobe\Acrobat DC\Acrobat\Acrobat.exe""", IconIndex _
        :=0, IconLabel:=xFile.Name).Select
        
  End If

   ActiveCell.Offset(1, 0).Select   'next row
Next


Set xFolder = Nothing
Set xSubFolder = Nothing
Set xFileSystemObject = Nothing
End Sub