View Single Post
 
Old 03-13-2019, 09:46 AM
jjfreedman jjfreedman is offline Windows 10 Office 2016
Advanced Beginner
 
Join Date: May 2012
Location: https://jay-freedman.info
Posts: 39
jjfreedman is on a distinguished road
Default

Every time your code runs, it creates a new instance of Excel and opens a copy of the workbook in it. To prevent that, you need to (a) open a new instance of Excel only if the program isn't already running and (b) exit the procedure without opening the workbook if Excel is already running and does contain the workbook.

Code:
Private Sub Button1_Click()
Dim ObjExcel
Dim ObjXls
Dim tmpXls
Dim strFname As String

    strFname = "D:\MyAgenda.xlsx"

    ' try to get Excel object if it's already open
    On Error Resume Next
    Set ObjExcel = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
        ' Excel was not running
        Set ObjExcel = CreateObject("Excel.Application")
        ' there can't be any open workbooks yet, so open it
    Else
        ' Excel was running. Is our workbook already open?
        For Each tmpXls In ObjExcel.workbooks
            ' if it's there, just stop
            If LCase(tmpXls.FullName) = LCase(strFname) Then Exit Sub
        Next tmpXls
    End If
    
    ' if you get here, Excel is now running and doesn't have the workbook open yet
    Set ObjXls = ObjExcel.workbooks.Open(strFname)
    ObjExcel.Visible = True
End Sub
Reply With Quote