CODEWITHSUNDEEP
vbscript
user3086267
user3086267

Reputation: 41

How to retrieve files of different extensions in VB Script?

Hi I have to write a code where I can enter the extension of file I want and pull files of those extension only into my excel.The folder contains files of various extension. What I have written till now is user can enter * if he wants all files .But what I have to do exactly is user can enter .txt/.pdf,etc and pull only those fils.Plz help.

Do while x=0
strAnswer = InputBox("Please enter the file extension  *  For all files:", _
    "File Extension")
If strAnswer = "" Then
        MsgBox"You must enter an extension."
Else
        a=strAnswer
        Exit Do
    End If
Loop
If a="*" Then
intRow = 2
'strFileName = "T:\public\Madhumita\New.xls"
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
'objWorkbook.SaveAs(strFileName)
objExcel.Cells(1, 1).Value = "Folder"
objExcel.Cells(1, 2).Value = "File Name"
objStartFolder = "T:\public\Madhumita\Madhu"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(objStartFolder)
Set colFiles = objFolder.Files
For Each objFile in colFiles
objExcel.Cells(intRow, 1).Value = objfolder.Name
objExcel.Cells(intRow, 2).Value = objFile.Name
intRow = intRow + 1
Next
objExcel.Range("A1:B1").Select
objExcel.Selection.Font.Bold = True
objExcel.Cells.EntireColumn.AutoFit
Sub SaveAs() 
    Application.Dialogs(xlDialogSaveAs).Show 
End Sub 
End If


objExcel.Quit
MsgBox "Done"

Upvotes: 0

Views: 219

Answers (1)

MC ND
MC ND

Reputation: 70933

Use objFSO.GetExtensionName( objFile.Path ) to get the extension of the file. Compare with the value you user provided, if equal then add to sheet.

Upvotes: 1

Related Questions