cancel
Showing results for 
Search instead for 
Did you mean: 

Determine if Excel workbook is already open

MulgraveTester
Frequent Contributor

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 8
irina_lukina
Super Contributor

RE: Determine if Excel workbook is already open

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?

I’m not a member of the SmartBear Support Team, I’m just helping users in this community. Any views or opinions expressed do not necessarily represent those of SmartBear Software.
MulgraveTester
Frequent Contributor

RE: Determine if Excel workbook is already open

Thanks,



This is what I ended up doing.



cheers,



Michael
keerthana_annau
New Contributor

RE: Determine if Excel workbook is already open

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
Regular Contributor

RE: Determine if Excel workbook is already open

Check whether u have mentioned correct workbook name with proper extension ( *.xls or ".xlsx)



    MyWorkbookName = "myworkbook.xls"
keerthana_annau
New Contributor

RE: Determine if Excel workbook is already open

Ya I have checked it. There is no problem with the extension.
murugans1011
Regular Contributor

RE: Determine if Excel workbook is already open

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
Contributor

RE: Determine if Excel workbook is already open

 

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
Regular Contributor

RE: Determine if Excel workbook is already open

what does " --  app.Workbooks.Count -- " returns if some work is opened?
New Here?
Join us and watch the welcome video:
Announcements
Top Kudoed Authors