VBA Code to close a workbook using a named range

Tster

New Member
Joined
Jun 23, 2010
Messages
4
Hello,

I am trying to close a workbook using a named range. I use the named range to open the workbook,

Workbooks.Open Range("ABCCDM")

but cannot reciprocate when I go to close the workbook. For example I have a named range called "ABCCDM". The cell contains the file name I want to open ex "05-03-2010 ABC.xls". Once it is open I copy and paste data into another workbook. After pasting I want to close the 05-03-2010 ABC.xls workbook. It no longer serves me any purpose.


Using the code

Windows("5-03-10 ABC.xls").Activate
ActiveWindow.Close

does not work because the date will change monthly (6-02-2010 this month). I don't know how to get the macro to go back to the opened workbook to close it using my named range (which has the filename in it).

Any help would be greatly appreciated. Thanks.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Isn't it?

Workbooks(Range("ABCCDM")).Close

provided that that range exists in the active workbook.

You can also use an object variable like this:

Code:
Dim wb As Workbook
Set wb = Workbooks.Open(Range("ABCCDM"))

Then to close it you just need:

wb.Close
 
Upvote 0
Andrew-
Thank you for the response, but when I tried that code I get a Run-time error '13': Type mismatch.


VoG-
Thanks for assisting again, but I am unable to do activeworkbook.close because once I copy and paste into the main file, it is no longer the active workbook.

I need to use the named range to either close the other file or to somehow activate it again and then close it.

Thanks.
 
Upvote 0
Andrew,

I tried your code again and you were correct, it works. Thank you. Your response was very useful and concise.

I do have one last question. I modified by adding what VoG wrote and added savechanges to the end of my line.

Ex: wb.Close savechanges:=False

How do I navigate or learn what other commands I can add at the end of a line like that?? I know sometimes the yellow boxes pop up with all sorts of items listed.

Thanks.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top