Detect if Excel is already Open

jdhock8865 · November 1st, 2008, 07:09 PM

Hello,
I am creating an application and one of the functions is to read from and write to an excel file. I have written procedures to do both but I ran into a problem with the write procedure. If the excel file that I want to write to is already open the procedure that I wrote does not work correctly. I have been searching on the internet for several days now and have not found a solution to this problem. Is there a way to detect if an excel workbook is open?

Thanks in advance for any help.

Below is the code that I wrote for the write procedure:

Code:

Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click

        Dim MyExcelApp As New Excel.Application
        Dim CaseLogWorkbook As Excel.Workbook
        Dim strCaseLogPath As String

        ClearLabels()

        Me.lblUnderwriter.Text = Me.cmbUnderwriter.Text
        Me.lblSalesSupport.Text = Me.cmbSalesSupport.Text
        Me.lblAccountExecutive.Text = Me.cmbAccountExecutive.Text
        Me.lblSMUContact.Text = Me.cmbSMUContact.Text

        strCaseLogPath = frmCreateCase.TreeView1.SelectedNode.Name & "\Case Log.xls"

        Try
            CaseLogWorkbook = MyExcelApp.Workbooks.Open(strCaseLogPath)
            Select Case strProductType
                Case "Medical"
                    CaseLogWorkbook.Sheets("Home").range("Home_MedUnderwriter").value = Me.lblUnderwriter.Text
                    CaseLogWorkbook.Sheets("Home").range("Home_MedSalesSupport").value = Me.lblSalesSupport.Text
                    CaseLogWorkbook.Sheets("Home").range("Home_MedAE").value = Me.lblAccountExecutive.Text
                    CaseLogWorkbook.Sheets("Home").range("Home_SMUContact").value = Me.lblSMUContact.Text
                Case "Dental"
                    CaseLogWorkbook.Sheets("Home").range("Home_DenUnderwriter").value = Me.lblUnderwriter.Text
                    CaseLogWorkbook.Sheets("Home").range("Home_DenSalesSupport").value = Me.lblSalesSupport.Text
                    CaseLogWorkbook.Sheets("Home").range("Home_DenAE").value = Me.lblAccountExecutive.Text
                    CaseLogWorkbook.Sheets("Home").range("Home_SMUContact").value = Me.lblSMUContact.Text
                Case "GI"
                    CaseLogWorkbook.Sheets("Home").range("Home_GIUnderwriter").value = Me.lblUnderwriter.Text
                    CaseLogWorkbook.Sheets("Home").range("Home_GISalesSupport").value = Me.lblSalesSupport.Text
                    CaseLogWorkbook.Sheets("Home").range("Home_GIAE").value = Me.lblAccountExecutive.Text
                    CaseLogWorkbook.Sheets("Home").range("Home_SMUContact").value = Me.lblSMUContact.Text
            End Select
            MessageBox.Show("The Contact Information has been successfully updated/changed.", "Case Log", MessageBoxButtons.OK, MessageBoxIcon.Information)
            CaseLogWorkbook.Save()
            CaseLogWorkbook.Close()

        Catch
            MessageBox.Show("There was a problem updating the Case Log." & _
            vbCrLf & "Please contact the Help Desk.", "Case Log Connection Problem.", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Me.Dispose()
        End Try

        CaseLogWorkbook = Nothing
        MyExcelApp = Nothing


    End Sub

J. David Hock

Shasur · November 1st, 2008, 07:46 PM

Hi

One way is to check if the Book is ReadOnly

If CaseLogWorkbook.ReadOnly then

End if

Can you try if it works for you

Cheers
Shasur

http://www.dotnetdud.blogspot.com

VBA Tips & Tricks (http://www.vbadud.blogspot.com)

jdhock8865 · November 1st, 2008, 08:09 PM

Hi Shasur,
That worked perfectly!! Thank you so much. I have been searching for over 2 days for a solution to this problem, I could not even fall asleep last night.

Thanks Again!

J. David Hock