Macro to Insert Objects from folders and move it to respective Sheets

[satish gubbi]

Hi Team,

I have request, is there any way we can insert multiple objects (PDF's) into excel and move it to respective sheets in the work book.

Please help

[MINCUS1308]

err I can do that for image files but a pdf? I don't know

[satish gubbi]

err I can do that for image files but a pdf? I don't know

Thanks for your reply, while browsing through, I found below code

Public Sub insertFile()
    Set Rng = Range("D3")  'Name the cell in which you want to place the attachment
    Rng.RowHeight = 56
    On Error Resume Next
    fpath = Application.GetOpenFilename("All Files,*.*", Title:="Select file", MultiSelect:=True)
    For i = 1 To UBound(fpath)
        Rng.Select
        Rng.ColumnWidth = 12
        ActiveSheet.OLEObjects.Add _
        Filename:=fpath(i), _
        Link:=False, _
        DisplayAsIcon:=True, _
        IconFileName:="excel.exe", _
        IconIndex:=0, _
        IconLabel:=extractFileName(fpath(i))
        Set Rng = Rng.Offset(0, 1)
    Next i
End Sub

Public Function extractFileName(filePath)
    For i = Len(filePath) To 1 Step -1
        If Mid(filePath, i, 1) = "\" Then
        extractFileName = Mid(filePath, i + 1, Len(filePath) - i + 1)
        Exit Function
        End If
    Next
End Function

Can we have code which would move the objects to different sheets based on the

[MINCUS1308]

Can we have code which would move the objects to different sheets based on the...

Most likely, but based on the what?

What is the criteria for selecting a specific worksheet?

 


'logic to determine which worksheet is correct

WsName = "" 'your worksheet name here

If Not Worksheets(WsName) Is Nothing Then  
'place the data to a specific cell here
else
msgbox "your ws does not exist"
End If