MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

To find .XLS file in a folder

  Asked By: Alonsa    Date: Feb 01    Category: MS Office    Views: 1099

Can some one please help me in suggesting how to find, if an excel file is
existing in a particular folder using VBA. I just want to find a file with
extension .xls and not by any name of file.

I have been trying for sometime now, but I couldnot succeed.



3 Answers Found

Answer #1    Answered By: Clinton Edwards     Answered On: Feb 01

please post what you have so far, so we can have a look at it.

Answer #2    Answered By: Adelaide Fischer     Answered On: Feb 01

You need to add Microsoft Scripting Library to your references for this
to work.

Dim fso As FileSystemObject

Dim objFolder As Folder

Dim objFile As File

Dim strName As String

Set fso = New FileSystemObject

Set objFolder = fso.GetFolder("C:\MyDocs")

For Each objFile In objFolder.Files

strName = objFile.Path

If Right(strName, 4) = ".xls" Then

MsgBox "It's there!"

End If


Answer #3    Answered By: Blake Smith     Answered On: Feb 01

With Application.FileSearch
.FileType = msoFileTypeExcelWorkbooks
'.LookIn = "C:\Data"
.LookIn = UserForm1.txtLocation.Text
.SearchSubFolders = True
.Execute SortBy:=msoSortByFileType
If .FoundFiles.Count Then
UserForm1.Label4.Caption = .FoundFiles.Count
For lFile = 1 To .FoundFiles.Count
On Error GoTo myerrhand
If UCase(Right(.FoundFiles(lFile), 3)) = "XLS" Then
Workbooks.Open Filename:=.FoundFiles(lFile), UpdateLinks:=0
xbook = ActiveWorkbook.Name
If UCase(xbook) <> "STDRPT1.XLS" Then
Application.StatusBar = "Now working on " &
'Here is the line that calls the macro below, passing the
workbook to it
Application.DisplayAlerts = False
End If
End If

Next lFile
End If
End With

Didn't find what you were looking for? Find more on To find .XLS file in a folder Or get search suggestion and latest updates.