Macro to list worksheet names of all closed workbooks in a specified folder

[Chrispelletier]

Hello,

I have been searching in forums and I have not been able to find a working macro for my specific need or at least not able to modify it myself so I am seeking some help.

The macro I am looking for would go through all the Excel closed workbooks found in a specified folder and list the name of all the worksheets contained in those closed workbooks. The location of the closed workbook is on a network drive. The results of the macro would show in a new worksheet and look something like this:

Column A | Column B
Book1.xlsm | Sheet1
Book1.xlsm | Sheet2
Book1.xlsm | Sheet3
Book2.xlsm | Sheet1
Book2.xlsm | Sheet2
Book2.xlsm | Sheet3

I am including below the codes I have come up so far but it is not working. I have combined 2 codes. The first one (private declare function) is used to enable the use of network drive location so I think this part is good (it is used successfully in other macros I have). The 2nd part is a macro that I found here and that I modified slightly by remplacing directory for ChDirNet. This code seems to be as close as what I am looking for (the only thing is that I would prefer is to list the worksheet names vertically instead of listing them horizontally but it is not a big deal). FYI, I was not able to run that 2nd part (ListWsheetClosedWrkbks) of the macro even when I use a local drive so maybe there is a mistake in it even for local drive.

Thank you for your time.

Please Login or Register  to view this content.

[JBeaucaire]

Try fixing this:

Please Login or Register  to view this content.

And MAYBE this too:

Please Login or Register  to view this content.

[Chrispelletier]

Hello,
Thank you for your quick response and sorry for my late reply I did try implementing your suggestions but it did not help. FYI, I am using .xlsm files (macro-enabled)

Afterward, I did review though the 2nd part of the macro and I did realize I made a mistake in copying the code from the website. The line ‘loop’ should have been more at the bottom (see codes changes below in red). Adding the \ was needed as you suggested.

So the macro is partially working now as it did pick up the first file in the folder (which is an .xlsm file) but I get an error code on this line:

Please Login or Register  to view this content.

The error code I get is: Run time error ‘9’: Subscript out of range

Exiting the code at that point does results in having only the filename of the first file inputed under A1 but nothing else.

Any ideas on how to resolve this?

Below is the code changes in red. I decided to work first on having a workable version on a local drive so the code is a little different than the original one. Once it works on a local drive, I don’t think I will have a problem using the network drive and my private declare function. Thank you for you time!

Please Login or Register  to view this content.

[JBeaucaire]

1) This macro should be installed in the workbook where this list if being created on the sheet(1)

2) Your old macro still had a harcoded workbook name within the loop. With workbooks opening and closing there should NO commands in the macro that do not specify exactly which wb/ws/cell you are targeting using variable references.


Here's my take on your macro:

Please Login or Register  to view this content.

[Chrispelletier]

Hello JBeaucaire,

Thanks again for your quick reply. Just to confirm that the macro is installed in the workbook where the list if being created on the sheet(1).

I did try your code and got an error code on this line:

Please Login or Register  to view this content.

The error code I get is: Run time error ‘438': Object doesn't support this property or method.

Exiting the code at that point does results in having only the filename of the first file inputed under A1 but nothing else.

Were you able to run the code successfully on your side? Maybe it is a setting on my side or the fact that I am on a network (though I run the macro workbook and the file on the local C :\ drive)

Thanks for your time and effort in trying to help me.

[JBeaucaire]

Shouldn't that be "C:\test\"

I don't have a lot of faith in working in the root directory.

When you debug and hover your mouse over the fPATH and the fNAME, what exactly are the values?

[Chrispelletier]

Sorry I meant that the drive is C:\ but the full path is "C:\test\"

The value in debug given for fpath is "C:\test\" and the value for fname is "book1.xlsm".

FYI, for testing purposes, I currently have in my "C:\test\" folder 2 blank .xlsm files with 3 blank sheet each.

Thanks again!

[JBeaucaire]

Wow, that's nuts. An extra set of ( ) around the Workbook.Open command seems to gum up the works. Weirdnesses.

This should do it:

Please Login or Register  to view this content.

[Chrispelletier]

Hello JBeaucaire,

The macro is working perfectly and it works as well on a network drive without any additional modifications. Thanks for your time and effort again!

[apo]

Hi..

Variety is the spice of life.. here's another..

Change strpath to suit.

Please Login or Register  to view this content.

[Chrispelletier]

Hello Apo,

I did mark the thread as solved but I was curious and tried your code. I worked very well thank you but I had a question:

Your macro goes in the specified folder as well as the subfolders. It there a way to limit only to the specified folder (and not to go search in its subfolder)? Thanks!

[apo]

Sorry.. the thread wasn't marked a Solved when i replied...

I will take a look at your question tomorrow (its past midnight here).. initial thought was to just remove the /s but it is causing an automation error so i will have to check it out tomorrow..

[apo]

This should work...

Not sure what was the go with the previous error.. I swear this is the same code with changes i tried 15 minutes ago..

Please Login or Register  to view this content.

[Chrispelletier]

Great, thanks for your quick response. It is working perfectly including on a network drive. I did change *.xls to *.xl* in order to pickup .xlms files. Thanks again!