Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004
    Posts
    28

    Unhappy Unanswered: how to check wether an Excel workbook is already open or not and search for a value

    Halloo every body
    I have to open an excel sheet and I have to search for a value in it but it is opening the sheet eventhough it is already opened.
    can any one kindly help me how to check wether an excel sheet is already open or not.
    here is my code but it is not working when the file is already opened.

    private sub cmd1_click()
    Dim XL As New Excel.Application
    Dim wbk As New Excel.Workbook
    Dim ws As New Excel.Worksheet

    If Not WorkbookOpen("Book2.xls") Then
    Set wbk = XL.Workbooks.Open("C:\Dokumente und Einstellungen\Kiran Karnati\Desktop\EXCEL\Book2.xls")
    End If
    Set wbk = XL.Workbooks("Book2.xls")
    Set ws = wbk.Worksheets("Sheet1")
    With ws
    Label48.Caption = .Cells(1, 2).Value
    End With
    XL.Visible = True
    'Releasing the SA and Xl Objects is wise TODO
    Set SA = Nothing
    Set XL = Nothing
    End Sub

    Function WorkbookOpen(WorkBookName As String) As Boolean
    'Returns TRUE if the workbook is open
    WorkbookOpen = False
    On Error GoTo WorkBookNotOpen
    If Len(Excel.Workbooks(WorkBookName).Name) > 0 Then
    WorkbookOpen = True
    Exit Function
    End If

    WorkBookNotOpen:
    End Function

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I think you shold look at the "GetObject function" in Access help.

    This explains how to detect if excel is running (look at the example) and how to access it workbooks etc.


    HTH

    MTB

  3. #3
    Join Date
    Aug 2004
    Posts
    28

    Unhappy I found how to make it to work , is it useful for you

    Really am very happy to say that am a member of this forum.
    my programming is working and thanks a million for your help.

    I used that technique that you gave and it was working good if I don't close the file but If the user closes the file and tries to open again by clicking the button then the GETOBJECT() function is not generating any error even the file is not yet opened, so I have used your technique and also did something else to make it work and I want to post my solution here so that no one else will sufer for this problem again.
    Any way if you find that this is bad and any other technique is also possible pelase help me, but at the moment it is working .
    Thank you and here is my solution.
    Code:
    --------------------------------------------------------------------------------

    Private Sub command47_Click()

    Dim XL As New Excel.Application
    Dim wbk As New Excel.Workbook
    Dim ws As Excel.Worksheet
    Dim WorkBookName As String
    Dim chk As Integer
    Dim bool_wkbPresent As Boolean

    chk = 0


    WorkBookName = "Book2.xls"

    If Not WorkbookOpen(WorkBookName) Then
    chk = 1
    Set wbk = XL.Workbooks.Open("C:\Dokumente und Einstellungen\Kiran Karnati\Desktop\EXCEL\Book2.xls")
    Else
    On Error GoTo openExcel
    DoCmd.SetWarnings False
    Set wbk = GetObject("C:\Dokumente und Einstellungen\Kiran Karnati\Desktop\EXCEL\Book2.xls")
    End If

    Work_On_Worksheet:
    Set ws = wbk.Worksheets("Sheet1")
    If chk = 0 Then
    With ws
    Label48.Caption = .Cells(1, 2).Value
    .Cells(1, 3).Select
    End With
    Else
    With ws
    Text49.Value = .Cells(1, 2).Value
    .Cells(1, 6).Select
    End With
    XL.Visible = True
    End If
    GoTo Exit_Work

    openExcel:
    chk = 1
    Set wbk = XL.Workbooks.Open("C:\Dokumente und Einstellungen\Kiran Karnati\Desktop\EXCEL\Book2.xls")
    Resume Work_On_Worksheet


    Exit_Work:
    wbk.Activate
    ws.Visible = xlSheetVisible
    'Releasing the SA and Xl Objects is wise TODO
    Set SA = Nothing
    Set XL = Nothing
    Set wbk = Nothing
    DoCmd.SetWarnings True
    End Sub

    Function WorkbookOpen(WorkBookName As String) As Boolean
    'Returns TRUE if the workbook is open
    Dim myxl As Excel.Application

    On Error Resume Next
    Set myxl = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
    Err.Clear
    WorkbookOpen = False
    Else
    WorkbookOpen = True
    End If
    Set myxl = Nothing

    End Function


    Have fun there
    Kiran Karnati

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •