VBA Command to close specific workbook?

[BLaCKouT]

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!

 

[ScottR]

workbooks("workbookname").close

 

[BLaCKouT]

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]

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]

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]

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