Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Macro to open Files

    I have a spreadsheet, where I want to open all the files that are listed on sheet "workspace" in a folder called C:\Pull as well as the sub-folders

    I have tried to write code to do this -see code below

    Your assistance in resolving this is most appreciated



    Code:
      Sub Open_Files()
    
    Dim ws As Worksheet
    Dim rngCell As Range
    Dim fPATH As String:    fPATH = "C:\pull\"
    Dim FSO As Object:      Set FSO = CreateObject("Scripting.FileSystemObject")
    Dim FLD As Object:      Set FLD = FSO.GetFolder(fPATH)
    Dim SubFLDRS As Object: Set SubFLDRS = FLD.SubFolders
    Dim SubFLD As Object
    
    Dim wbData As Workbook
    Set ws = ThisWorkbook.Sheets("Workspace")
    
    Application.ScreenUpdating = False
     On Error Resume Next
    For Each SubFLD In SubFLDRS
        
      For Each rngCell In ws.Range("A1:A" & ws.Cells(ws.Rows.Count, 1).End(xlUp).Row)
        Set wbData = Workbooks.Open(fPATH & SubFLD.Name & "\" & rngCell.Value & ".xls")
    
    Next rngCell
        
    Next SubFLD
    
      Application.ScreenUpdating = True
      Application.DisplayAlerts = True
        
        
    End Sub
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Howard,

    Nice job! You need to change just one line:

    Code:
    Set wbData = Workbooks.Open(fPATH & SubFLD.Name & "\" & rngCell.Value & ".xls")
    
                 to
    
    Set wbData = Workbooks.Open(SubFLD & "\" & rngCell.Value)
    SubFLD already contains the path and file name
    rngCell.Value already contains the extension

    As you have it set up though, it will only open files in the subfolders. Keep going, you are on the right track.

    HTH,
    Maud
    Last edited by Maudibe; 2014-11-01 at 12:27.

  3. #3
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    The error is not that.
    It will run with/without the set wbData=
    It will run with/without the wbData=

    The problem is that the macro is asking for .xls files to open and the list has the extension of .xls so it should be this line to open all .xls* files

    Workbooks.Open (fPATH & SubFLD.Name & "\" & rngCell.Value)


    It is the fact that his macro is

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    dg,

    I tested Howard's code by creating a path C:\pull with a subfolder called "test". Running the code yields the values for the following object variables

    subFld= C:\pull\test (includes the path)


    rngCell.value= BR1 ACCNTS(P).xls

    Howard1.png

    Therefore, there is no need for path and the subfolder name

    fPATH & SubFLD.Name

    which can simply be replaced by

    subFLD

    Assuming that Howard might have plans for the object variable wdData (and left as it is), the code line to open the file and set it to wbData would be:

    Set wbData = Workbooks.Open(SubFLD & "\" & rngCell.Value)

    Respectfully,
    Maud

  5. #5
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    And set wbdata= should not be necessary

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Correct, if he has no further intention to use wbData in his code.

  7. #7
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Maud

    Thanks for the help and your input

  8. #8
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Thanks for your input dguillett

Posting Permissions

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