Consulting

Results 1 to 6 of 6

Thread: checking if some specific workbook is open; if not then it should be opened.

  1. #1

    checking if some specific workbook is open; if not then it should be opened.

    Hello,


    I want to write a code that checks if some specific workbook (wb2) is open already and if not then it should open wb2.

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    What is wb2 exactly? Is it a code object, your name for it, or what?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    Hi xld,

    my workbook is called wb2.csv

    This is my code:

     Sub Import()
    
     Dim fname As String    
     Dim fpath As String     
     Dim wb1 As Workbook
     Dim wb2 As Workbook
    
     Set wb1 = ThisWorkbook
     fpath = wb1.Sheets(Sheet1).Range("Path").Value
     fname = wb1.Sheets(Sheet1).Range("Name").Value
    
     'Open Workbook
        Application.DisplayAlerts = False
        Set wb2 = Workbooks.Open(fpath & "\" & fname)
        Application.DisplayAlerts = True
    
     'code 
     'code


    This opens wb2.csv But I want first check if wb2.csv is already open and if not it should be opened.
    Last edited by Bob Phillips; 12-21-2015 at 04:34 PM. Reason: Added VBA tags

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,785
    Location
    I think something like this

    Sub CheckOpen()
    
        Dim wb As Workbook, wbCSV As Workbook
    
        Set wbCSV = Nothing
    
        For Each wb In Application.Workbooks
            If wb.Name = "wb2.csv" Then
                Set wbCSV = wb
                Exit For
            End If
        Next
    
        If wbCSV Is Nothing Then
            MsgBox "Open it"
        Else
            MsgBox "Already open"
        End If
    
    End Sub
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    Another way
    Dim isOpen As Boolean
    
        On Error Resume Next
        isOpen = CBool(Not Workbooks("wb2.csv") Is Nothing)
        On Error Goto 0
        
        If isOpen Then
            MsgBox "Already open"
        Else
            MsgBox "Open it"
        End If
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #6
    This is perfect thank you !

Posting Permissions

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