VB6.0: Writing an argument to see if an Excel file is already opened before opening the Excel file, because if the file is already opened the Excel file opens as read only. If it's already open I want to skip to the next "goto" label (ie: "File Already Opened").

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
End Sub

Private Sub Command2_Click()

'This does not work
xlTemp = nothing

End Sub

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
        MsgBox "File Not Found."
    End If

    Exit Sub

    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
                    If nFiles = 1 Then          'Confirm that no other workbooks are open and then quit
                    End If
                    Exit For
                End If
        End If
    End If

    Exit Sub

    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
        End If
    End If
    Set xlTemp = Nothing
    Resume cmdClose_Click_Done

End Sub

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.

This question has already been answered. Start a new discussion instead.