Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    Tullahoma, Tennessee, USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA Code to switch Active Workbook

    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

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: VBA Code to switch Active Workbook

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code to switch Active Workbook

    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

  4. #4
    New Lounger
    Join Date
    Jan 2001
    Location
    Tullahoma, Tennessee, USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code to switch Active Workbook

    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...

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code to switch Active Workbook

    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"

    <pre> Msgbox MyWorkbook.Name
    </pre>


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

    <pre>Dim MyWorkbook As Workbook
    Set MyWorkbook = ActiveWorkbook
    MsgBox MyWorkbook.Name
    </pre>

    Legare Coleman

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code to switch Active Workbook

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •