How To Delete Multiple Workbooks Using Macro

[Launchnet]

Good Morning Everyone . . .
I am looking for a way to close a group on Workbooks that are in separate instances of Excel.

These Workbooks get opened similar to running a Powerpoint program, but much different usage than Powerpoint.

AS AN EXAMPLE:
I have 7 Workbooks open in Excel . . . Could be more or less.
Each workbook has 2 sheets, the "User Sheet" (not the actual name, as all user sheet names vary) and a sheet named "Setup." (Always named "Setup.")

On the "Setup" sheet I have a list of all the possible Workbooks that I want to close. For reference purposes only, I'll call this list "To Close". All 7 Workbooks would be listed, but not all of them would be opened. If any particular Workbook on the "To Close" list is not open, it would simply be bypassed.

Example of list: Column A

Row 25 has the name of Workbook A
Row 26 has the name of Workbook B
Row 27 has the name of Workbook C
And this would continue down to the last Workbook possibly open. I will know how many Workbooks could possibly be open.

Other workbooks could also be open, but they would not be closed by this macro. In this example, I would only want Workbooks A & B & C closed.

I would have rows 25 thru 27 (or more) listed in the same location on the "Setup" sheet of each Workbook.

Each "User Sheet" would have a macro called "Close Group"

The macro would have to search through all open Workbooks and when it found one of the Workbooks listed for closing, it would close that workbook. Then, the macro would look for the next Workbook on the "To Close" list.

The Active Workbook would also be on the list and that also needs to be closed. I would guess that this would be the last Workbook to close, but it really would not make any difference if the macro would still work. I think the macro has to have the active Workbook open to close the others.

After the list has been gone through, the macro would stop.

NOTE: Each Workbook has it's own "To Close" list. If it would help, I can leave the Workbook Name A off of the list in Workbook A. I would do the same on Workbook B, etc. Then the Active Workbook could automatically be closed at the end of the macro.

This is going to take someone many times more talented than me. I hope someone can help me.

[Leith Ross]

Hello Matt,

I have written 2 macros to help you do this. The first macro finds all running instances of Excel and passes back a variant array that contains the Excel title and the window handle. This information will be used the by second macro to close the workbooks in the "Setup" sheet list. This second macro will need to be copied into the other workbooks that you want to close.

This macro goes into the main workbook.

Please Login or Register  to view this content.

You will need to qualify the reference to the first macro with the main workbook name in this code. The code is marked in red. Copy this macro into each of the auxiliary workbooks.

Please Login or Register  to view this content.

[Launchnet]

Boy, that was fast . . . Thanks

I copied the first macro into the Workbook that opens the 1st of 3 or 5 or 6 + additional workbooks (auxuliary workbooks). I would call this my main workbook name. The name of this workbook is: KeysToMaxLiving.xls

I copied the second macro into each of 3 Workbooks that I have opened.

Now I need to make up the "To Close" list in each of the 3 "Setup" sheets.
Do I type just the names of the workbooks and/or do I include the .xls ???

I don't understand the 1st two sentences of the following that you wrote. Can you explain this to me so I can understand?

You will need to qualify the reference to the first macro with the main workbook name in this code. The code is marked in red. Copy this macro into each of the auxiliary workbooks.

I believe that I follow what you are doing with these macro's.

One More question:

Let's say that I have the Main Workbook Open and the user wants to open the 1st auxiliary workbook. The user clicks on the button that opens the next workbook.

Anytime after the 1st auxiliary workbook is open - or - the 2nd or the 3rd, the user would beable to click on the New "Close Group" button and all of the auxiliary workbooks would be closed and the Main Workbook would still be open. Naturally, the "To Close" list on the "Setup" sheet would include all possible workbooks that could be opened. The user would not necessarly open all possible workbooks.

Also, Auxiliary workbook 1 has a button that opens Auxiliary workbook 2 and so on. They do not go back to the Main Workbook to open additional auxiliary workbooks.

Boy am I windy ! Red color represents Hot Air. Ho Ho Ho

I sure hope this helps.

[Leith Ross]

Hello Matt,

I gave you some bad information. Both macros need to be copied into the auxiliary workbooks. Qualifying the reference will only work if the workbooks are open in the same instance of Excel.

The macro looks at the running instances of Excel and compares them to your "To Close" list. If a match is found, that workbook is closed. You need to enter the workbook name (no path) and the ".xls" extension in your "To Close" list.

[Launchnet]

Thanks - No problem

I copied both macros to each auxiliary workbook.

Ready to test I think.

Is there something I need to do or know about on your words: Qualifying the reference"

I have no idea what this means.

[Launchnet]

Sorry . . . See previous message, this one should not have been sent.
I found that the SendMessageA was below the macro and when I moved it above I don't get error.

Please see previous message.

[Leith Ross]

Hello Matt,

I went out to dinner. Are the macros working?

[Launchnet]

Hope you had a good dinner. I'm married to a wonderful woman from Mainland China. I have never had such good chinese food. She cooks original chinese food and her own make goodies. I used to hate eggplant, but now it is probably my favorite food.

Back to work.

So far I'm no longer getting errors right now anyway.

I walk my way thru the below macro I find the following:

Rng gets first workbook name
RngEnd gets last workbook name
Set Rng = IIf doesn't show anything
WkbList appears blank
WkbInstances appears blank - - - It does go to GetExcelInstances
When this Function is called, it keeps looping and nothing happens

Please Login or Register  to view this content.

In every Workbook "Setup" sheet, all the auxiliary workbook names are in column A starting at row 25 on every workbook.

Got any ideas? I'm stumped at this point, but it looks good so far.

[Leith Ross]

Hello Matt,

The WkbList is a 2-D Variant Array. Unless you provide it subscripts (dimensions), you won't see anything. The array is n Rows by 1 column wide. You have use something like code below to check it...

Please Login or Register  to view this content.

If there was an error when the macro was running, the Events may be disabled. Run the macro ResetEvents before testing the macro again.

[Launchnet]

Hi
I tried your added code and now I can see that it has a value . . . OK

I don't know where or how to run the macro ResetEvents. Where do I find this macro?

[Leith Ross]

Hello Matt,

Sorry for the late reply. I had to do some follow up with my father-in-law. Two of his dogs are out running around and he doesn't know where they are. I'll have to call the humane society in the morning to check if they were picked up.

The macro for resetting the events didn't get copied over. Here is the macro to reset the events. Copy this into a standard VBA module.

Please Login or Register  to view this content.

[Launchnet]

God Morning Before Church . . .

I think I have time to bring you up to date.

I copied and ran the Reset Events Macro.

Then, I stepped thru "CloseWorkbooks" macro
Set Rng gets populated (First name on To Close List)
Set RngEnd gets populated (Last name on To Close List)
Set Rng = . . . clears Rng (should it do this)
WkbList = Rng.Value (and Rng is empty and therefor so is WkbList)
The Function "GetExcelInstances" is now called.

Stepping through the Function . . .
hWnd value is 656236 & GW_CHILD is 5
ClassName is: ClassName=" followed by many square boxes
L=16
ClassName = tooltips_class32"
Then on . . . If ClassName = "XLMAIN" (it does not, so it goes to End If
hWnd is now 328696
DoEvents simply goes to next row which is Wend
Now the Function loops back to While hWnd <> 0

When I step thru the While loop, the ClassName changes to "Auto-Suggest Dropdown"
L = 21

The Function continues to run in the above manner and does not stop until I Halt it.

I don't know whatelse I can describe to you. Hope this helps

[Leith Ross]

Hello Matt,

The code works in the workbooks I created to test it. However, your workbooks may contain other code that effects the macros in some other way. Can you post the workbook in Excel 2003 format for review?

[Launchnet]

Hi

Here are the 4 workbooks to test. I have tested it with these four workbooks and it still does the same thing.

These workbooks are not saved in Excel 2003 as I do not know how to do this.

When you have saved them in Excel 2003 . . .
Please open in the following order:

MasterWorkbook.xls . . . . This does not get closed. It should be the workbook displayed when the program is run.

Then open the other 3 and Book30.xls will be on top

Book10.xls
Book20.xls
Book30.xls . . . This book has the macro's and a Button to automatically run "CloseWorkbooks" macro.

Please drop me a note if I need to send you anything else or answer any questions.

[Launchnet]

Hi Leith . . .

Just a short note.

Remember you have to open each file in a separate instance of excel.

When I opened these 4 files for testing, I 1st opened Excel and then opened the 1st file.

I did the same for the other 3 files.

Is their a better way of opening a file in a separate instance of Excel. I could not find anyother way.

[Launchnet]

Good Morning Leith & Everyone . . .

Sunday Morning and God Blesses us everyday. No sermons, but just listen and you can hear him. He does speak to us.

Leith, I found the solution. I awoke friday morning and I heard this voice say . . . Don't make it so hard . . . Do it a simple way, like this.

I went to my computer and when the a macro opens the next workbook, I inserted at the end of the macro "Application.Quit" This closes the workbook that I ran the macro from, and leaves open the workbook I just opened. I put this in every workbook macro that opens another workbook.

In My "Back" or "Previous" key I made the macro to "Re-Open" the previous workbook and at the end of this macro I also put "Application.Quit".

Then, I added a macro to each workbook where the link says "Close Presentation. This macro simply is "Application.Quit", as there are no other workbooks open. Even if there are other workbooks open that are not part of the presentation, they still remain.

God Bless You All . . . You've been extremely helpful while I was building this application.

I'll talk to everyone again soon, I'm sure . . . As I already have a very small application to do.