VBA FIle Search

Question

Sub Main()
Dim FSO As New FileSystemObject
Dim Fl As File
Dim Folder As Folder
Dim F_Name, F_Path As String
F_Path = ThisWorkbook.Path & "\"
Set Folder = FSO.GetFolder(F_Path)
F_Name = "CI*.*"
For Each Fl In Folder.Files
    If Fl.Name = F_Name Then
        GoTo Report
    End If
Next

Report:
Workbooks.Open Filename:=F_Path & F_Name

I would like to open a excel file with same location but I know only part of file name so please assist how can I open the file name. Thanks!

you have to use Dir function to open file through wildcards. techonthenet.com/excel/formulas/dir.php — hackwithharsha
– hackwithharsha, Commented Feb 13, 2016 at 19:06
Try to change If Fl.Name = F_Name Then to If Fl.Name Like F_Name Then — Fadi
– Fadi, Commented Feb 13, 2016 at 19:07
This answer will gonna help you go through it stackoverflow.com/questions/19527415/… — hackwithharsha
– hackwithharsha, Commented Feb 13, 2016 at 19:10
Change F_Name to Fl.Name in this line Workbooks.Open .... — Fadi
– Fadi, Commented Feb 13, 2016 at 19:38

Fadi · Accepted Answer · 2016-02-14 06:31:20Z

1

Try:

Sub Main()
Dim FSO As New FileSystemObject
Dim Fl As File
Dim Folder As Folder
Dim F_Name, F_Path As String
F_Path = ThisWorkbook.Path & "\"
Set Folder = FSO.GetFolder(F_Path)
F_Name = "CI*.*"
For Each Fl In Folder.Files
    If Fl.Name Like F_Name Then
        GoTo Report
    End If
Next

msgbox "File not found"
exit sub

Report:
Workbooks.Open Filename:=F_Path & Fl.Name
End Sub

Edit:

To search using Dir :

Sub TestDir()

    Dim F_Path As String, F_Name As String, f As String

    F_Path = ThisWorkbook.Path & "\"
    F_Name = "CI*.*"

    f = Dir(F_Path & F_Name)

    If f = "" Then
     MsgBox "File not found"
    Else
     Workbooks.Open FileName:=F_Path & f
    End If

End Sub

edited Feb 14, 2016 at 6:31

answered Feb 13, 2016 at 19:43

Fadi

3,3223 gold badges21 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Developer_World Over a year ago

May I direct open the file with out searching ?

Fadi Over a year ago

@VBA_Coder, if you don't know the full name of the file I don't think you can open it without searching , but we can use Dir to search without looping (faster), see edit.

Developer_World · Accepted Answer · 2016-02-13 19:43:35Z

0

Sub Main()
Dim FSO As New FileSystemObject
Dim Fl As File
Dim Folder As Folder
Dim F_Name, F_Path As String
F_Path = ThisWorkbook.Path & "\"
Set Folder = FSO.GetFolder(F_Path)
F_Name = "CI*.*"
For Each Fl In Folder.Files
If Fl.Name Like F_Name Then
    GoTo Report
End If
Next

Report:
Workbooks.Open Filename:=F_Path & Fl.Name

answered Feb 13, 2016 at 19:43

Developer_World

632 silver badges14 bronze badges

Collectives™ on Stack Overflow

VBA FIle Search

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related