none
Insert multiple objects (pdf files) to an Excel worksheet RRS feed

  • Question

  • I am looking for a macro to add all of the pdf files in a particular folder to a worksheet as icons in the active cell and the cells directly below it. For example, if cell E12 is active and the folder contains 4 pdf files, I would like the icons added to E12, E13, E14, E15. The macro does not need to filter out other file types because I have in mind to add the pdf files to the target folder and then move them after being inserted into the worksheet. The target folder will NOT contain the Excel workbook - only the pdf files. Any ideas?

    Thanks!


    • Edited by MBAEric Thursday, June 4, 2015 1:48 PM
    • Moved by George123345 Friday, June 5, 2015 5:12 AM
    Thursday, June 4, 2015 1:45 PM

Answers

  • Hi MBAEric,

    >> I am looking for a macro to add all of the pdf files in a particular folder to a worksheet as icons in the active cell and the cells directly below it.

    There are two steps to achieve your requirement.

    Step1: get the pdf files in the folder, you could loop through all files, the link bellow shows more details.
    # Macro to Loop Through All Files in a Folder (Directory)
    https://support.microsoft.com/en-us/kb/139724?wa=wsignin1.0

    Step2: add the OLEObject to excel with the icon shown and setting the position, you could use the OLEObjects.Add Method
    # OLEObjects.Add Method (Excel)
    https://msdn.microsoft.com/EN-US/library/office/ff195728.aspx

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, June 8, 2015 7:22 AM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    George Zhao
    TechNet Community Support


    It's recommended to download and install Configuration Analyzer Tool (OffCAT), which is developed by Microsoft Support teams. Once the tool is installed, you can run it at any time to scan for hundreds of known issues in Office programs.

    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.
    Friday, June 5, 2015 5:12 AM
  • Hi MBAEric,

    >> I am looking for a macro to add all of the pdf files in a particular folder to a worksheet as icons in the active cell and the cells directly below it.

    There are two steps to achieve your requirement.

    Step1: get the pdf files in the folder, you could loop through all files, the link bellow shows more details.
    # Macro to Loop Through All Files in a Folder (Directory)
    https://support.microsoft.com/en-us/kb/139724?wa=wsignin1.0

    Step2: add the OLEObject to excel with the icon shown and setting the position, you could use the OLEObjects.Add Method
    # OLEObjects.Add Method (Excel)
    https://msdn.microsoft.com/EN-US/library/office/ff195728.aspx

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, June 8, 2015 7:22 AM
  • Hi MBAEric,

    I have written a VB Code for this requirement. Here is the code pasted below. Enjoy it.

    '============================================================

    Sub Insert_Objects()
    '
    ' Insert_Objects Macro
    '
    ' Keyboard Shortcut: Ctrl+Shift+O
    '
        Dim objFSO As Object
        Dim objFolder As Object
        Dim objFile As Object
        Dim ws As Worksheet
        Dim MyRow As Integer
        Dim MyCol As Integer
        Dim CurrentFile As String
        
        Set objFSO = CreateObject("Scripting.FileSystemObject")
        Set ws = ThisWorkbook.ActiveSheet
        
         'Get the folder object associated with the directory
       
    Set objFolder = Application.FileDialog(msoFileDialogFolderPicker)
    With objFolder
    .Title = "Select a Folder"
    .AllowMultiSelect = False
    .InitialFileName = strPath
    If .Show <> -1 Then GoTo NextCode
    sItem = .SelectedItems(1)
    End With
    NextCode:
    GetFolder = sItem
       
       
        Set objFolder = objFSO.GetFolder(sItem)
         'Loop through the Files collection
        MyRow = ActiveCell.Row
        MyCol = ActiveCell.Column
        For Each objFile In objFolder.Files
        ws.Cells(MyRow, MyCol).Value = objFolder & "\" & objFile.Name
          
        CurrentFile = ws.Cells(MyRow, MyCol).Value
        ActiveSheet.OLEObjects.Add(Filename:= _
        CurrentFile, Link:= _
        False, DisplayAsIcon:=True, IconFileName:= _
        "C:\WINDOWS\system32\packager.dll", IconIndex:=0, IconLabel:="").Select
        Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(1, 0)).Select
                       
        Next
        
         'Clean up!
        Set objFolder = Nothing
        Set objFile = Nothing
        Set objFSO = Nothing
    End Sub

    '=====================================================================

    'Please contact me on Skype ID "er.bhupy" for any query

    Monday, October 2, 2017 2:00 PM