Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,009
    Thanks
    63
    Thanked 2 Times in 2 Posts

    Opening up files in folder and sub-folder

    I have a list of file names on all the files on sheet "workspace"


    into sheet "workspace" for workbooks containing in the name ACCNTS (P).xls for eg Br1 ACCNTS (P).xls

    I then want to use a macro to open these files but for some reason It does not open any files

    It would be appreciated if you could assist




    Code:
     Sub Open_Files()
    
    Sheets("Workspace").Select
    
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        Dim c As Range, fPath As String
                fPath = "C:\extract\"
                
        For Each c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
       Workbooks.Open (fPath & c.Value & ".xls")
                  
               
      Next c
             
    
        
        Application.ScreenUpdating = True
         Application.DisplayAlerts = True
    End Sub
    Attached Files Attached Files

  2. #2
    4 Star Lounger
    Join Date
    Jan 2004
    Location
    Wiltshire, UK
    Posts
    526
    Thanks
    1
    Thanked 45 Times in 43 Posts
    You will probably get your answer here:

    http://www.mrexcel.com/forum/excel-q...ub-folder.html

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,176
    Thanks
    200
    Thanked 781 Times in 715 Posts
    Howard,

    Minor changes but it works on my Excel 2010.
    Code:
    Option Explicit
    
    Sub Open_Files()
    
    'Sheets("Workspace").Select     'Uncomment if not using current sheet
    
        Dim rngCell As Range
        Dim zFPath  As String
        Dim zFSpec  As String
        
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        zFPath = "G:\BEKDocs\Excel\"       'change as appropriate
                
        For Each rngCell In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
           zFSpec = zFPath & rngCell.Value & ".xls"
           Workbooks.Open zFSpec
        
        Next rngCell
        
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
         
    End Sub
    HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  4. #4
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,009
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Hi RG

    When running your macro, I get run time error "cannot find C:\pull\file name.xls .... and the code below was highlighted

    Code:
      Workbooks.Open zFSpec
    What I need to do is to open up all workbooks within the pull folder as well as the sub-folders within the pull directory that has that contains the name ACCNTS (P).xls for eg BR1 ACCNTS (P)

    see your code below which I have slightly amended

    Code:
     Sub Open_Files()
    
    'Sheets("Workspace").Select     'Uncomment if not using current sheet
    
        Dim rngCell As Range
        Dim zFPath  As String
        Dim zFSpec  As String
        
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        zFPath = "C:\Pull\"       'change as appropriate
                
        For Each rngCell In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
           zFSpec = zFPath & rngCell.Value & ".xls"
           Workbooks.Open zFSpec
        
        Next rngCell
        
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
         
    End Sub

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,176
    Thanks
    200
    Thanked 781 Times in 715 Posts
    Howard,

    You didn't uncomment the Sheets("Workspace").Select line. Are your file names there?
    Did you check the file in the error message to make sure it was in the Pull directory? Any extra spaces on the worksheet?
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  6. #6
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,009
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Hi RG

    Still can't get it right. Have attached workbook to up files in C:\pull as well as subfolder for eg Pull\TBBR1 as well as file to be opened

    It would be appreciated if you could test and amend code
    Attached Files Attached Files
    Last edited by HowardC; 2014-08-23 at 11:05.

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,176
    Thanks
    200
    Thanked 781 Times in 715 Posts
    Howard,

    Don't know what is going on at your machine but it works perfectly on mine.
    I placed the .xlsm file in "G:\BEKDocs\Excel\Test" and
    the .xls file in "G:\BEKDocs\Excel\Test\Pull"
    changed the code to zFPath = "G:\BEKDocs\Excel\Test\Pull\"

    You must have something going on that I'm not aware of.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  8. #8
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,009
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Hi RG

    Have resolved part of the problem. If I copy the files into the pull directory, and then run the macro, then the xls workbooks open. What I need is for all the workbooks containing ACCNTS (P) within the sub-folder of C:\pull to open

    Kindly emend code so that the workbooks containing with the name ACCNTS (P) opens

    Thanks

    Howard

  9. #9
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,009
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Hi RG

    I think that the code would have to include a variable to declare the subfolder and then set the sub-folder something along this code below, which needs to be tweaked

    Code:
     Sub Open_Files()
    
    Sheets("Workspace").Select     'Uncomment if not using current sheet
    
        Dim rngCell As Range
        Dim zFPath  As String
        Dim zFSpec  As String
         Dim strSubFolder As String
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        zFPath = "C:\Pull\"       'change as appropriate
            strSubFolder = Dir(zFPath & "*", vbDirectory)
        For Each rngCell In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
           zFSpec = zFPath & strSubFolder & rngCell.Value & ".xls"
           Workbooks.Open zFSpec
        
        Next rngCell
        
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
         
    End Sub

  10. #10
    4 Star Lounger
    Join Date
    Jan 2004
    Location
    Wiltshire, UK
    Posts
    526
    Thanks
    1
    Thanked 45 Times in 43 Posts
    Dear Howard,

    I have reviewed the many posts you have made concerning Excel on this and other forums and I conclude that you have a complex array of spreadsheets - linked by and containing VB modules which are undocumented, untested (other than "it appears to work when you first try it") and usually not handling error conditions (either well or at all).

    It also appears that you are running a business based on these spreadsheets.

    I would recommend a comprehensive audit by a competent professional as I would bet my last dollar that somewhere - probably in many places - you are getting incorrect, incomplete or misleading results.

    Please take these remarks in the constructive spirit in which they are written - if it has not already gone off (and it probably has) you are sitting on a time bomb and it would be unkind not to tell you that.

  11. #11
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,009
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Hi Martin

    Thanks very much for your constructive feedback. I will make use of a professional programmer to assist me on this one. I'm getting very close to getting the
    result I want , but just need it fine tuned now
    I have also posted on Mr Excel

    [code] Sub Open_Test()

    Dim rngCell As Range


    Dim zPATH As String: zPATH = "C:\extract\"
    Dim FSO As Object: Set FSO = CreateObject("Scripting.FileSystemObject")
    Dim FLD As Object: Set FLD = FSO.GetFolder(zPATH)
    Dim SubFLDRS As Object: Set SubFLDRS = FLD.SubFolders
    Dim SubFLD As Object

    Dim wbData As Workbook


    Application.ScreenUpdating = False
    On Error Resume Next
    For Each SubFLD In SubFLDRS

    For Each rngCell In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    Set wbData = Workbooks.Open(zPATH & SubFLD.Name & "\" & rngCell.Value & ".xls")



    Next rngCell
    Next SubFLD

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True


    End Sub [\code]


    http://windowssecrets.com/forums/showthread.php/163895-Opening-up-files-in-folder-and-sub-folder
    Last edited by HowardC; 2014-08-25 at 04:45.

Posting Permissions

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