+ Reply to Thread
Results 1 to 4 of 4

VBA to check if any files in folder (and subfolders) are open?

  1. #1
    Forum Contributor
    Join Date
    07-22-2009
    Location
    Minneapolis, MN
    MS-Off Ver
    2016
    Posts
    220

    VBA to check if any files in folder (and subfolders) are open?

    Hi,
    I have a macro that, based on cell values; creates a new folder, saves the ActiveWorkbook as a new file name in the new folder, copies the contents of the old folder to the new folder (except the old workbook), and deletes the old folder.

    The problem I'm running in to is sometimes there is another file open that is located in the same folder (or occasionally a sub folder), which then causes the macro to crash because you can't copy or move an open file.

    What I need to do is add some code to the existing macro that will check and see if any other files in ThisWorkbook.Path (besides the ActiveWorkbook) are open.

    Let's say the ActiveWorkbook is located in the following folder:

    C:\DIRECTORY_1\DIRECTORY_2\DIRECTORY_3\DIRECTORY_4\DIRECTORY_5\WORKBOOK_LOCATION

    The check would need to accommodate any file type, but typically the folder contents would include xls, doc, pdf, and jpg.

    Also, occasionally there is a folder(s) in ThisWorkbook.Path as well, so it would probably be a good idea to check those folders (and any sub folders) as well.




    Ideally, this would be an IF statement, something like:

    Please Login or Register  to view this content.
    I'm sure this isn't an overly difficult thing to do, I'm just not sure how to go about it.

    Thanks for the help!

  2. #2
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: VBA to check if any files in folder (and subfolders) are open?

    Something like:
    Please Login or Register  to view this content.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Forum Contributor
    Join Date
    07-22-2009
    Location
    Minneapolis, MN
    MS-Off Ver
    2016
    Posts
    220

    Re: VBA to check if any files in folder (and subfolders) are open?

    That's awesome, and works perfect for checking the other files that are in the folder!

    How difficult would it be to add on to that to: check IF there are any folders in ThisWorkbook.Path, and check if there are, check if any files open in there as well?

  4. #4
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: VBA to check if any files in folder (and subfolders) are open?

    Okay, try this:

    (note the correction I've made to the original AnyFileLocked function, too)

    Please Login or Register  to view this content.
    Last edited by Olly; 12-12-2014 at 03:52 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] list of subfolders in folder - without files and sub-subfolders
    By MartyZ in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-11-2022, 10:56 AM
  2. [SOLVED] Reading Excel files in folder/subfolders
    By crakter in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 02-21-2014, 05:48 AM
  3. [SOLVED] Code for deleting all files in a folder & its subfolders
    By Swalih in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-17-2014, 03:21 AM
  4. Moving Files from Folder and Subfolders
    By mvinay in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-30-2014, 01:59 AM
  5. Counting files in folder including subfolders also and folder size
    By mido609 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-12-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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1