Logo 
Search:

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: 1130
  

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.

Share: 

 

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

Next

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

With Application.FileSearch
.NewSearch
.FileType = msoFileTypeExcelWorkbooks
'.LookIn = "C:\Data"
.LookIn = UserForm1.txtLocation.Text
'.FileTypes
.SearchSubFolders = True
.Execute SortBy:=msoSortByFileType
If .FoundFiles.Count Then
UserForm1.Label4.Caption = .FoundFiles.Count
UserForm1.Repaint
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 " &
ActiveWorkbook.FullName
'Here is the line that calls the macro below, passing the
workbook to it
DoChanges
ActiveWorkbook.Save
Application.DisplayAlerts = False
Windows(xbook).Close
End If
End If

myerrhand:
Windows(ActiveWorkbook.Name).Activate
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.




Tagged: