Ad:

Visual Basic 4 / 5 / 6 Discussion Thread
Marked Solved
Views: 8960

Aug 13th, 2008

Help! Check to see if Excel file is Open

Expand Post »

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.

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
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
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

Last edited by Tekmaven; Aug 14th, 2008 at 4:08 pm. Reason: Code tags

Similar Threads

Open Excel file from Visual Basic in Visual Basic 4 / 5 / 6
opening an Excel file in VB.NET in VB.NET
How to retrieve data from one form to another/ through Data Base in VB.NET
Open Excel file,Error in C++
How to read/write to an MS-Excel file?? in C++

snurd

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

13 posts
since Aug 2008

Permalink

Aug 14th, 2008

Re: Help! Check to see if Excel file is Open

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.

aktharshaik

Reputation Points: 26

Solved Threads: 40

Posting Whiz

Offline

316 posts
since Aug 2008

Permalink

Aug 14th, 2008

Re: Help! Check to see if Excel file is Open

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.

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
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
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

Reputation Points: 26

Solved Threads: 40

Posting Whiz

Offline

316 posts
since Aug 2008

Permalink

Aug 14th, 2008

Re: Help! Check to see if Excel file is Open

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

snurd

snurd

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

13 posts
since Aug 2008

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in Visual Basic 4 / 5 / 6 Forum Timeline: Openig an Excel File

Next Thread in Visual Basic 4 / 5 / 6 Forum Timeline: Excel Date/Time Format

DaniWeb Message

Cancel Changes

User Name
Password