PDA

View Full Version : Macro to Insert Objects from folders and move it to respective Sheets



satish gubbi
03-12-2018, 09:28 AM
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
03-12-2018, 11:22 AM
err I can do that for image files but a pdf? I don't know

satish gubbi
03-12-2018, 10:17 PM
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
03-13-2018, 04:53 AM
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