Upload document into the excel via VBA based on initial name

[Rahulwork]

Hello Everyone,

I wrote below code, this code upload all document from sharedrive into excel based on their naming convention.

However it's applicable for Pdf file only. can you please help me that how can i upload other ext file as well like outlook/word/excel?

Dim folderPath As String, fileName As String
Dim destCell As Range

With Worksheets("ABC")
folderPath = .Range("F7").Value
Set destCell = .Range("G12")
End With

If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"

Application.ScreenUpdating = False
fileName = Dir(folderPath & "ABC*.pdf")
Do While fileName <> vbNullString
destCell.Worksheet.OLEObjects.Add _
fileName:=folderPath & fileName, _
Link:=False, _
DisplayAsIcon:=True, _
IconFilename:="C:\windows\Installer\{AC76BA86-1033-F400-7760-000000000005}\_PDFFile.ico", _
IconIndex:=0, _
IconLabel:=fileName, _
Left:=destCell.Left, _
Top:=destCell.Top, _
Width:=150, _
Height:=5
Set destCell = destCell.Offset(, 1)
fileName = Dir
Loop
Application.ScreenUpdating = True

MsgBox "Document Uploaded Successfully..!!"

 

[rlv01]

It is not clear to me exactly what you are asking for help with. This is the line in your code that chooses certain pdf files:

fileName = Dir(folderPath & "ABC*.pdf")

If you want your code to use another file extension, then change that line. Example:

fileName = Dir(folderPath & "ABC*.docx")

Of course, for each new extension you will also need to specify a new icon file in this line:

IconFilename:="C:\windows\Installer\{AC76BA86-1033-F400-7760-000000000005}\_PDFFile.ico", _

 

[Rahulwork]

Thanks for response.

i want if in folder there is pdf and word file with ABC so i need both the document with their icon.

by above code it's coming only of pdf.

IconFilename:="C:\windows\Installer\{AC76BA86-1033-F400-7760-000000000005}\_PDFFile.ico", _

can you pls tell me how can i put multple iconfilename in code?

 

[rlv01]

The general approach would be to use a Select Case statement (not tested)

  Dim IconFile As String, FileExt As String
    Do While fileName <> vbNullString
        FileExt = UCase(Right(fileName, Len(fileName) - InStrRev(fileName, ".")))
        'select icon file depending on what the file extension is
        Select Case FileExt
        Case "PDF"
            IconFile = "C:\windows\Installer\{AC76BA86-1033-F400-7760-000000000005}\_PDFFile.ico"
        Case "DOC", "DOCX"
            IconFile = "C:\windows\Installer\{AC76BA86-1033-F400-7760-000000000005}\_PDFFile.ico"    '<- replace with word icon file on your PC that you want to use
        Case "TXT"
            IconFile = "C:\windows\Installer\{AC76BA86-1033-F400-7760-000000000005}\_PDFFile.ico"    '<- replace with text file icon file on your PC that you want to use
        End Select

        destCell.Worksheet.OLEObjects.Add _
        fileName:=folderPath & fileName, _
        Link:=False, _
        DisplayAsIcon:=True, _
        IconFileName:=IconFile, IconIndex:=0, IconLabel:=fileName, Left:=destCell.Left, Top:=destCell.Top, Width:=150, Height:=5
        Set destCell = destCell.Offset(, 1)
        fileName = Dir
    Loop