|
code
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Excel Execution from VB Fails on 2nd AttemptDuring the first attempt, there is NO Excel object so "IsExcelActive" = False. After manually closing Excel independent of VB, VB appears to be still keeping a reference to the Excel Object as IsExcelActive returns True. Even executing oXL.Quit and setting things to Nothing does NOT appear to clear the previously manually closed Excel as Excel starts but NO New workbook shows and Excel hangs. Any ideas how to code in VB if user closes Excel separately? [CODE] Private Sub cmdGo_Click() If IsExcelActive Then '<< Check for Excel Object oXL.Quit Set oWS = Nothing Set oWB = Nothing Set oXL = Nothing End If Call MExcel.Excel_StartUp(False, True) Call MExcel.AddNewWorkBook Call MExcel.SetExcelForSpeed(True) Call DoAccounting 'Make Excel visible at end for faster processing Call MExcel.Excel_ShowHide(True) Call MExcel.SetExcelForSpeed(False) End Sub [/CODE]
Show quote
Hide quote
"David" <dw85745***@earthlink.net> wrote in message When my app is done I use code similar to:news:%234lVV%23r5JHA.480@TK2MSFTNGP06.phx.gbl... > Code follows. > > During the first attempt, there is NO Excel object so "IsExcelActive" = > False. > > After manually closing Excel independent of VB, > VB appears to be still keeping a reference to the Excel Object as > IsExcelActive returns True. > > Even executing oXL.Quit and setting things to Nothing does NOT appear to > clear the previously > manually closed Excel as Excel starts but NO New workbook shows and Excel > hangs. > > Any ideas how to code in VB if user closes Excel separately? > > [CODE] > Private Sub cmdGo_Click() > > If IsExcelActive Then '<< Check for Excel Object > oXL.Quit > Set oWS = Nothing > Set oWB = Nothing > Set oXL = Nothing > End If > Call MExcel.Excel_StartUp(False, True) > Call MExcel.AddNewWorkBook > Call MExcel.SetExcelForSpeed(True) > > Call DoAccounting > > 'Make Excel visible at end for faster processing > Call MExcel.Excel_ShowHide(True) > > Call MExcel.SetExcelForSpeed(False) > > End Sub > > [/CODE] > objExcel.ActiveWorkbook.Close objExcel.Application.Quit If the spreadsheet were to close manually, or an error halts the program, I use TaskMgr to close the Excel process still running in memory. I'm not aware of any other way.
Show quote
Hide quote
"David" <dw85745***@earthlink.net> wrote in message You should have posted your code for IsExcelActive so we could have see how news:%234lVV%23r5JHA.480@TK2MSFTNGP06.phx.gbl... > Code follows. > > During the first attempt, there is NO Excel object so "IsExcelActive" = > False. > > After manually closing Excel independent of VB, > VB appears to be still keeping a reference to the Excel Object as > IsExcelActive returns True. > > Even executing oXL.Quit and setting things to Nothing does NOT appear to > clear the previously > manually closed Excel as Excel starts but NO New workbook shows and Excel > hangs. > > Any ideas how to code in VB if user closes Excel separately? > > [CODE] > Private Sub cmdGo_Click() > > If IsExcelActive Then '<< Check for Excel Object > oXL.Quit > Set oWS = Nothing > Set oWB = Nothing > Set oXL = Nothing > End If > Call MExcel.Excel_StartUp(False, True) > Call MExcel.AddNewWorkBook > Call MExcel.SetExcelForSpeed(True) > > Call DoAccounting > > 'Make Excel visible at end for faster processing > Call MExcel.Excel_ShowHide(True) > > Call MExcel.SetExcelForSpeed(False) > > End Sub > you're determining that because that's really kind of the key to all of this. But essentially, regardless of how you get a reference to the Excel.Application object (CreateObject, GetObject, or Set <var> = New Excel.Application), if the user closes Excel, your app is going to have a "phantom" (for lack of a better term) reference...IOW, you've still got a reference but it's a reference to something that doesn't exist anymore (the same goes if the user closes a worksheet or workbook for which you have references). If you try to use that reference, you'll get a trappable error. In lieu of that, declare your variables using the WithEvents keyword so you'll have events in which you can write code. Of course, this does require early-binding which could potentially pose a problem if you need to support multiple versions of Excel. Develop against the earliest version of Excel you need to support and you should be OK. Problems usually only arise with early-binding if you develop with a later version and a user has an earlier version. As far as there not being any open workbooks, this is normal if you instantiate Excel via code. Adding a workbook using oXL.Workbooks.Add will add the default 3 worksheets (at least it did with what I just tried using Excel 2007). Here's the code I tried: Option Explicit Private oXL As Excel.Application Private oWB As Excel.Workbook Private oWS As Excel.Worksheet Private Sub Form_Click() MsgBox oWS.Name End Sub Private Sub Form_Load() Set oXL = New Excel.Application oXL.Visible = True Set oWB = oXL.Workbooks.Add Set oWS = oWB.Worksheets.Add End Sub Assuming Excel is not already running, this will start Excel, add a workbook and add a worksheet (so there are 4 worksheets). The only worksheet the VB app has a reference to is Sheet4. So, if you manually close Excel or even just remove Sheet4 and then click the form, you'll get this error: ? err.Description Method 'Name' of object '_Worksheet' failed ? err.Number -2147221080 You can trap that error. For example, change the Form_Click code to this: Private Sub Form_Click() On Error GoTo EH MsgBox oWS.Name Exit Sub EH: If Err.Number = -2147221080 Then Set oWS = oWB.Worksheets.Add Resume End If End Sub Now, this error handling doesn't handle Excel or the workbook being closed...only the worksheet. And when it creates a new sheet, it's named Sheet5 (but you're probably naming the worksheets anyway, or at least you probably should be). For the Application and Workbook object reference, you'll get different errors if those objects don't exist. For example, I added the line MsgBox oWB.Name to Form_Click and when I closed the workbook and clicked the form, I got this error: ? err.Description Automation error The object invoked has disconnected from its clients. ? err.Number -2147417848 Still a trappable error though. -- Mike Thanks all for responses.
MikeD 1) >if the user closes Excel, your app is going to have a > "phantom" (for lack of a better term) reference The "phantom" reference is the exact problem -- Object Variable Not Set. > If you try to use that reference, you'll get a trappable error. That is what IsExcelActive is trapping. Here's the function '================================== Public Function IsExcelActive() As Boolean On Error Resume Next Dim blnReturn As Boolean '----------------- blnReturn = True oXL.Quit If Err.Number = 91 Then '<<Object Variable Not Set blnReturn = False End If IsExcelActive = blnReturn Exit Function End Function '============================ 2) The above works and traps for "object variable not set", but doesn't appear to resolve the issue as VB is still holding the Excel "phantom" object. This leaves Mr. Mueller post regarding TaskMgr 3) One idea, since I'm creating Excel from scratch through VB (using early binding by the way), I'm thinking probably the only way to "possibly" handle this issue is -- get the handle to Excel after creation and disable Excel's close button and menu close (this may be a tough one) .This way user "must" close using VB. David Show quoteHide quote "MikeD" <nob***@nowhere.edu> wrote in message news:OVeV%23et5JHA.5180@TK2MSFTNGP04.phx.gbl... > > "David" <dw85745***@earthlink.net> wrote in message > news:%234lVV%23r5JHA.480@TK2MSFTNGP06.phx.gbl... >> Code follows. >> >> During the first attempt, there is NO Excel object so "IsExcelActive" = >> False. >> >> After manually closing Excel independent of VB, >> VB appears to be still keeping a reference to the Excel Object as >> IsExcelActive returns True. >> >> Even executing oXL.Quit and setting things to Nothing does NOT appear to >> clear the previously >> manually closed Excel as Excel starts but NO New workbook shows and Excel >> hangs. >> >> Any ideas how to code in VB if user closes Excel separately? >> >> [CODE] >> Private Sub cmdGo_Click() >> >> If IsExcelActive Then '<< Check for Excel Object >> oXL.Quit >> Set oWS = Nothing >> Set oWB = Nothing >> Set oXL = Nothing >> End If >> Call MExcel.Excel_StartUp(False, True) >> Call MExcel.AddNewWorkBook >> Call MExcel.SetExcelForSpeed(True) >> >> Call DoAccounting >> >> 'Make Excel visible at end for faster processing >> Call MExcel.Excel_ShowHide(True) >> >> Call MExcel.SetExcelForSpeed(False) >> >> End Sub >> > > You should have posted your code for IsExcelActive so we could have see > how you're determining that because that's really kind of the key to all > of this. But essentially, regardless of how you get a reference to the > Excel.Application object (CreateObject, GetObject, or Set <var> = New > Excel.Application), if the user closes Excel, your app is going to have a > "phantom" (for lack of a better term) reference...IOW, you've still got a > reference but it's a reference to something that doesn't exist anymore > (the same goes if the user closes a worksheet or workbook for which you > have references). If you try to use that reference, you'll get a trappable > error. In lieu of that, declare your variables using the WithEvents > keyword so you'll have events in which you can write code. Of course, > this does require early-binding which could potentially pose a problem if > you need to support multiple versions of Excel. Develop against the > earliest version of Excel you need to support and you should be OK. > Problems usually only arise with early-binding if you develop with a later > version and a user has an earlier version. > > As far as there not being any open workbooks, this is normal if you > instantiate Excel via code. Adding a workbook using oXL.Workbooks.Add will > add the default 3 worksheets (at least it did with what I just tried using > Excel 2007). > > Here's the code I tried: > > Option Explicit > > Private oXL As Excel.Application > Private oWB As Excel.Workbook > Private oWS As Excel.Worksheet > > Private Sub Form_Click() > > MsgBox oWS.Name > > End Sub > > Private Sub Form_Load() > > Set oXL = New Excel.Application > oXL.Visible = True > Set oWB = oXL.Workbooks.Add > Set oWS = oWB.Worksheets.Add > > End Sub > > Assuming Excel is not already running, this will start Excel, add a > workbook and add a worksheet (so there are 4 worksheets). The only > worksheet the VB app has a reference to is Sheet4. So, if you manually > close Excel or even just remove Sheet4 and then click the form, you'll get > this error: > > ? err.Description > Method 'Name' of object '_Worksheet' failed > ? err.Number > -2147221080 > > You can trap that error. For example, change the Form_Click code to this: > > Private Sub Form_Click() > > On Error GoTo EH > > MsgBox oWS.Name > > Exit Sub > > EH: > > If Err.Number = -2147221080 Then > Set oWS = oWB.Worksheets.Add > Resume > End If > > End Sub > > Now, this error handling doesn't handle Excel or the workbook being > closed...only the worksheet. And when it creates a new sheet, it's named > Sheet5 (but you're probably naming the worksheets anyway, or at least you > probably should be). For the Application and Workbook object reference, > you'll get different errors if those objects don't exist. For example, I > added the line > > MsgBox oWB.Name > > to Form_Click and when I closed the workbook and clicked the form, I got > this error: > > ? err.Description > Automation error > The object invoked has disconnected from its clients. > ? err.Number > -2147417848 > > Still a trappable error though. > > -- > Mike > > Few things that don't quite make sense. First, why are you quitting Excel in
your IsExcelActive? In your first post, you're calling IsExcelActive and if True, quitting Excel then. Second, if you were having the problem I was describing, you wouldn't be getting an Object Variable Not Set error. That indicates that you're either explicitly setting it to Nothing somewhere or the variable is going out of scope and implicitly getting set to Nothing. That's a different problem. -- Show quoteHide quoteMike "David" <dw85745***@earthlink.net> wrote in message news:ecipfXu5JHA.4936@TK2MSFTNGP04.phx.gbl... > Thanks all for responses. > > MikeD > > 1) >if the user closes Excel, your app is going to have a > > "phantom" (for lack of a better term) reference > > If you try to use that reference, you'll get a trappable error. > > The "phantom" reference is the exact problem -- Object Variable Not Set. > That is what IsExcelActive is trapping. > > Here's the function > > '================================== > Public Function IsExcelActive() As Boolean > > On Error Resume Next > > Dim blnReturn As Boolean > > '----------------- > > blnReturn = True > > oXL.Quit > If Err.Number = 91 Then '<<Object Variable Not Set > blnReturn = False > End If > > IsExcelActive = blnReturn > > Exit Function > > End Function > '============================ > > 2) The above works and traps for "object variable not set", but doesn't > appear to resolve the issue as VB is still holding the Excel "phantom" > object. This leaves Mr. Mueller post regarding TaskMgr > > 3) One idea, since I'm creating Excel from scratch through VB (using > early binding by the way), I'm thinking probably the only way to > "possibly" handle this issue is -- get the handle to Excel after creation > and disable Excel's close button and menu close (this may be a tough one) > .This way user "must" close using VB. > > David > > "MikeD" <nob***@nowhere.edu> wrote in message > news:OVeV%23et5JHA.5180@TK2MSFTNGP04.phx.gbl... >> >> "David" <dw85745***@earthlink.net> wrote in message >> news:%234lVV%23r5JHA.480@TK2MSFTNGP06.phx.gbl... >>> Code follows. >>> >>> During the first attempt, there is NO Excel object so "IsExcelActive" = >>> False. >>> >>> After manually closing Excel independent of VB, >>> VB appears to be still keeping a reference to the Excel Object as >>> IsExcelActive returns True. >>> >>> Even executing oXL.Quit and setting things to Nothing does NOT appear to >>> clear the previously >>> manually closed Excel as Excel starts but NO New workbook shows and >>> Excel hangs. >>> >>> Any ideas how to code in VB if user closes Excel separately? >>> >>> [CODE] >>> Private Sub cmdGo_Click() >>> >>> If IsExcelActive Then '<< Check for Excel Object >>> oXL.Quit >>> Set oWS = Nothing >>> Set oWB = Nothing >>> Set oXL = Nothing >>> End If >>> Call MExcel.Excel_StartUp(False, True) >>> Call MExcel.AddNewWorkBook >>> Call MExcel.SetExcelForSpeed(True) >>> >>> Call DoAccounting >>> >>> 'Make Excel visible at end for faster processing >>> Call MExcel.Excel_ShowHide(True) >>> >>> Call MExcel.SetExcelForSpeed(False) >>> >>> End Sub >>> >> >> You should have posted your code for IsExcelActive so we could have see >> how you're determining that because that's really kind of the key to all >> of this. But essentially, regardless of how you get a reference to the >> Excel.Application object (CreateObject, GetObject, or Set <var> = New >> Excel.Application), if the user closes Excel, your app is going to have a >> "phantom" (for lack of a better term) reference...IOW, you've still got a >> reference but it's a reference to something that doesn't exist anymore >> (the same goes if the user closes a worksheet or workbook for which you >> have references). If you try to use that reference, you'll get a >> trappable error. In lieu of that, declare your variables using the >> WithEvents keyword so you'll have events in which you can write code. Of >> course, this does require early-binding which could potentially pose a >> problem if you need to support multiple versions of Excel. Develop >> against the earliest version of Excel you need to support and you should >> be OK. Problems usually only arise with early-binding if you develop with >> a later version and a user has an earlier version. >> >> As far as there not being any open workbooks, this is normal if you >> instantiate Excel via code. Adding a workbook using oXL.Workbooks.Add >> will add the default 3 worksheets (at least it did with what I just tried >> using Excel 2007). >> >> Here's the code I tried: >> >> Option Explicit >> >> Private oXL As Excel.Application >> Private oWB As Excel.Workbook >> Private oWS As Excel.Worksheet >> >> Private Sub Form_Click() >> >> MsgBox oWS.Name >> >> End Sub >> >> Private Sub Form_Load() >> >> Set oXL = New Excel.Application >> oXL.Visible = True >> Set oWB = oXL.Workbooks.Add >> Set oWS = oWB.Worksheets.Add >> >> End Sub >> >> Assuming Excel is not already running, this will start Excel, add a >> workbook and add a worksheet (so there are 4 worksheets). The only >> worksheet the VB app has a reference to is Sheet4. So, if you manually >> close Excel or even just remove Sheet4 and then click the form, you'll >> get this error: >> >> ? err.Description >> Method 'Name' of object '_Worksheet' failed >> ? err.Number >> -2147221080 >> >> You can trap that error. For example, change the Form_Click code to this: >> >> Private Sub Form_Click() >> >> On Error GoTo EH >> >> MsgBox oWS.Name >> >> Exit Sub >> >> EH: >> >> If Err.Number = -2147221080 Then >> Set oWS = oWB.Worksheets.Add >> Resume >> End If >> >> End Sub >> >> Now, this error handling doesn't handle Excel or the workbook being >> closed...only the worksheet. And when it creates a new sheet, it's named >> Sheet5 (but you're probably naming the worksheets anyway, or at least you >> probably should be). For the Application and Workbook object reference, >> you'll get different errors if those objects don't exist. For example, I >> added the line >> >> MsgBox oWB.Name >> >> to Form_Click and when I closed the workbook and clicked the form, I got >> this error: >> >> ? err.Description >> Automation error >> The object invoked has disconnected from its clients. >> ? err.Number >> -2147417848 >> >> Still a trappable error though. >> >> -- >> Mike >> >> > > Mike:
1) When VB is run, Excel is created using early binding and a Excel Workbook and Worksheet are generated from VB. This works great. 2) If the user closes Excel (using X or Excel file menu close) then VB loses the link to Excel. Since Excel was closed by the user, from the users perspective it is gone. However VB is still running and retains a("phantom link") to Excel. 3) Since VB is still running, if the user hits VB OK again (NOTE Excel is created in cmdOK) another instance (Book2) of Excel is created, shows, then hangs. 4) In trapping what was going on, I've found that during the second Excel creation an "Object Variable Not Set" err was generated. I assumed this is caused by the "phantom link" to Excel Book1. 5) IsExcelActive was designed to trap this error (91) during the second press of cmdOK with the objective of getting rid of the previous Excel Book1 by running oXL.Quit prior to creating another instance of Excel. It did NOT WORK as hoped. 6) I also tried late binding, and the results are better, but still not what I would consider acceptable. 7) So looking into disabling Excel close button (X), and also see if anyway to gray "Excel file menu close" from VB. David Show quoteHide quote "MikeD" <nob***@nowhere.edu> wrote in message news:ekBuAi75JHA.4864@TK2MSFTNGP03.phx.gbl... > Few things that don't quite make sense. First, why are you quitting Excel > in your IsExcelActive? In your first post, you're calling IsExcelActive > and if True, quitting Excel then. Second, if you were having the problem > I was describing, you wouldn't be getting an Object Variable Not Set > error. That indicates that you're either explicitly setting it to Nothing > somewhere or the variable is going out of scope and implicitly getting set > to Nothing. That's a different problem. > > -- > Mike > > > > "David" <dw85745***@earthlink.net> wrote in message > news:ecipfXu5JHA.4936@TK2MSFTNGP04.phx.gbl... >> Thanks all for responses. >> >> MikeD >> >> 1) >if the user closes Excel, your app is going to have a >> > "phantom" (for lack of a better term) reference >> > If you try to use that reference, you'll get a trappable error. >> >> The "phantom" reference is the exact problem -- Object Variable Not Set. >> That is what IsExcelActive is trapping. >> >> Here's the function >> >> '================================== >> Public Function IsExcelActive() As Boolean >> >> On Error Resume Next >> >> Dim blnReturn As Boolean >> >> '----------------- >> >> blnReturn = True >> >> oXL.Quit >> If Err.Number = 91 Then '<<Object Variable Not Set >> blnReturn = False >> End If >> >> IsExcelActive = blnReturn >> >> Exit Function >> >> End Function >> '============================ >> >> 2) The above works and traps for "object variable not set", but doesn't >> appear to resolve the issue as VB is still holding the Excel "phantom" >> object. This leaves Mr. Mueller post regarding TaskMgr >> >> 3) One idea, since I'm creating Excel from scratch through VB (using >> early binding by the way), I'm thinking probably the only way to >> "possibly" handle this issue is -- get the handle to Excel after creation >> and disable Excel's close button and menu close (this may be a tough one) >> .This way user "must" close using VB. >> >> David >> >> "MikeD" <nob***@nowhere.edu> wrote in message >> news:OVeV%23et5JHA.5180@TK2MSFTNGP04.phx.gbl... >>> >>> "David" <dw85745***@earthlink.net> wrote in message >>> news:%234lVV%23r5JHA.480@TK2MSFTNGP06.phx.gbl... >>>> Code follows. >>>> >>>> During the first attempt, there is NO Excel object so "IsExcelActive" = >>>> False. >>>> >>>> After manually closing Excel independent of VB, >>>> VB appears to be still keeping a reference to the Excel Object as >>>> IsExcelActive returns True. >>>> >>>> Even executing oXL.Quit and setting things to Nothing does NOT appear >>>> to clear the previously >>>> manually closed Excel as Excel starts but NO New workbook shows and >>>> Excel hangs. >>>> >>>> Any ideas how to code in VB if user closes Excel separately? >>>> >>>> [CODE] >>>> Private Sub cmdGo_Click() >>>> >>>> If IsExcelActive Then '<< Check for Excel Object >>>> oXL.Quit >>>> Set oWS = Nothing >>>> Set oWB = Nothing >>>> Set oXL = Nothing >>>> End If >>>> Call MExcel.Excel_StartUp(False, True) >>>> Call MExcel.AddNewWorkBook >>>> Call MExcel.SetExcelForSpeed(True) >>>> >>>> Call DoAccounting >>>> >>>> 'Make Excel visible at end for faster processing >>>> Call MExcel.Excel_ShowHide(True) >>>> >>>> Call MExcel.SetExcelForSpeed(False) >>>> >>>> End Sub >>>> >>> >>> You should have posted your code for IsExcelActive so we could have see >>> how you're determining that because that's really kind of the key to all >>> of this. But essentially, regardless of how you get a reference to the >>> Excel.Application object (CreateObject, GetObject, or Set <var> = New >>> Excel.Application), if the user closes Excel, your app is going to have >>> a "phantom" (for lack of a better term) reference...IOW, you've still >>> got a reference but it's a reference to something that doesn't exist >>> anymore (the same goes if the user closes a worksheet or workbook for >>> which you have references). If you try to use that reference, you'll get >>> a trappable error. In lieu of that, declare your variables using the >>> WithEvents keyword so you'll have events in which you can write code. >>> Of course, this does require early-binding which could potentially pose >>> a problem if you need to support multiple versions of Excel. Develop >>> against the earliest version of Excel you need to support and you should >>> be OK. Problems usually only arise with early-binding if you develop >>> with a later version and a user has an earlier version. >>> >>> As far as there not being any open workbooks, this is normal if you >>> instantiate Excel via code. Adding a workbook using oXL.Workbooks.Add >>> will add the default 3 worksheets (at least it did with what I just >>> tried using Excel 2007). >>> >>> Here's the code I tried: >>> >>> Option Explicit >>> >>> Private oXL As Excel.Application >>> Private oWB As Excel.Workbook >>> Private oWS As Excel.Worksheet >>> >>> Private Sub Form_Click() >>> >>> MsgBox oWS.Name >>> >>> End Sub >>> >>> Private Sub Form_Load() >>> >>> Set oXL = New Excel.Application >>> oXL.Visible = True >>> Set oWB = oXL.Workbooks.Add >>> Set oWS = oWB.Worksheets.Add >>> >>> End Sub >>> >>> Assuming Excel is not already running, this will start Excel, add a >>> workbook and add a worksheet (so there are 4 worksheets). The only >>> worksheet the VB app has a reference to is Sheet4. So, if you manually >>> close Excel or even just remove Sheet4 and then click the form, you'll >>> get this error: >>> >>> ? err.Description >>> Method 'Name' of object '_Worksheet' failed >>> ? err.Number >>> -2147221080 >>> >>> You can trap that error. For example, change the Form_Click code to >>> this: >>> >>> Private Sub Form_Click() >>> >>> On Error GoTo EH >>> >>> MsgBox oWS.Name >>> >>> Exit Sub >>> >>> EH: >>> >>> If Err.Number = -2147221080 Then >>> Set oWS = oWB.Worksheets.Add >>> Resume >>> End If >>> >>> End Sub >>> >>> Now, this error handling doesn't handle Excel or the workbook being >>> closed...only the worksheet. And when it creates a new sheet, it's >>> named Sheet5 (but you're probably naming the worksheets anyway, or at >>> least you probably should be). For the Application and Workbook object >>> reference, you'll get different errors if those objects don't exist. >>> For example, I added the line >>> >>> MsgBox oWB.Name >>> >>> to Form_Click and when I closed the workbook and clicked the form, I got >>> this error: >>> >>> ? err.Description >>> Automation error >>> The object invoked has disconnected from its clients. >>> ? err.Number >>> -2147417848 >>> >>> Still a trappable error though. >>> >>> -- >>> Mike >>> >>> >> >> > "David" <dw85745***@earthlink.net> wrote in message news:uHJ1IyC6JHA.4892@TK2MSFTNGP06.phx.gbl... I personally wouldn't recommend doing that. For one, it's really just not "right". But more importantly, what if your app closes > > 7) So looking into disabling Excel close button (X), and also see if anyway to gray "Excel file menu close" from VB. unexpectedly or you lose the reference? Then, the only way to close Excel would be Task Manager and this would not provide the user any way to save anything that might need saved. Keep in mind they might have other workbooks open besides the one your VB is adding. IMO, what you need to do is add additional code and error handling for the possibilities I originally described. -- Mike MIkeD:
Thanks for your help. Somewhere along the line we're not communicating. In my case if the user closes Excel (not a worksheet) I get error 91 (Object Variable Not Set). While trappable it does not appear that it is recoverable other than by using TaskMgr. This is per Mr Mueller's comments and my testing. If you know otherwise please post code. Thanks David Show quoteHide quote "MikeD" <nob***@nowhere.edu> wrote in message news:eaD03PF6JHA.1380@TK2MSFTNGP05.phx.gbl... > > "David" <dw85745***@earthlink.net> wrote in message > news:uHJ1IyC6JHA.4892@TK2MSFTNGP06.phx.gbl... >> >> 7) So looking into disabling Excel close button (X), and also see if >> anyway to gray "Excel file menu close" from VB. > > > I personally wouldn't recommend doing that. For one, it's really just not > "right". But more importantly, what if your app closes unexpectedly or you > lose the reference? Then, the only way to close Excel would be Task > Manager and this would not provide the user any way to save anything that > might need saved. Keep in mind they might have other workbooks open > besides the one your VB is adding. > > IMO, what you need to do is add additional code and error handling for the > possibilities I originally described. > > -- > Mike > >
Show quote
Hide quote
"David" <dw85745***@earthlink.net> wrote in message I may be misunderstanding what you are asking also, but there should be news:#QOLwIG6JHA.3968@TK2MSFTNGP03.phx.gbl... > MIkeD: > > Thanks for your help. Somewhere along the line we're not communicating. > > In my case if the user closes Excel (not a worksheet) I get error 91 > (Object Variable Not Set). While trappable it does not appear that > it is recoverable other than by using TaskMgr. This is per Mr Mueller's > comments and my testing. > > If you know otherwise please post code. > > Thanks > David > > > "MikeD" <nob***@nowhere.edu> wrote in message > news:eaD03PF6JHA.1380@TK2MSFTNGP05.phx.gbl... >> >> "David" <dw85745***@earthlink.net> wrote in message >> news:uHJ1IyC6JHA.4892@TK2MSFTNGP06.phx.gbl... >>> >>> 7) So looking into disabling Excel close button (X), and also see if >>> anyway to gray "Excel file menu close" from VB. >> >> >> I personally wouldn't recommend doing that. For one, it's really just not >> "right". But more importantly, what if your app closes unexpectedly or >> you lose the reference? Then, the only way to close Excel would be Task >> Manager and this would not provide the user any way to save anything that >> might need saved. Keep in mind they might have other workbooks open >> besides the one your VB is adding. >> >> IMO, what you need to do is add additional code and error handling for >> the possibilities I originally described. >> >> -- >> Mike >> >> > > David, several ways around this problem. You could check to see if Excel is running and if the object you created is still in existence. If your object still exists and Excel is not running then release your object before creating a new one. There are several ways you should be able to do this, I am including the code I use for Word below, as well as code for checking if Excel is running. Hope this helps. Norm Check if Word, Excel or Access is running: Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As Any, ByVal lpWindowName As Any) As Long Function IsAccessRunning() As Boolean On Error Resume Next IsAccessRunning = (FindWindow("OMain", vbNullString) <> 0) End Function Function IsExcelRunning() As Boolean On Error Resume Next IsExcelRunning = (FindWindow("XLMain", vbNullString) <> 0) End Function Function IsWordRunning() As Boolean On Error Resume Next IsWordRunning = (FindWindow("OpusApp", vbNullString) <> 0) End Function Another way to check if program is running by checking for executable name: Option Explicit Private Const TH32CS_SNAPHEAPLIST = &H1 Private Const TH32CS_SNAPPROCESS = &H2 Private Const TH32CS_SNAPTHREAD = &H4 Private Const TH32CS_SNAPMODULE = &H8 Private Const TH32CS_SNAPALL = (TH32CS_SNAPHEAPLIST + _ TH32CS_SNAPPROCESS + TH32CS_SNAPTHREAD + TH32CS_SNAPMODULE) Private Const MAX_PATH = 260 Private Type PROCESSENTRY32 dwSize As Long cntUsage As Long th32ProcessID As Long th32DefaultHeapID As Long th32ModuleID As Long cntThreads As Long th32ParentProcessID As Long pcPriClassBase As Long dwFlags As Long szExeFile As String * MAX_PATH End Type Private Declare Function CreateToolhelp32Snapshot Lib "kernel32" _ (ByVal dwFlags As Long, ByVal th32ProcessID As Long) As Long Private Declare Function Process32First Lib "kernel32" (ByVal _ hSnapshot As Long, lppe As PROCESSENTRY32) As Long Private Declare Function Process32Next Lib "kernel32" (ByVal hSnapshot _ As Long, lppe As PROCESSENTRY32) As Long Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As _ Long) As Long Public Function IsProgramRunning(sExeName As String) As Boolean Dim hSnapshot As Long, lRet As Long, P As PROCESSENTRY32 Dim strExeName As String IsProgramRunning = False P.dwSize = Len(P) hSnapshot = CreateToolhelp32Snapshot(TH32CS_SNAPALL, ByVal 0) If hSnapshot Then lRet = Process32First(hSnapshot, P) Do While lRet strExeName = Left$(P.szExeFile, InStr(P.szExeFile, Chr$(0)) - 1) If UCase(strExeName) = UCase(sExeName) Then IsProgramRunning = True Exit Do End If lRet = Process32Next(hSnapshot, P) Loop lRet = CloseHandle(hSnapshot) End If End Function How I check if my Word object is still existing: Dim x As Long On Error Resume Next If Not (wrd Is Nothing) Then If wrd.Documents.Count > 0 Then For x = 1 To wrd.Documents.Count wrd.Documents(x).Activate wrd.ActiveDocument.Close Sleep (20) Next x End If wrd.Quit Set wrd = Nothing End If 
Other interesting topics
Use an Addin to automatically add date/time stamp to each edited line of VB6 code?
Sub .... or Private Sub.... When and where do I do Set m_FormVar = Nothing? Serial Number VB6 - Just In Time Error Message Help MS Access Query in VB6 fso.Drives replacement The GPF in VB6 Environment is caused by: Call HtmlHelp(Me.hwnd, App.HelpFile, HH_CLOSE_ALL, 0&) Security Settings Open a FoxPro DAT file? |
|||||||||||||||||||||||
