+ Reply to Thread
Results 1 to 10 of 10

Macro to list and open .xlsm files in folder and sub-folder

  1. 02-22-2019, 10:10 AM #1
    Howardc1001
    Howardc1001 is offline
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,382

    Macro to list and open .xlsm files in folder and sub-folder

    I have code to list files in C:\pull and well as sub-folder where workbook contain ACCNTS(P) in the name as well as if files are .xlsm files

    eg M_BR ACCNTS (P).xlsm



    The codes lists only .xls files in C:\pull and its sub-folder


    It would be appreciated if someone could amend my code to include only .xlsm files




    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  2. 02-22-2019, 10:59 AM #2
    JLGWhiz
    JLGWhiz is offline
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Macro to list and open .xlsm files in folder and sub-folder

    It looks like the code already looks for the .xlsm files but is qualified with a partial file name to limit those files to ones containing ACCNTS(P) in the file name. If you just want all the xlsm files then change this statement

    Please Login or Register  to view this content.
    in the 'ListFilesinFolder' macro to

    Please Login or Register  to view this content.
    and it will list all of the .xlsm files.

    But it should not have been listing any other type files.
    Last edited by JLGWhiz; 02-22-2019 at 02:28 PM.
    Any code provided by me should be tested on a copy or a mock up of your original data before applying it to the original. Some events in VBA cannot be reversed with the undo facility in Excel. If your original post is satisfied, please mark the thread as "Solved". To upload a file, see the banner at top of this page.
    Just when I think I am smart, I learn something new!
    Reply With QuoteRegister To Reply
  3. 02-22-2019, 12:27 PM #3
    Howardc1001
    Howardc1001 is offline
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,382

    Re: Macro to list and open .xlsm files in folder and sub-folder

    Thanks for the help. It now extracts all the .xlsm files


    I only need the .xlsm files containing "ACCNTS(P) in the name of the workbook for eg M_BR1 ACCNTS (P).xlsm . M_CA_ACCNTS.xlsm etc to be extracted


    Kindly amend code accordingly
    Last edited by Howardc1001; 02-22-2019 at 12:30 PM.
    Reply With QuoteRegister To Reply
  4. 02-22-2019, 02:25 PM #4
    JLGWhiz
    JLGWhiz is offline
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Macro to list and open .xlsm files in folder and sub-folder

    Quote Originally Posted by Howardc1001 View Post
    Thanks for the help. It now extracts all the .xlsm files


    I only need the .xlsm files containing "ACCNTS(P) in the name of the workbook for eg M_BR1 ACCNTS (P).xlsm . M_CA_ACCNTS.xlsm etc to be extracted


    Kindly amend code accordingly
    Then your original code should have been producing what you wanted. But there is nothing that I can see that would produce only .xls files. Try using this line of code instead.

    Please Login or Register  to view this content.
    Last edited by JLGWhiz; 02-22-2019 at 02:30 PM.
    Reply With QuoteRegister To Reply
  5. 02-22-2019, 02:35 PM #5
    Howardc1001
    Howardc1001 is offline
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,382

    Re: Macro to list and open .xlsm files in folder and sub-folder

    Thanks for your help. Code is working perfectly
    Reply With QuoteRegister To Reply
  6. 02-22-2019, 02:46 PM #6
    JLGWhiz
    JLGWhiz is offline
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Macro to list and open .xlsm files in folder and sub-folder

    Quote Originally Posted by Howardc1001 View Post
    Thanks for your help. Code is working perfectly
    You're welcome,
    regards, JLG
    Reply With QuoteRegister To Reply
  7. 02-23-2019, 02:47 PM #7
    Howardc1001
    Howardc1001 is offline
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,382

    Re: Macro to list and open .xlsm files in folder and sub-folder

    Is it possible to amend code that if no .xlsm file in sub-folder within C:\pull then to extract the .xls file ?

    Some sub-folders may only have an .xls workbook



    If so kindly amend code
    Reply With QuoteRegister To Reply
  8. 02-24-2019, 11:21 AM #8
    JLGWhiz
    JLGWhiz is offline
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Macro to list and open .xlsm files in folder and sub-folder

    You can try this
    Please Login or Register  to view this content.
    that should then return any Excel file with 'ACCNTS' in the name.
    Reply With QuoteRegister To Reply
  9. 02-24-2019, 12:43 PM #9
    Howardc1001
    Howardc1001 is offline
    Forum Contributor
    Join Date
    07-12-2018
    Location
    South Africa
    MS-Off Ver
    Office 2021
    Posts
    2,382

    Re: Macro to list and open .xlsm files in folder and sub-folder

    thanks for the help. Code works perfectly
    Reply With QuoteRegister To Reply
  10. 02-24-2019, 07:35 PM #10
    JLGWhiz
    JLGWhiz is offline
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Macro to list and open .xlsm files in folder and sub-folder

    Quote Originally Posted by Howardc1001 View Post
    thanks for the help. Code works perfectly
    You're welcome,
    regards, JLG
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Excel VBA code for selecting a folder then search & open specified files in that folder
    By Excelrookie_1 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-27-2021, 03:09 AM
  2. [SOLVED] VBA to save all .CSV files in a folder as .XLSM not working?
    By patdools in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-18-2019, 02:28 PM
  3. [SOLVED] Listing only xlsm files of the folder with VBA
    By ImranBhatti in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-27-2017, 10:09 AM
  4. Convert csv files in folder to xlsm
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-13-2015, 09:49 PM
  5. [SOLVED] Open password protected files in folder using list of passwords
    By Shift-4 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 07-22-2013, 06:08 PM
  6. Index Page in Folder for Macro buttons, to Run Multiple Macros on all Files in a Folder
    By marsham in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-12-2013, 04:45 AM
  7. Macro to open up all files in certain folder
    By dpcp in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-06-2012, 03:26 PM

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1