Forum Discussion

MulgraveTester's avatar
MulgraveTester
Frequent Contributor
13 years ago

Determine if Excel workbook is already open

How do I determine if an Excel workbook is already open? Any help would be appreciated - thanks.



I have tried the two methods below.

Test1 fails if the workbook is not open because it can't find the object.

Test2 fails if the workbook is open because workbooks are not parameterised.



'VBScript

sub test1 'Reference the workbook directly

    set Excel = sys.OleObject("Excel.Application")

    delay 3000

    excel.Visible = true

    if not (Excel.workbooks("myworkbook.xls") is nothing) then

        log.message("Workbook is already open")

    end if

end sub



sub test2 'Search for the workbook in the collection of open workbooks

    set Excel = sys.OleObject("Excel.Application")

    delay 3000

    excel.Visible = true

    for i = 0 to Excel.Workbooks.count -1

        if Excel.workbooks(i).name = "myworkbook.xls" then

            log.message("Workbook is already open")

        end if

    next

end sub

8 Replies

Sort By
  • irina_lukina's avatar
    irina_lukina
    Super Contributor
    13 years ago

    Hi Michael,


    Try using the following code snippet:





    ' returns TRUE if the workbook is open 

    function WorkbookOpen(WorkBookName)

        Set Excel = Sys.OleObject("Excel.Application")

        WorkbookOpen = false

        count =  Excel.Workbooks.Count

        ' Iterate through the workbooks collection

        ' Note that this collection is not zero-based

        For i = 1 to count

        if (Excel.Workbooks.Item(i).Name = WorkBookName) then

            WorkbookOpen = true

        end if

        Next

    end function


    sub Main

      MyWorkbookName = "myworkbook.xls"

      if (WorkbookOpen(MyWorkbookName)) then

        Log.Message("The workbook is open")

      else

        Log.Message("The workbook is not open")

      end if

    end sub



    Does this help?

  • keerthana_annau's avatar
    keerthana_annau
    New Contributor
    11 years ago
    I need to find out whether a particular excel file is opened or not.



    I tried Michael's code,

    Test1 gives error even if the workbook is open, vb run time error - subscript out of range.



    I also tried with     Irina's Code, the count variable is set to zero even if there are some excel workbooks opened.



    Where am going wrong in the code? 
  • murugans1011's avatar
    murugans1011
    Regular Contributor
    11 years ago
    Check whether u have mentioned correct workbook name with proper extension ( *.xls or ".xlsx)



        MyWorkbookName = "myworkbook.xls"
  • murugans1011's avatar
    murugans1011
    Regular Contributor
    11 years ago
    try using this......



    function WorkbookOpen(WorkBookName)

    dim app



        Set app = sys.OleObject("Excel.Application")

     

        WorkbookOpen = false

     

        count = app.Workbooks.Count

     

     

        For i = 1 to count

     

        if (app.Workbooks.Item(i).Name = WorkBookName) then

     

            WorkbookOpen = true

     

        end if

     

        Next

     

    end function



    sub Main

     

      MyWorkbookName = "test.xlsx"

     

      if (WorkbookOpen(MyWorkbookName)) then

     

        Log.Message("The workbook is open")

      else

        Log.Message("The workbook is not open")

      end if

     

    end sub





  • prasad_kumar_s's avatar
    prasad_kumar_s
    Contributor
    11 years ago
     

    Code returns count when the opened workbook name matches with "myworkbook.xls". Do you have a workbook with the name "myworkbook.xls" opened?





     MyWorkbookName = "myworkbook.xls"



  • murugans1011's avatar
    murugans1011
    Regular Contributor
    11 years ago
    what does " --  app.Workbooks.Count -- " returns if some work is opened?