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

    Macro to open up specified Files in Directory and Sub-directory

    I have several files in directory c:\pull as well as several sub-folders in this directory starting with TB for eg TBBR1, TBBr2 etc

    What I want to do is to open up all the XLS files that contains (p) in the file name for eg Br1 ACCNTS (p).xls


    When running the code nothing happens

    It would be appreciated if someone can assist me in resolving this


    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
    I have also posted on Mr Excel.com


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

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,003
    Thanks
    63
    Thanked 2 Times in 2 Posts
    I have attached a workbook which I tried to adapt from a Workbook Zeddy assisted me a few months to move files from a directory and sub-directories to another folder

    I now want to open all XLS files in the folder C:\pull as well as the sub-directories with this folder containing the name ACCNTS (P) for eg in sub-directory C:\Pull\TBBR BR1 ACCNTS(P).xls etc

    Your assistance in this regard is most appreciated
    Attached Files Attached Files

  4. #3
    Star Lounger
    Join Date
    Apr 2014
    Posts
    63
    Thanks
    0
    Thanked 7 Times in 7 Posts
    Are we to understand that you want to look at each file on the C:\ HD for any file with Accnts anywhere in the filename.

    I have a file I designed to make a list of all files of a type desired such as *.xls* or *.mp3* etc. It could be adapted to do as you desire if I can find the time.

  5. #4
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,003
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Thanks for the reply. I am looking to open up all files in c:\pull and its sub-foldfers for eg c:\pull\tbbr1 where there are files that contain the name accnts (p)

  6. #5
    Star Lounger
    Join Date
    Apr 2014
    Posts
    63
    Thanks
    0
    Thanked 7 Times in 7 Posts
    I still don't understand.
    You say you want to open all the files . Do you mean you want to open each,
    one at a time, and get info from each file. If so, where in each file and
    what info and what do you want to do with the data.
    Or_____________???

  7. #6
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,003
    Thanks
    63
    Thanked 2 Times in 2 Posts
    I want to open all workbooks in c:\pull and well and its the sub-folders where its contains in the name "ACCNTS (P)" for eg BR1 ACCNTS (P) using a macro

    I will then update these one at a time

  8. #7
    Star Lounger
    Join Date
    Apr 2014
    Posts
    63
    Thanks
    0
    Thanked 7 Times in 7 Posts
    You still need to explain more
    I will then update these one at a time
    What and how will you update.
    Depending on the number of files, it may NOT be advisable to open ALL at once.
    Can you attach a sample file to open and show what is updated. And, are all files updated in the same way. Trying !!!! to help here.

  9. #8
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,003
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Thanks for the reply.

    I agree that it is easier to have a macro open up the files one by one. Once the file is open the data needs to be refreshed (See Refresh button) and updated with the lastest csv file in the same folder
    Attached Files Attached Files

  10. #9
    Star Lounger
    Join Date
    Apr 2014
    Posts
    63
    Thanks
    0
    Thanked 7 Times in 7 Posts
    Well, now you send a file with a password for the macros (I can crack but will not take the time) and it asks for a .txt file and you send .cvs and have a picker instead of the fixed folder. I'm tired of asking questions that I don't get good answers to, So, I am just going to give you what you originally asked for which is to open all files in the folder and subfolders that meet the criteria. Good Luck!
    Attached Files Attached Files

  11. #10
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,003
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Thanks for the help

    My apologies. I had to leave in a hurry to go to a function and uploaded the workbook not realising at the time that the workbook Macros were password protected. I have now removed the password

    Even though the macro asks for a .txt files , the filein question is actually a CSV file. The xls workbook and the csv file pertain to the workbook must be in the same sub-directory in order for the refresh macro to update the data

    For some unknown reason our maro is only opening up one workbook in C:\pull and its sub-directories

    I would also like to exclude the workbooks the start with the name back up
    Attached Files Attached Files
    Last edited by HowardC; 2014-08-31 at 03:09.

  12. #11
    Star Lounger
    Join Date
    Apr 2014
    Posts
    63
    Thanks
    0
    Thanked 7 Times in 7 Posts
    Use this instead to open all files. No more time right now for freebies.
    Attached Files Attached Files

  13. #12
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,003
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Thanks for the help. It now comes up with error code 445, object does not support this action

    The folowing code is highlighted

    Code:
     With Application.FileSearch

  14. #13
    Star Lounger
    Join Date
    Apr 2014
    Posts
    63
    Thanks
    0
    Thanked 7 Times in 7 Posts
    I thought you mentioned that you were using xl2003. Filesearch does NOT work in later versions. ALWAYS tell us your excel version. I have no more time right now to work on freebies.

  15. #14
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,003
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Thanks for the reply, Never made mention of the version. I'm using Office 2010

  16. #15
    Star Lounger
    Join Date
    Apr 2014
    Posts
    63
    Thanks
    0
    Thanked 7 Times in 7 Posts
    Send your email to dguillett@gmail.com for private help.

Posting Permissions

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