Results 1 to 8 of 8

Thread: Code to open up files in folder and sub-folder

  1. #1
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    13

    Code to open up files in folder and sub-folder

    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

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

  2. #2
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    Code:
    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

  3. #3
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    13
    Thanks for the reply

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

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

  4. #4
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    Why don't you ?

  5. #5
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    13
    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


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

  6. #6
    Member
    Join Date
    Jul 2012
    Posts
    55
    Rep Power
    13
    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
    Last edited by Ingolf; 08-25-2014 at 04:49 PM.

  7. #7

  8. #8
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    13
    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.

Similar Threads

  1. Replies: 2
    Last Post: 11-10-2013, 11:48 PM
  2. Replies: 15
    Last Post: 08-23-2013, 12:03 PM
  3. Replies: 0
    Last Post: 07-07-2013, 01:52 AM
  4. Replies: 1
    Last Post: 02-14-2013, 12:09 PM
  5. Open Folder Using VBA
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 06-28-2011, 03:52 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •