Solved

Check if excel is already open

Posted on 2016-09-15
4
38 Views
Last Modified: 2016-09-15
Hello,
I n vb.net 2015 how to check if excel file is already open ?

Cheers
0
Comment
Question by:RIAS
  • 3
4 Comments
 
LVL 6

Expert Comment

by:DPatel
ID: 41799450
Try making a function that will check to see if the workbook is already open or not, like this:

Private Shared Function IsWorkbookAlreadyOpen(app As Excel.Application, workbookName As String) As Boolean
    Dim isAlreadyOpen As Boolean = True

    Try
        app.Workbooks.get_Item(workbookName)
    Catch theException As Exception
        isAlreadyOpen = False
    End Try

    Return isAlreadyOpen
End Function

Then you can use it in your code like this:

Private Sub btnMinSummaryWorksheet_Click(sender As Object, e As EventArgs) Handles btnMinSummaryWorksheet.Click
    'This procedure runs when the btnOpenSummaryWorksheet button is clicked. Calls the
    'Sub procedure opens the Summary Worksheet Dashboard

    Dim xlApp As New Excel.Application
    xlApp.Visible = True

    Dim xlBook As Excel.Workbook
    Dim workbookName = "F:\Test Environment\Compensation Workbook\Compensation Workbook\bin\Debug\2011.1004.Compensation Template.xlsx"
    If IsWorkbookAlreadyOpen(xlApp, workbookName) Then
        xlBook = xlApp.Workbooks.get_Item(workbookName)
    Else
        xlBook = xlApp.Workbooks.Open(workbookName)
    End If

    Dim xlSheet As Excel.Worksheet
    xlSheet = CType(xlBook.Sheets("SummaryWorksheet"), Worksheet)
    xlSheet.Activate()

    Me.Close()
End Sub
0
 
LVL 6

Accepted Solution

by:
DPatel earned 500 total points
ID: 41799455
Also you can write like this:

jst Check it out this work properly

Imports Microsoft.Office.Interop

Public Class Form1 Private moExcelApplication As Excel.Application Private moExcelWorkBook As Excel.Workbook Private moWorkBooks As Excel.Workbooks Private moActivesheet As Excel.Worksheet Private moWorkSheets As Excel.Sheets Private moCurrentRange As Excel.Range

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ShowDirect.Click
    System.Diagnostics.Process.Start("C:\Program Files\Microsoft Office\Office12\EXCEL.EXE")
End Sub

Private Sub Showobj_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Showobj.Click
    Dim sCount As String

    Try
        Cancel.Enabled = True
        If isopen() = 1 Then
            Call OpenExcelFile()
            moActivesheet = moExcelWorkBook.ActiveSheet
            moCurrentRange = moActivesheet.Range("a1", "e5")
            For i As Integer = 1 To 5
                For j As Integer = 1 To 5
                    sCount = i.ToString + j.ToString
                    moActivesheet.Cells(i, j).value = sCount
                Next
            Next
        Else

            MsgBox("already open")

        End If

    Catch ex As Exception

    End Try


End Sub
Private Declare Auto Function GetWindowThreadProcessId Lib "user32.dll" (ByVal hwnd As IntPtr, _
          ByRef lpdwProcessId As Integer) As Integer


Private Sub Cancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Cancel.Click
    'Dim oclass As Collection
    'Try
    '    Showobj.Enabled = True
    '    oclass = New Collection
    '    oclass.moExcelApplication = moExcelApplication
    '    oclass.moExcelWorkBook = moExcelWorkBook
    '    oclass.moCurrentRange = moCurrentRange
    '    oclass.CloseExcelFile()


    'Catch ex As Exception

    'End Try

    Try
        If (Not moExcelWorkBook Is Nothing) Then
            If (Not moExcelApplication Is Nothing) Then
                moExcelApplication.DisplayAlerts = False
                moExcelWorkBook.Nothing() = True

            End If
        End If

        If (Not moExcelApplication Is Nothing) Then
            moExcelApplication.DisplayAlerts = True
            moExcelWorkBook.Nothing() = True

        End If

    Catch ex As Exception

    Finally

        If moExcelApplication.Visible = True Then
            moExcelWorkBook.Close(False)
        Else
            MsgBox("Excel already closed")
        End If
        recordclear(moCurrentRange)
        recordclear(moActivesheet)
        recordclear(moWorkSheets)
        recordclear(moExcelWorkBook)
        recordclear(moWorkBooks)
        moExcelApplication.Quit()
        recordclear(moExcelApplication)
        System.Threading.Thread.Sleep(500)
        MessageBox.Show("Excel Closed")
        Cancel.Enabled = False
    End Try



End Sub




Private Sub OpenExcelFile()
    Try
        moExcelApplication = New Excel.Application
        moWorkBooks = moExcelApplication.Workbooks

        If Not (moExcelApplication Is Nothing) Then
            If Not moWorkBooks Is Nothing Then
                moExcelWorkBook = moWorkBooks.Open("C:\Documents and Settings\anand\Desktop\amit2.xlsx")
                moExcelApplication.Visible = True
            End If
        End If
    Catch ex As Exception
    End Try
End Sub
Function isopen()

    On Error Resume Next
    moExcelWorkBook.Open("C:\Documents and Settings\anand\Desktop\amit2.xlsx")
    If Not moExcelWorkBook Is Nothing Then
        moExcelWorkBook.Nothing() = False
        On Error GoTo -1
        If moExcelWorkBook.ConnectionsDisabled = True Then
            Return 1
        Else
            Return 0
        End If
    Else 'It is open
        moExcelWorkBook = Nothing
        On Error GoTo 0
        Return 1
    End If

End Function

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Showobj.Enabled = True
    Cancel.Enabled = False
End Sub

Private Sub recordclear(ByVal o As Object)
    Try
        Do Until _
        System.Runtime.InteropServices.Marshal.ReleaseComObject(o) <= 0
        Loop
    Catch

    Finally
        o = Nothing
    End Try


End Sub
End Class
1
 

Author Comment

by:RIAS
ID: 41799461
Thanks,

 xlApp.Workbooks.get_Item ---error not a member of workbooks?
0
 
LVL 6

Expert Comment

by:DPatel
ID: 41799486
usually it can be get solved by cleaning and rebuilding the application
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

948 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now