Import files into the excel with same prefix

Rahulwork

Active Member
Joined
Jun 9, 2013
Messages
284
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.
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,416
Try this macro:
Code:
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

Active Member
Joined
Jun 9, 2013
Messages
284
Hi John,

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

Rahulwork

Active Member
Joined
Jun 9, 2013
Messages
284
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,416
Change this:
Code:
Set destCell = destCell.Offset(2)
to:
Code:
Set destCell = destCell.Offset(,1)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,816
Messages
5,525,064
Members
409,617
Latest member
Lenaf

This Week's Hot Topics

Top