How to prevent the file from opening again on the second click?

matapagi2019 · #1 03-13-2019, 05:43 AM

When the user clicks "Button1" once, the file "MyAgenda.xlsx" opens.
How do I prevent Excel from opening the file "MyAgenda.xlsx" when the user clicks "Button1" on the second click, third, and so on ?
Because from the following code, the file "MyAgenda" continues to open again and again every time the user clicks on the second one and so on.

Code:

Private Sub Button1_Click()
Dim ObjExcel
Dim ObjXls    
    Set ObjExcel = CreateObject("Excel.Application")
    Set ObjXls = ObjExcel.Workbooks.Open("D:\MyAgenda.xlsx")
    ObjExcel.Visible = True
End Sub

For all assistance, I am very grateful.

jjfreedman · #2 03-13-2019, 09:46 AM

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

matapagi2019 · #3 03-13-2019, 11:44 AM

@ jjfreedman

Wow..it worked.
Seem it's so easy for you as an expert.
Thank you for two things, your help and your fast response.
Hopefully you don't mind if I ask again next time so I can be proficient like you.
Thanks a lot..!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
VBA Code to prevent word document trying to open links at opening	John Pryor	Word VBA	4	07-17-2018 01:51 AM
Prevent 'click anywhere in slide'	tradedog	PowerPoint	2	06-12-2017 10:17 AM
Any way to prevent mail appearing as read after opening from a desktop alert?	pumpkin_feet	Outlook	0	10-24-2011 06:55 AM
Prevent Appointment Item from opening?	happyaslarry	Outlook	0	01-06-2011 08:46 PM
Prevent highlighted only message from opening	Rickkimbrell	Outlook	2	12-16-2009 07:42 AM