Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Visual Basic > VB 2005 > Visual Basic 2005 Basics
|
Visual Basic 2005 Basics If you are new to Visual Basic programming with version 2005, this is the place to start your questions. For questions about the book: Beginning Visual Basic 2005 by Thearon Willis and Bryan Newsome, ISBN: 0-7645-7401-9 please, use this forum instead.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Visual Basic 2005 Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 1st, 2008, 07:09 PM
Registered User
 
Join Date: Jul 2008
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default Detect if Excel is already Open

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
 
Old November 1st, 2008, 07:46 PM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

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)
 
Old November 1st, 2008, 08:09 PM
Registered User
 
Join Date: Jul 2008
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Open Excel Spreadsheet sektor VBScript 1 June 28th, 2010 11:13 PM
open excel file C# ? npqk VS.NET 2002/2003 2 November 26th, 2004 10:31 AM
Open Second excel session magali VB How-To 0 September 27th, 2004 08:34 AM
How can i open Excel From VB6 chiefouko Beginning VB 6 1 June 30th, 2003 08:28 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.