Open File Macro

[K]

I have macro set on a button. (see below)

Sub OpenFile()
Folder = "C:\My Document\Records"
FileName = Range("U21").Value
If IsNumeric(FileName) And (Range("U21").Value <> "") And _
Len(FileName) = 6 Then

fName = Dir(Folder & "\*" & FileName & "*.xlsx")
If fName = "" Then
MsgBox "Record *" & FileName & "* DONT EXIST" _
& vbNewLine & "OR" & vbNewLine & "ENTERED INCORRECT NUMBER" _
, vbCritical, "INCORRECT"
Else
Do While fName <> ""
Workbooks.Open Filename:=Folder & "\" & fName


fName = Dir()
Loop
End If
Else
MsgBox "VIREMENT *" & FileName & "* DONT EXIST" _
& vbNewLine & "OR" & vbNewLine & "ENTERED INCORRECT NUMBER" _
, vbCritical, "INCORRECT"
End If
End Sub

at the moment above macro look at the file name in Range("U21") and
find that file in Folder "C:\My Document\Records" and then Open it.
The problem is that there are also Subfolders in Folder "Records" and
I want macro to look in all the Subfolders for the file and when file
is found then Open it. I did try changing the code line

Folder = "C:\My Document\Records"

TO

Folder = "C:\My Document\Records\*"

but it didn’t work. Please can any friend can help that what changes
I should do in above macro that it should look for file in all the
Subfolders and then Open it

 

[Joel]

I wrote this code assuming only one level of subdirectories. if you have
multiple levels of subdirectories then I will have to write the code to use
recursion.


Sub GetLogs()

Folder = "C:\My Document\Records"
Filename = Range("U21").Value
If IsNumeric(Filename) And (Range("U21").Value <> "") And _
Len(Filename) = 6 Then

Set objShell = CreateObject("Shell.Application")
Set fs = CreateObject("Scripting.FileSystemObject")

Foundfile = False

Set Folder = _
fs.GetFolder(Folder)

If Folder.subfolders.Count > 0 Then
For Each Fl In Folder.subfolders
fname = Dir(Fl & "\*" & Filename & "*.xlsx")
Do While fname <> ""

fname = Dir()
Workbooks.Open Filename:=Fl & "\" & fname
Foundfile = True
Loop
Next Fl
End If

fname = Dir(Folder & "\*" & Filename & "*.xlsx")
Do While fname <> ""
Workbooks.Open Filename:=Folder & "\" & fname
fname = Dir()
Foundfile = True
Loop
Foundfile = True

If Foundfile = False Then
MsgBox "Record *" & Filename & "* DONT EXIST" _
& vbNewLine & "OR" & vbNewLine & "ENTERED INCORRECT NUMBER" _
, vbCritical, "INCORRECT"
End If
End If
End Sub

 

[K]

I wrote this code assuming only one level of subdirectories.  if you have
multiple levels of subdirectories then I will have to write the code to use
recursion.

Sub GetLogs()

   Folder = "C:\My Document\Records"
   Filename = Range("U21").Value
   If IsNumeric(Filename) And (Range("U21").Value <> "") And _
      Len(Filename) = 6 Then

      Set objShell = CreateObject("Shell.Application")
      Set fs = CreateObject("Scripting.FileSystemObject")

      Foundfile = False

      Set Folder = _
         fs.GetFolder(Folder)

      If Folder.subfolders.Count > 0 Then
         For Each Fl In Folder.subfolders
            fname = Dir(Fl & "\*" & Filename & "*.xlsx")
            Do While fname <> ""

               fname = Dir()
               Workbooks.Open Filename:=Fl & "\" & fname
               Foundfile = True
            Loop
         Next Fl
      End If

      fname = Dir(Folder & "\*" & Filename & "*.xlsx")
      Do While fname <> ""
         Workbooks.Open Filename:=Folder & "\" & fname
         fname = Dir()
         Foundfile = True
      Loop
      Foundfile = True

     If Foundfile = False Then
        MsgBox "Record  *" & Filename & "*  DONT EXIST" _
        & vbNewLine & "OR" & vbNewLine & "ENTERED INCORRECT NUMBER" _
        , vbCritical, "INCORRECT"
     End If
   End If
End Sub











- Show quoted text -

Thats brilliant Joel. Thanks again

 

[K]

Hi Joel, I have tried your code and i am getting error on code line
(see below). Please help

Workbooks.Open Filename:=Fl & "\" & fname

See below the error message

Run-time error '1004'
"C:\My Document\Records\Record A" could not be found. Check the
spelling of the file name, and verify that the file location is
correct
If you are trying to open the file from you list of most recently used
files, make sure that the file has not been renamed, moved, or deleted