Close workbook whose name is different each day.

[Mike_in_IT]

I have a macro that runs daily. It formats and then copies data from one workbook to another. The first workbook, call it A, needs to be closed after I'm done with it, but it's name is different every day. workbook B stays the same except that I'm adding the data from A to it. I've got everything else working just fine except closing A. I try closing A before switching to B, but then it doesn't copy the data. I tried going back to B and using just the first part of its name (since the first part stays the same then it has the date after) and using * to mark for the rest. This did not work either.

Anyone have any suggestions?

[Norie]

How are you opening the workbook you want to close?

If you are using code then create a reference to it when you open it.

Then you can use that reference in the rest of the code and when you are finished with it to close it.

Please Login or Register  to view this content.

[Leon V (AW)]

Please Login or Register  to view this content.

[Mike_in_IT]

@Nori - The workbook I'm trying to close is a report that I open from my email.
@Leo - That won't work as I have several other workbooks open in the same instance that I don't want closed.

[Norie]

Why not open the 2 workbooks A and B in the same instance of Excel, separate from the instance with multiple workbooks open?

[Mike_in_IT]

Is there no way to do with the others in the same instance. I don't know that the other users will always do that. And I always have them open, except for A which is new every day.

[Norie]

Is there any way to identify the workbook?

For example, does it's name always contain a certain word/words?

[Mike_in_IT]

Yes, it always starts with Storage_Trending_

[Norie]

You could use this to close the workbook.

Please Login or Register  to view this content.

By the way, how are you referring to this workbook in the code that transfers data from it to the other workbook?

[Mike_in_IT]

I don't refer to it. I open it from my email, which opens it in the same instance as the others. Then I am able to run the macros that I have on the other workbooks that are open. I run a setup macro that formats the data then changes the focus to the appropriate workbook to copy it to. I just wanted to close the first workbook as it is not needed once the data is copied over. I'll try your code out to see what happens.

[Norie]

You can't copy/format a workbook without referring to it in some way.

Does your code assume that the active workbook is the correct workbook?

[Mike_in_IT]

This is the macro that I use. I start the aSetup and it does the rest. I just added your code to it. It did not however close the workbook.

Please Login or Register  to view this content.

[Norie]

The code I posted will close the first workbook it comes across with a name beginning 'Storage_Trending'.

Are you sure the name begins with that? No spaces, weird characters, misspelling...

PS In your code you are referring to a workbook called 'Storage Trending', is the workbook you are copying to?

[Mike_in_IT]

Yeah, the name always starts with that, it's the name of the report. Then it has the days date after. There are no spaces the report uses '_' instead of spaces. I ran your code and added breakpoints to watch what it does and it never goes into the 'IF' statement as though none of the workbooks have a name starting with Storage_Trending_

[Norie]

Add this to the code just before the If

Please Login or Register  to view this content.

Then run the code, open the immediate window (CTRL+G) and you should see a list of all the open workbooks.

[Mike_in_IT]

The name is in all caps for the workbook that I'm trying to close does that matter? It does show up in the window as the last workbook that is looked at.

[Mike_in_IT]

I did that and I see it run through all the open workbooks that I have. I saw that the workbook I'm trying to close is in all caps so I changed the code to reflect that and now it tries to close the workbook, but wants to save it first. How do I prevent it from trying to save?

[Norie]

Yes it matters, a lot.

Try this, it converts both the worksheet name and what we are looking for to upper case, so case shouldn't be an issue.

Please Login or Register  to view this content.

[Mike_in_IT]

Awesome, that works! Thanks for all the help!

[Leon V (AW)]

if you are opening it from an email attachment it won't have a save location right? so how about:

Please Login or Register  to view this content.