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. http://www.techonthenet.com/excel/formulas/dir.php — hackwithharsha, Feb 13 '16 at 19:06
Try to change `If Fl.Name = F_Name Then` to `If Fl.Name Like F_Name Then` — Fadi, Feb 13 '16 at 19:07
This answer will gonna help you go through it http://stackoverflow.com/questions/19527415/using-a-wildcard-to-open-an-excel-workbook — hackwithharsha, Feb 13 '16 at 19:10
searching is working fine but I m unable open the file please assist. — Developer_World, Feb 13 '16 at 19:33
Change `F_Name` to `Fl.Name` in this line `Workbooks.Open ....` — Fadi, Feb 13 '16 at 19:38

Fadi · Answer 1 · 2016-02-14T06:31:20.130

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 '16 at 06:31

answered Feb 13 '16 at 19:43

Fadi

3,302
3
18
41

May I direct open the file with out searching ? – Developer_World Feb 14 '16 at 01:58
@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. – Fadi Feb 14 '16 at 06:35

score 0 · Accepted Answer · answered Feb 13 '16 at 19:43

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

VBA FIle Search

2 Answers2