VBA Code to switch Active Workbook

Topic

New Reply

I have a VBA code working perfectly except, at the end, I need to close a workbook (which isn’t the active one.)

I can successfully use the following to close a workbook when I hard code the filename, but I need to use a variable name:

Windows(“Fielda.xls”).Activate
ActiveWorkbook.Close

BUT I need the “Fielda.xls” to be a variable. The user chose which file to open earlier in the procedure, and I just need to close whichever one they had opened.

Thanks

Reply | Quote

Replies

Hi,
The simplest thing would be to store the name of the file in a variable when it is opened. If the opening of the file is part of your procedure, it shouldn’t be too hard.
Hope that helps.

Reply | Quote

Another thing you could do is associate a variable with the workbook itself, eg

Dim MyWorkbook as Workbook
… Open workbook …
set MyWorkbook = ActiveWorkbook
… more code …
MyWorkbook.close

You can then use MyWorkbook whereever you would use Workbooks(“FieldA.xls”) or Activeworkbook, and you don’t have to worry about activating it, ie
MyWorkbook.Activate
Activeworkbook.close
is the same as
MyWorkbook.close

Jon

Reply | Quote

Thank you Jon!

This really helped me. I did have problems trying to use the dim statement, however. When I tried:
Dim Myworkbook as Workbook
set Myworkbook = ActiveWorkbook

It didn’t seem to work with the exact syntax as above.
MyWorkbook = ActiveWorkbook.Name
MsgBox MyWorkbook

Thanks…

Reply | Quote

What didn’t work? What happened when you tried it? When you Dim MyWorkbook as Workbook, MyWorkbook is a Workbook object, not the name of a workbook. To display the name you should be able to do”

    Msgbox MyWorkbook.Name

However, you must use the Set statement to set MyWorkbook’s value. This code should display the active workbook’s name:

Dim MyWorkbook As Workbook
    Set MyWorkbook = ActiveWorkbook
    MsgBox MyWorkbook.Name

Reply | Quote

I don’t know if you are familiar with Excel’s GetOpenFilename method? It allows you to immediately have the filename in a variable.

e.g.

FileName = Application.GetOpenFilename (FileFilter:= sFilter, Filterindex:= Iindex, Title:=sTitle)

where sFilter is a string e.g. “Excel files (*.xls), *.xls, All Files(*.*), *.*”, Iindex is an integer giving the index of sFilter e.g. Iindex = 1 or 2 in my example, because there are only two filters installed, and sTitle is a string e.g. sTitle = “Select a file”

this method opens a dialog box (as you are used to see in a Windows environment) that allows you to select a Filename and the filename is then put in the variable.

Reply | Quote