#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
@ 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..! |
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 |