Closing workbook with dynamic name

Flick · Jan 12th 2006

I'm looking for some code to activate and close a workbook with a dynamic name.
Eg. I have a workbook, BOOK1.xls with a macro that opens a 2nd workbook, DYNAMIC-NAME.xls based on a name selected from a userform: eg.

Code

file_dir2 = "C:\data\dailydata\" 
File_Name2 = file_dir2 & Me.CmbStationName.Text & ".xls"
Workbooks.Open File_Name2

After opening DYNAMIC-NAME.xls, I reactivate BOOK1 for editing. I then need to close the DYNAMIC-NAME.xls.

I've tried

Code

Workbooks.close File_Name2

and

Code

Workbooks("Me.CmbStationName.text.xls").Close

to no avail
Any ideas?

Cheers,
Flick.

Flick · Jan 12th 2006

Re: Closing workbook with dynamic name

I think I've worked out the answer from another query that I missed when I did a search...

Code

file_dir2 = "C:\data\dailydata\" 
File_Name2 = file_dir2 & Me.CmbStationName.Text & ".xls" 
set wb=Workbooks.Open File_Name2

then

Code

wb.close

Simple... Why couldn't I think of that?!

Cheers,
Flick

max_lux · Jan 12th 2006

Re: Closing workbook with dynamic name

If you want to close the other workbook, which would be the inactive window(of any name, therefore dynamic), assuming you only have two workbooks open :

Code

Sub CloseOther()
     ActiveWindow.ActivateNext
    ActiveWorkbook.Close
End Sub

or (which is better, but must have a static name), no matter how many workbooks are open:

Code

Sub CloseOther()
    Windows("WorkbookName.xls").Activate
    ActiveWorkbook.Close
End Sub

Let me know if this wasn't what you were trying to do. I may have misunderstood what you want to do.

Cheers,

Closing workbook with dynamic name

Participate now!

Share