Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Checking for an active workbook

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I want to be able to check whether a workbook is already in memory so that my macro can either switch to it or load it up from the network.

    [ This Message was edited by: mcgrathm on 2002-03-19 08:24 ]

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try the following code:

    BookName = "Insert Workbook name to check"
    BookCount = Workbooks.Count
    For i = 1 To BookCount
    If BookName = Workbooks(i).Name Then
    BookCheck = 1
    GoTo 1
    Else: BookCheck = 2
    End If
    Next i
    1 If BookCheck = 1 Then MsgBox "Workbook is loaded"
    If BookCheck = 2 Then Application.GetOpenFilename

    Insert the Workbook name to check and then the code checks to see if the workbook is open, if it is not open then it opens the file open dialog box.
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Another suggestion:

    Function BookOpen(wbName as String) as Boolean
    Dim wb as Workbook
    On Error Resume Next
    Set wb = Workbooks(wbName)
    BookOpen = Not (Err.Number > 0)
    Exit Function

    You can use it like this:

    BookOpen("MyBook.xls")

    it should run much faster.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks very much for the code, very useful

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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