14 Replies - 10111 Views - Last Post: 13 February 2012 - 05:02 AM Rate Topic: -----

#1 nighttrain  Icon User is offline

  • D.I.C Regular

Reputation: 7
  • View blog
  • Posts: 340
  • Joined: 22-September 10

Check if Workbook is open

Posted 08 February 2012 - 11:08 AM

Hi there all good people,

I have got problem, I got function which should correctly check if Woorkbook is open or not, but anyway if i got open my excel file or not I always get False Value in MsgBox. I don't know where is a problem. Please check it:

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

    Function IsXLBookOpen(strName As String) As Boolean

        'Function designed to test if a specific Excel
        'workbook is open or not.

        Dim i As Long
        Dim XLAppFx As Excel.Application
        Dim NotOpen As Boolean

        'Find/create an Excel instance
        On Error Resume Next
        XLAppFx = GetObject(, "Excel.Application")
        If Err.Number = 429 Then
            NotOpen = True
            XLAppFx = CreateObject("Excel.Application")
            Err.Clear()
        End If

        'Loop through all open workbooks in such instance
        For i = XLAppFx.Workbooks.Count To 1 Step -1
            If XLAppFx.Workbooks(i).name = strName Then Exit For
        Next i

        'Set all to False
        IsXLBookOpen = False

        'Perform check to see if name was found
        If i <> 0 Then IsXLBookOpen = True

        'Close if was closed
        If NotOpen Then XLAppFx.Quit()

        'Release the instance
        XLAppFx = Nothing

    End Function




    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click


        MsgBox(IsXLBookOpen("G:/test.xls"))
    End Sub
End Class



with best regards

Is This A Good Question/Topic? 0
  • +

Replies To: Check if Workbook is open

#2 demausdauth  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 178
  • View blog
  • Posts: 652
  • Joined: 03-February 10

Re: Check if Workbook is open

Posted 08 February 2012 - 11:17 AM

When you set the Function Name to false you essentially trigger the return. So by setting it to False and then trying to do more work you have already returned false.

What you need is a return value that you set as the work progresses and then at the end of the function return that value.

Also get rid of the VB6 On Error you should be using Try ... Catch statements instead.
Was This Post Helpful? 0
  • +
  • -

#3 _HAWK_  Icon User is offline

  • Master(Of Foo)
  • member icon

Reputation: 1057
  • View blog
  • Posts: 4,092
  • Joined: 02-July 08

Re: Check if Workbook is open

Posted 08 February 2012 - 11:18 AM

It would be better function to return the excel object if it is not open rather than make the object and then close it - which you will run into problems as you are not using Marshal.FinalReleaseComObject which is the standard for closing/disposing Word/Excel objects properly. I would simply check for the "EXCEL.EXE" process and kill it. Then create the new Excel object.
Was This Post Helpful? 0
  • +
  • -

#4 nighttrain  Icon User is offline

  • D.I.C Regular

Reputation: 7
  • View blog
  • Posts: 340
  • Joined: 22-September 10

Re: Check if Workbook is open

Posted 08 February 2012 - 11:31 AM

The Point is I need a function which will be checking if x excels are open or closed and give message of it. For example if I have got file1.xls, file2.xls, file3.xls Then I want to check if it open or close and message. Could You help me with that?
Was This Post Helpful? 0
  • +
  • -

#5 _HAWK_  Icon User is offline

  • Master(Of Foo)
  • member icon

Reputation: 1057
  • View blog
  • Posts: 4,092
  • Joined: 02-July 08

Re: Check if Workbook is open

Posted 08 February 2012 - 11:52 AM

So you need an array for the names of the files to check. You loop thru the processes for each one that is "EXCEL.EXE" then check the mainWindowTitle for the file name then give message and kill that process with the users choice when using the MessageBox.Show's DialogResult.
Was This Post Helpful? 0
  • +
  • -

#6 nighttrain  Icon User is offline

  • D.I.C Regular

Reputation: 7
  • View blog
  • Posts: 340
  • Joined: 22-September 10

Re: Check if Workbook is open

Posted 08 February 2012 - 12:41 PM

I correct loop in processess, but I really don't know how to equequal instances of excel and his name to my array table.

Dim WoorkBookNames() As String = {"test1.xls", "test2.xls", "test3.xls"}

        Dim xlApp As Excel.Application
        Dim Woorkbook As Excel.Workbooks
        Dim i As Byte
        i = 0

        Dim p As System.Diagnostics.Process

        For Each p In System.Diagnostics.Process.GetProcesses()
            If p.ProcessName = "EXCEL" Then
                i = i + 1
            End If
        Next

        If i > 0 Then
            MsgBox(i)
        End If


Was This Post Helpful? 0
  • +
  • -

#7 _HAWK_  Icon User is offline

  • Master(Of Foo)
  • member icon

Reputation: 1057
  • View blog
  • Posts: 4,092
  • Joined: 02-July 08

Re: Check if Workbook is open

Posted 08 February 2012 - 01:10 PM

Dim WorkBookNames() As String = {"test1.xls", "test2.xls", "test3.xls"}
For Each p As System.Diagnostics.Process In System.Diagnostics.Process.GetProcesses()
  If p.ProcessName = "EXCEL" Then
     For Each excelFile As String In WorkBookNames
        If p.MainWindowTitle.Contains(excelFile) Then
           If MessageBox.Show("<set parameters>") = DialogResult.OK Then
              p.Kill()
           End If
        End If
     Next
  End If
Next


Was This Post Helpful? 1
  • +
  • -

#8 nighttrain  Icon User is offline

  • D.I.C Regular

Reputation: 7
  • View blog
  • Posts: 340
  • Joined: 22-September 10

Re: Check if Workbook is open

Posted 08 February 2012 - 03:13 PM

Ok it works fine, but what I've seen is that not only the files of array was killed. The rest of my open excel files was killed too. For example anotherexcel.xls.

Anyway I wonder How to change that code, to show message if is open for example text1.xls, and not open test2.xls and test3.xls I just want to inform user that he has to open all 3 files, if not there will be a message to require to open rest files. Again please of Your great help.

regards
Was This Post Helpful? 0
  • +
  • -

#9 _HAWK_  Icon User is offline

  • Master(Of Foo)
  • member icon

Reputation: 1057
  • View blog
  • Posts: 4,092
  • Joined: 02-July 08

Re: Check if Workbook is open

Posted 08 February 2012 - 03:36 PM

I was using the .Contains function as some versions of Word add the [compatibility mode] to it. You might try the .Equals method if the titles are too similar.
Was This Post Helpful? 0
  • +
  • -

#10 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Check if Workbook is open

Posted 08 February 2012 - 04:17 PM

OK, so you have multiple excel documents open and you want to check if their open and close them based on a specified document name? Is that correct?

Since they're open you should be able to check if they're read-only and respond to that. I think this code would return the proper boolean value based on that information.
    Function IsXLBookOpen(strName As String) As Boolean
        Dim xl As New Excel.Application
        Dim wb As Excel.Workbook
        xl = New Excel.Application

        wb = xl.Workbooks.Open(strName)
        If wb.ReadOnly = True Then
            Return True
        Else
            Return False
        End If
 end function

Then in your code, handle it based on the return result.

But also, look into what _HAWK_ was saying about Marshal.FinalReleaseComObject or your test results are going to be confusing based on you not properly getting the processes closed.

This post has been edited by CharlieMay: 08 February 2012 - 05:08 PM

Was This Post Helpful? 0
  • +
  • -

#11 nighttrain  Icon User is offline

  • D.I.C Regular

Reputation: 7
  • View blog
  • Posts: 340
  • Joined: 22-September 10

Re: Check if Workbook is open

Posted 09 February 2012 - 02:30 AM

Ok, but that code not work properly I think, I don't receive any message. I add ofcourse that:

Private Sub Button7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button7.Click
        IsXLBookOpen("D:\test1.xls")


    End Sub


This post has been edited by nighttrain: 09 February 2012 - 02:31 AM

Was This Post Helpful? 0
  • +
  • -

#12 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Check if Workbook is open

Posted 09 February 2012 - 05:13 AM

well, since you're calling a function, you will need to assign a return variable to catch the value of the return or for testing purposes display it from a messagebox, try changing the code above to
MessageBox.Show(isXLBookOpen("D:\test1.xls"))

Was This Post Helpful? 0
  • +
  • -

#13 nighttrain  Icon User is offline

  • D.I.C Regular

Reputation: 7
  • View blog
  • Posts: 340
  • Joined: 22-September 10

Re: Check if Workbook is open

Posted 10 February 2012 - 02:39 AM

I make another code. First i manually open some excel files, my code checking how much files are open. I declare an array of string with 3 files: excel1.xls, excel2.xls, excel3.xls. Now i want to check if the all 3 of files are open, if nothing or only 1 or 2 of this files it should be a message. But something is wrong with my code. I always get 3 communicats even if i open those 3 files or only 2 or 1 sometimes twice. Please of help.

Dim nazwa As New List(Of String)

        Dim excelApp As Microsoft.Office.Interop.Excel.Application = Marshal.GetActiveObject("Excel.Application")
        MessageBox.Show(excelApp.Workbooks.Count.ToString)
        For i As Integer = 1 To excelApp.Workbooks.Count
            nazwa.Add(excelApp.Workbooks(i).FullName)
            'nazwa = MessageBox.Show(excelApp.Workbooks(i).FullName)
        Next

Dim list As New List(Of String) From {"D:\test1.xls", "D:\test2.xls", "D:\test3.xls"}


For Each element In nazwa

            For Each items In list
                'ListBox3.Items.Add(element)
                If element <> items Then
                    MsgBox("Proszę otworzyć " & items & " wszystkie potrzebne pliki")
                Else
                    MsgBox("All of 3 files are open")
                End If
            Next
        Next

excelApp.Visible = False
excelApp.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp)


Was This Post Helpful? 0
  • +
  • -

#14 nighttrain  Icon User is offline

  • D.I.C Regular

Reputation: 7
  • View blog
  • Posts: 340
  • Joined: 22-September 10

Re: Check if Workbook is open

Posted 13 February 2012 - 01:07 AM

Any help ?:/
Was This Post Helpful? 0
  • +
  • -

#15 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Check if Workbook is open

Posted 13 February 2012 - 05:02 AM

So in this new section of code why would you not use your Function that started this thread and check against that?
In other words, as the code loops through the list of filenames you would then check against it
If isXLBookOpen(listitem of your list) then
 'store your item to another list because it's open
End If


'Then after you've went through your list, iterate through the other list and build a string using string.format so that you can display the open workbooks in a messagebox.

This post has been edited by CharlieMay: 13 February 2012 - 05:03 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1