Save all worksheets in a folder (incl subfolders) as separate files (w/o name conflicts)

[1819]

I would like to extend the capability of this code taken from http://superuser.com/questions/56192...heets-into-sep, which allows worksheets in a workbook to be saved as a separate files.

Please could you suggest how:

1) it can be adapted to loop through all closed Excel files in a folder (including subfolders) so that it's not just run on the Active Workbook
2) the files which are created are named so that conflicts are avoided (so that lots of "Sheet1's" don't cause problems). Ideally, the name of the file would be the original workbook name plus the worksheet name.

Many thanks

Please Login or Register  to view this content.

[Leith Ross]

Hello 1819,

Add a new VBA module to your workbook's VBA Project. Copy and paste the code below into it.

The macro is set to search for "xlsx" files but can be changed to search for more than one file type. If you need to do this, let me know so I can adjust it for you.

Please Login or Register  to view this content.

[1819]

Hello Leith

Many thanks indeed for your help. I'd like to take you up on your offer about other file types, as I have numerous ".XLS" mixed in.

Unfortunately, I'm getting a "Run-time error '438': Object doesn't support this property or method" at the line "For Each Wks In Wkb" in the middle of this section of the code. Please could you assist? Thanks.

Please Login or Register  to view this content.

[Leith Ross]

Hello 1819,

You should never see this error in VBA. This indicates something is wrong with either the VBA environment or Excel. You should defragment your disk and registry and try it again.

[Leith Ross]

Hello 1819,

My apologies for making you do all that work. I was a bit distracted with getting my daughter's cat to the vet for his surgery. When I read the post again, I realized my error.

The code should be ...

Please Login or Register  to view this content.

[1819]

Hello Leith

Absolutely no problem - hope the cat is all right.

It works now. Many thanks. If you could spare a moment to show the adaptation for .XLS, I would be really grateful.

Thanks.

[Leith Ross]

Hello 1819,

The cat came through the surgery with no problem. Thanks for for your concern.

Here is the code to add more filters to the macro...

Please Login or Register  to view this content.

[1819]

Hello Leith

Well that's a relief - our friend's Siamese got through $9K in bills recently. Thank goodness they had insurance.

Thanks for adapting the code. It's stumbling when it finds it is creating a duplicate file name. The debugger points to this line:

Please Login or Register  to view this content.

Can this possibly be set to allow a file to be created with a unique name?

Many thanks.

[1819]

This is now closed. Please reply to http://www.vbaexpress.com/forum/show...656#post325656