Close All Open Files in a Specific Folder Using VBA

**e4excel** · 07-28-2022, 07:01 AM

Close All Open Files in a Specific Folder Using VBA

Dear Forum,

I need to run a Macro which creates My Report By Opening Several Files from a Specific Folder and then once the Report is Made the same files need to be closed, this Macro worked for a Very Long Time without any hassles however after the Microsoft Office was upgraded from Excel-2010 to Excel-2019 it does not work the way it used to..
The Macro does the work of generating the Correct Report however when the Opened Files need to be closed a few Files do remain open. this used to not happen earlier however after the migration this issue came up.
Please find the Code below which is used only for Closing the Open Files in a Specific Folder.

Please Login or Register  to view this content.

Can someone please assist me on the above code so that all the Files are closed with this Macro.

Regards
e4excel...

**TMS** · 07-28-2022, 09:28 AM

Ok, this is an out and out guess, and I have no way of testing it. BUT, as you've had quite a few views and no response, let's give it a punt.

Recent versions of Excel are more aware of the source of files; for example, if they're downloaded from the internet, or an email attachment, or even if they are (or are not) in a "secure" location.

It may be more relevant if the files being opened are macro enabled.

That said, if the files are "blocked", it may prevent you from setting the Protected View mode. That, in turn, may prevent you from closing the files.

The more I write, the less confident I feel about this as a possible cause. But, anyway, something to investigate and eliminate.

**e4excel** · 07-29-2022, 12:18 AM

Thanks for the response TMS...But is there something missing in my code as the same code was being effectively used earlier, if so please can you please offer that input as well..
In regards with providing an attachment I really dont know whether that would really help in this case as the files are being extracted from a BI Tool QlikView and these files are stored in the Folder INetCache.

**TMS** · 07-29-2022, 03:19 AM

I suspect it is not the code but the status of the files. Check the properties in File Manager and see if they are blocked.

**e4excel** · 07-29-2022, 05:06 AM

There is no option such as Security in my Files..to be UNBLOCKED..

**TMS** · 07-29-2022, 05:13 AM

Well, that is that option checked and eliminated. Unfortunately, my only shot.

The only thing I can suggest is that you put some Debug statements in the "do stuff" bit of code and see if the full path is followed through the code for all files. If not, then you need to explore why it fails to get through to the close file statement.

**e4excel** · 07-29-2022, 05:15 AM

Is there any other code to close all open files from a Specific Folder which are open, I had also included the ENABLE CONTENT Macro to first Enable the content and then copy paste the data

**TMS** · 07-29-2022, 05:48 AM

I really cannot help. The thing to focus on is this loop:

Please Login or Register  to view this content.

You open a workbook, do stuff (unknown), and close the workbook. It is a very tight loop. So, if the code is not closing the workbook, the implication is that something (presumably in the do stuff code) is preventing it getting to the close statement.

You could try taking the DisplayAlerts = False out and see what message(s) you get.

Other than that, I am out of ideas.

**e4excel** · 07-29-2022, 11:32 PM

Please Login or Register  to view this content.

I am just Setting the Variable wb in the first Open statement and do nothing and then simply close this file.. as this enetire code is just to close all the Files..
I call this Procedure in another Macro where the stuff is done. so is there something which needs to be tweaked ?

**TMS** · 07-30-2022, 03:20 AM

I honestly don't know. I don't understand why you have a loop that just opens and closes workbooks if nothing happens in between.

I can't offer any more help but I will see if anyone else has any ideas.

**Croweater** · 07-30-2022, 03:51 AM

Try using

Please Login or Register  to view this content.

**davesexcel** · 07-30-2022, 07:48 AM

I will just go by the title.
"Close All Open Files in a Specific Folder Using VBA "

The assumption is you want to loop through all the open workbooks and close the ones from a specific folder.

If you loop through all the open workbooks

Please Login or Register  to view this content.

To get the full name for the open workbooks

Please Login or Register  to view this content.

See if there is any text in the wb.full name

Please Login or Register  to view this content.

If specific text is in the workbook name, close the workbook.

ThisWorkbook is the workbook with the code.

Please Login or Register  to view this content.

**e4excel** · 07-31-2022, 12:12 AM

I tried your code davesexcel with s = "*.xls*" however it does not work and how do i provide the Path for the specific Folder ?

**davesexcel** · 07-31-2022, 08:56 AM

s = "*.xls*"

You asked " Close All Open Files in a Specific Folder Using VBA "
would s = "*.xls*" be in the folder name?

Recheck my code, I show the variable: s='string in folder name

**e4excel** · 07-31-2022, 09:43 AM

Dear Dave,

I am unable to tweak your code to my requirement, where do I mention the Folder path and the Likely Name of all the Open Files ? All the Files which are open would start with "CH*.xls*" and in the below path MyFolder = "C:\Users\office\AppData\Local\Microsoft\Windows\INetCache"

So please explain how do I use your code ?

**TMS** · 07-31-2022, 02:23 PM

Try this. It does not open any files, it just closes files that are from the specified folder AND have the right file name structure.

Please Login or Register  to view this content.

**snb** · 07-31-2022, 04:34 PM

What is the result of

Please Login or Register  to view this content.

**TMS** · 07-31-2022, 06:55 PM

@snb: Error 438.

So maybe:

Please Login or Register  to view this content.

**e4excel** · 08-03-2022, 01:05 AM

Thank you so much TMS, this actually works the way I wanted...

Also thanks snb, for the alternative solution..

Regards
e4excel

**TMS** · 08-03-2022, 02:59 AM

You're welcome. Thanks for the rep.

**e4excel** · 12-05-2023, 03:19 AM

Dear Forum,

This code was working perfectly well however after I upgraded my CPU at work it stopped working there's no error however the files do not close as used to earlier is there anything i need to do in terms of checking certain references in the Library which might be impacting the same as I do not recall the references which i might have checked earlier and I do not have that CPU anymore..so can someone please guide me on the same as there are around 30 Files for 1 Task and multiple tasks have multiple files open which need to be closed..

Warm Regards
e4excel

**TMS** · 12-08-2023, 01:09 AM

Check: MyFolder = "C:\Users\office\AppData\Local\Microsoft\Windows\INetCache"

Close All Open Files in a Specific Folder Using VBA

LinkBack

Thread Tools

Rate This Thread

Display

Close All Open Files in a Specific Folder Using VBA

Re: Close All Open Files in a Specific Folder Using VBA

Re: Close All Open Files in a Specific Folder Using VBA

Re: Close All Open Files in a Specific Folder Using VBA

Re: Close All Open Files in a Specific Folder Using VBA

Re: Close All Open Files in a Specific Folder Using VBA

Re: Close All Open Files in a Specific Folder Using VBA

Re: Close All Open Files in a Specific Folder Using VBA

Re: Close All Open Files in a Specific Folder Using VBA

Re: Close All Open Files in a Specific Folder Using VBA

Re: Close All Open Files in a Specific Folder Using VBA

Re: Close All Open Files in a Specific Folder Using VBA

Re: Close All Open Files in a Specific Folder Using VBA

Re: Close All Open Files in a Specific Folder Using VBA

Re: Close All Open Files in a Specific Folder Using VBA

Re: Close All Open Files in a Specific Folder Using VBA

Re: Close All Open Files in a Specific Folder Using VBA

Re: Close All Open Files in a Specific Folder Using VBA

Re: Close All Open Files in a Specific Folder Using VBA

Re: Close All Open Files in a Specific Folder Using VBA

Re: Close All Open Files in a Specific Folder Using VBA

Re: Close All Open Files in a Specific Folder Using VBA

Thread Information

Users Browsing this Thread

Similar Threads

Open all files in a folder update, save and close

Open all files in a folder update, save and close

[SOLVED] Open files based on column in a specific folder, copy content and close.

[SOLVED] Open files in folder, replace comma with semicolon etc, save and close

[SOLVED] macro to open all files in a folder, save them, then close them

[SOLVED] Macro to open and close all files in a folder and sub folders

Macro to open & close mutiple xlsx files in folder

Bookmarks

Bookmarks

Posting Permissions