Help! Check to see if Excel file is Open

[snurd]

VB6.0
The following is how I open the Excel file, what I need to know is how to see if it is already open, and also a new Sub to close the file.

Private Sub Command1_Click()

'Place statement here to see if already open, if so then Goto 1
Dim xlTemp As excel.Application
Set xlTemp = New excel.Application
xlTemp.Workbooks.Open "c:\Test.xls"
xlTemp.Visible = True
1

End Sub

Private Sub Command2_Click()

'This does not work
xlTemp = nothing
xlTemp.Quit

End Sub

[aktharshaik]

Check Out this code. it may be of some help. i'll give still more effective code later on if u r not able to get what u need. try to use the logic from the sample code given here.

Option Explicit
Private xlTemp As Excel.Application
Private sFileName As String

Private Sub cmdOpen_Click()

    On Error GoTo cmdOpen_Click_Error

    Set xlTemp = New Excel.Application
    sFileName = "C:\TEST.XLS"

    If Dir(sFileName) <> "" Then
        xlTemp.Workbooks.Open sFileName
        xlTemp.Visible = True
    Else
        MsgBox "File Not Found."
    End If

cmdOpen_Click_Done:
    Exit Sub

cmdOpen_Click_Error:
    MsgBox "An Error has occured in Procedure cmdOpen_Click." & vbCrLf & Err.Number & " : " & Err.Description & vbCrLf & vbCrLf & "Contact System Administrator."
    Resume cmdOpen_Click_Done

End Sub

Private Sub cmdClose_Click()

    Dim ret As Integer
    Dim nFiles As Integer
    Dim iVar As Integer

    On Error GoTo cmdClose_Click_Error

    If Not xlTemp Is Nothing Then
        If xlTemp.Visible Then
            nFiles = xlTemp.Workbooks.Count
            For iVar = 1 To nFiles
                If UCase(xlTemp.Workbooks(iVar).Path & "\" & xlTemp.Workbooks(iVar).Name) = UCase(sFileName) Then
                    xlTemp.Workbooks(iVar).Close
                    If nFiles = 1 Then          'Confirm that no other workbooks are open and then quit
                        xlTemp.Quit
                    End If
                    Exit For
                End If
            Next
        End If
    End If

cmdClose_Click_Done:
    Exit Sub

cmdClose_Click_Error:
    MsgBox "An Error has occured in Procedure cmdClose_Click." & vbCrLf & Err.Number & " : " & Err.Description & vbCrLf & vbCrLf & "Contact System Administrator."
    If Not xlTemp Is Nothing Then
        If xlTemp.Visible Then
            xlTemp.Workbooks.Close
            xlTemp.Quit
        End If
    End If
    Set xlTemp = Nothing
    Resume cmdClose_Click_Done

End Sub

[aktharshaik]

Still more effective solution can be done.

First you may need to iterate through all the processes running in the memory to check whether at all if excel is running.

If it is not running then u may create the new instance of the excel application and open your file as usual.

if it is already running, then get the application handle to the excel application already open and check whether ur file to be opened is already opened or not. if yes then display "File Already Open" message, else open the file.

go for some googling at google.com for help regarding iterating through the running processes, getting the window handles of the applications and attaching them to objects, etc.

good luck.
U Work a Little, We Guide U a Little.

[snurd]

Thank you so much "aktharshaik" it really work great.

snurd