Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a program that gives users the choice of two data source workbooks to work with. Before opening one, however, I need to check to make sure the other one is closed, and vice versa. Here's the code I'm using in that portion of the program:

    Dim strSpreadsheetname as string
    Dim strSpreadsheetpath as string

    strSpreadsheetname = "Local Reviews10.xls"
    strSpreadsheetpath = "S:xxx\xxx\xxx\Local Reviews10.xls"

    If application.worksheets("FrontPage").obtnFY10.value = True then
    Workbooks ("Local Reviews11.xls").close false
    set wb=workbooks(strSpreadsheetname)
    wb.activate

    Else

    Workbooks("Local Reviews10.xls").close false
    Set wb = workbooks.open("S:xxx\xxx\xxx\Local Reviews11.xls")
    wb.Activate
    End if

    This will open and close the workbooks, but I need to be able to check to see if tey are open before opening them. How do I do that???

    Any help will be greatly appreciated. Thanks..

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Code:
    Sub IsWkBkOpen()
    
    Dim wkbk As Workbook
    Dim MyName As String
     
    MyName = "Blank.xls"
    For Each wkbk In Application.Workbooks
      If wkbk.Name = MyName Then
        MsgBox MyName & " Open"
      End If
    Next wkbk
    
    End Sub
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The closing is actually more of a problem than the opening. I tried using the collection for that and inadvertently closed the workbook containing the running code. How would you suggest I check to see if the workbook is open, and then close it?


  4. #4
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Oh, sorry, I misread your reply. It does do what I need. Thanks!

Posting Permissions

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