VBA Command to close specific workbook?

BLaCKouT

New Member
Joined
May 31, 2009
Messages
42
Hi all,

I've got an excel file which end users open as a template, then once they've put header information in, gets saved as an individual file.
After this has occurred, I've got a userform which is called from another workbook (the reason for this being that the form has bitmaps in it, and pushes the file size up by 600kb, so by calling it remotely, I'm saving a significant amount of disk space over time).

So, for this to work, Excel has to open secondary workbook in the background. I don't mind this, but after the user has selected the information from the form, and it's gone through its Unload Me process, the secondary workbook is still open.

If I add a activeworkbook.close command, Excel wants to close the ongoing (primary) workbook, as it's the one that's currently active.

Is there a way I can specify the workbook to be closed? The secondary workbook I'd like to close will always have the same filename, but by the time the Userform is called, the primary workbook will have its own, unique name. I also can't be 100% certain that end users won't have any other .xls files open, so I'd like to be as specific as possible when telling it to close one.

Any pointers would be greatly appreciated, many thanks in advance! :)
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

BLaCKouT

New Member
Joined
May 31, 2009
Messages
42
Scott, you're an absolute star, thank you.

(I'd tried Workbook("filename.xls").Close, and had no luck. I'd assumed that as ActiveWorkbook is singular, the specified version would be too. And yes, I know that I should assume nothing in this world)

Thanks again! :)
 

ScottR

Well-known Member
Joined
Apr 17, 2008
Messages
545
Think of it as accessing the collection of workbooks to pick one that you specify by naming it or picking its index value. Same applies to a lot of collections.

These are the annoying trials you'll go through as you build up experience!
 

mshaynerush

Board Regular
Joined
Oct 2, 2012
Messages
96
Scott, you're an absolute star, thank you.

(I'd tried Workbook("filename.xls").Close, and had no luck. I'd assumed that as ActiveWorkbook is singular, the specified version would be too. And yes, I know that I should assume nothing in this world)

Thanks again! :)

why doesn't this work for me, then?
 

Dave01

Board Regular
Joined
Nov 30, 2018
Messages
78
Hi,

this isnt working for me, if I put that line in under my code it says script out of range.

Workbooks("2.P2WPo.XLSX").Close

if I put

Workbooks("C:\Pager Reporting\Pager Source Files\""2.P2WPo.XLSX"").Close

it says

Expected list seperator or )

or a syntax error

nothing seems to work for closing an open excel worksheet

the thing is I may have 3 open at any one time, due to the auto open excel worksheet once its downloaded from SAP, which is so annoying.


so the typical code will look like this

session.findById("wnd[0]/usr/subPARAM_SUB_SCREEN:ZFI_ACCRUAL_REPORT:0100/btn%_R_BLDAT_%_APP_%-VALU_PUSH").press
session.findById("wnd[1]/tbar[0]/btn[24]").press
session.findById("wnd[1]/tbar[0]/btn[8]").press
session.findById("wnd[0]/usr/subPARAM_SUB_SCREEN:ZFI_ACCRUAL_REPORT:0100/ctxtV_LAYOUT").Text = "/OE3"
session.findById("wnd[0]/usr/subPARAM_SUB_SCREEN:ZFI_ACCRUAL_REPORT:0100/ctxtV_LAYOUT").SetFocus
session.findById("wnd[0]/usr/subPARAM_SUB_SCREEN:ZFI_ACCRUAL_REPORT:0100/ctxtV_LAYOUT").caretPosition = 9
session.findById("wnd[0]/tbar[1]/btn[8]").press
session.findById("wnd[0]/usr/shell").pressToolbarContextButton "&MB_EXPORT"
session.findById("wnd[0]/usr/shell").selectContextMenuItem "&XXL"
session.findById("wnd[1]/usr/cmbG_LISTBOX").SetFocus
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[1]/usr/ctxtDY_PATH").Text = "C:\Pager Reporting\Pager Source Files\"
session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "1.P1NoPo.XLSX"
session.findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 8
session.findById("wnd[1]/tbar[0]/btn[11]").press
session.findById("wnd[0]/tbar[0]/okcd").Text = "/n"
session.findById("wnd[0]").sendVKey 0

Workbooks("1.P1NoPo.XLSX").Close


(this leads to a script out of range.)


stumped
 

Watch MrExcel Video

Forum statistics

Threads
1,130,445
Messages
5,642,194
Members
417,259
Latest member
gtacw

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
Top