Import files into the excel with same prefix

[Rahulwork]

Hi Everyone,

I am struggling to create a macro which will import all files from predefined folder having same prefilx. Kindly refer below for more details:

C2 = ShareDrive link in which i have files
Files Name = Sample 1.pdf, Sample 2.pdf, Sample 3.pdf

I have below code by which i can upload predefined files into the excel as object:

Sub Button2_Click()


Dim strPath As String
Dim strFilename As String
Dim strCaption As String
Dim wksTarget As Worksheet
Dim rngTarget As Range
Dim wFiles As Variant
Dim wCells As Variant
Dim i As Long

Set wksTarget = Worksheets("Sheet1")

strPath = wksTarget.Range("C2").Value
If Right(strPath, 1) <> "" Then
strPath = strPath & ""
End If

wFiles = Array("sample 1.pdf", "sample 2.pdf", "sample 3.pdf")
wCells = Array("E7", "F7", "G7")

For i = LBound(wFiles) To UBound(wFiles)

strFilename = wFiles(i)
If Len(Dir(strPath & strFilename, vbNormal)) = 0 Then
MsgBox "'" & strPath & strFilename & "' does not exist!", vbExclamation, "Path and/or file?"

Else

strCaption = strFilename '"myCaption" 'change the caption as desired
Set rngTarget = wksTarget.Range(wCells(i))

wksTarget.OLEObjects.Add _
Filename:=strPath & strFilename, _
link:=False, _
displayasicon:=True, _
iconfilename:="", _
iconindex:=0, _
iconlabel:=strCaption, _
Left:=rngTarget.Left, _
Top:=rngTarget.Top, _
Width:=150, _
Height:=10




End If
Next

MsgBox "End"
End Sub



My query: i want to amend this code and make it possible that, if i click on button all files starts with "Sample" will get upload into the excel and there location would be E7...E9...E11 so on.

Please help me and let me know if there is any questions on it.

 

[John_w]

Try this macro:

Public Sub Insert_PDFs_As_Object()

    Dim folderPath As String, fileName As String
    Dim destCell As Range
    
    With Worksheets("Sheet1")
        folderPath = .Range("C2").Value
        Set destCell = .Range("E7")
    End With
   
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"
    
    Application.ScreenUpdating = False
    fileName = Dir(folderPath & "Sample*.pdf")
    Do While fileName <> vbNullString
        destCell.Worksheet.OLEObjects.Add _
            fileName:=folderPath & fileName, _
            Link:=False, _
            DisplayAsIcon:=True, _
            IconFilename:="", _
            IconIndex:=0, _
            IconLabel:=fileName, _
            Left:=destCell.Left, _
            Top:=destCell.Top, _
            Width:=150, _
            Height:=10
        Set destCell = destCell.Offset(2)
        fileName = Dir
    Loop
    Application.ScreenUpdating = True
    
    MsgBox "Finished"
    
End Sub

Remember, code tags.

 

[Rahulwork]

Hi John,

Thanks for your response. Can you please help me that how can i put the above code on button click?

 

[Joe4]

Can you please help me that how can i put the above code on button click?

See here: https://support.office.com/en-us/ar...l-button-d58edd7d-cb04-4964-bead-9c72c843a283

 

[Rahulwork]

thanks @Joe4 and @John_w for your help.

Above code is working fine but what if i need documents in E7, F7 G7.....and so on.

which part i have to amend ..please help

 

[John_w]

Change this:

Set destCell = destCell.Offset(2)

to:

Set destCell = destCell.Offset(,1)