Code to open up files in folder and sub-folder

[Howardc]

I Have the folowing code where I want to open up all files in a folder C:\pull as well as all the folders within C:\pull where the name contains ACCNTS(P). These are all XLS files

See code below, which does nothing when acticated

 Option Explicit

Sub Open_Files_Pull()
    Dim fso As Object 'FileSystemObject
    Dim fldStart As Object 'Folder
    Dim fld As Object 'Folder
    Dim fl As Object 'File
    Dim Mask As String

    Set fso = CreateObject("scripting.FileSystemObject") ' late binding
    'Set fso = New FileSystemObject 'or use early binding (also replace Object types)

    Set fldStart = fso.GetFolder("C:\Pull") ' <-- use your FileDialog code here

    Mask = "(p)*.xls"
    Debug.Print fldStart.Path & "\"
    ListFiles fldStart, Mask
    For Each fld In fldStart.SubFolders
        ListFiles fld, Mask
        ListFolders fld, Mask
    Next
End Sub


Sub ListFolders(fldStart As Object, Mask As String)
    Dim fld As Object 'Folder
    For Each fld In fldStart.SubFolders
        Debug.Print fld.Path & "\"
        ListFiles fld, Mask
        ListFolders fld, Mask
    Next

End Sub

Sub ListFiles(fld As Object, Mask As String)
    Dim fl As Object 'File
    For Each fl In fld.Files
        If fl.Name Like Mask Then
            Debug.Print fld.Path & "\" & fl.Name
        End If
    Next
End Sub

[snb]

Sub M_snb()
  c00="C:\Pull\" & replace(createobject(wscript.shell").exec("cmd /c Dir ""C:\Pull\*.xls"" /a/b").stdout.readall,vbcrlf,vbcrlf & "C:\Pull\") & vbcrlf
  sn=split(c00 & createobject(wscript.shell").exec("cmd /c Dir ""C:\Pull\*ACCNTS(P)*.xls"" /s/a/b").stdout.readall,vbcrlf)
  
   for j=0 to ubound(sn)
     msgbox sn(j)
   next
End sub

[Howardc]

Thanks for the reply

When running your code, I get "Compile Error: Syntax error" and the following code is highlighted

 c00="C:\Pull\" & replace(createobject(wscript.shell").exec("cmd /c Dir ""C:\Pull\*.xls"" /a/b").stdout.readall,vbcrlf,vbcrlf & "C:\Pull\") & vbcrlf
  sn=split(c00 & createobject(wscript.shell").exec("cmd /c Dir ""C:\Pull\*ACCNTS(P)*.xls"" /s/a/b").stdout.readall,vbcrlf)

Kindly test & amend code

[snb]

Why don't you ?

[Howardc]

Unfortunately, I don't understand your code

I have tried to write my own code.

If I copy the files from the sub-folders to C:\extract it works, but the code does not open the files containing the name ACCNTS (P) from the sub-folders

It would be appreciated if you could amend my code as i'm battling to code the code sorted out to open these files in the sub-folders within C:\extract

 Sub Open_Files()

Sheets("Workspace").Select    

    Dim rngCell As Range
    Dim zFPath  As String
    Dim zFSpec  As String
     Dim Folder As String
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    zFPath = "C:\extract\"      
       Folder = Dir(zFPath, vbDirectory)

    For Each rngCell In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
       zFSpec = zFPath & Folder & rngCell.Value & ".xls"
       Workbooks.Open zFSpec
    
    Next rngCell
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
     
End Sub

[Ingolf]

You should post a link to the other thread so other posters know what others have tried to help with

list files in folder and sub-folder
Open up workbooks in folder and sub-folders

and here:
Opening up files in folder and sub-folder | Windows Secrets Lounge

[alansidman]

Howard;

Please read this link:

http://www.excelfox.com/forum/f25/me...1172/#post5326

[Howardc]

My apologies-have read the link to Cross Posters and agree 100% with the sentiment. Will post solution on all the sites for once found, where I have cross posted


My code is partly working.