Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Word > Word VBA

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 03-13-2019, 05:43 AM
matapagi2019 matapagi2019 is offline Windows XP Office 2007
Novice
 
Join Date: Mar 2019
Posts: 8
matapagi2019 is on a distinguished road
Default How to prevent the file from opening again on the second click?

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.
Reply With Quote
  #2  
Old 03-13-2019, 09:46 AM
jjfreedman jjfreedman is offline Windows 10 Office 2016
Advanced Beginner
 
Join Date: May 2012
Location: http://jay-freedman.info
Posts: 36
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
  #3  
Old 03-13-2019, 11:44 AM
matapagi2019 matapagi2019 is offline Windows XP Office 2007
Novice
 
Join Date: Mar 2019
Posts: 8
matapagi2019 is on a distinguished road
Default

@ 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..!
Reply With Quote
Reply

Tags
file, open, vba

Thread Tools
Display Modes


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


All times are GMT -7. The time now is 05:27 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft