Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.vb.general.discussion,microsoft.public.excel.programming
|
|||
|
|||
Excel automation: Why my code is hijacked?
Hello,
I have this routine to disconnect from Excel: Public Sub XLDisconnectFromExcel() If Not moExcelWS Is Nothing Then Set moExcelWS = Nothing If Not moExcelApp Is Nothing Then Set moExcelApp = Nothing End Sub When the second line of that code is executed and moExcelApp is "Microsoft Excel" then that line of code is executed properly (Excel.exe is process terminated in Task Manager) but the code execution never returns after that line. It just dissapears and subsequently not any other code is executed. What can be wrong? To connect to Excel I use this code: Set moExcelApp = GetObject(, "Excel.Application") If moExcelApp Is Nothing Then Set moExcelApp = CreateObject("Excel.Application") End If Jack |
#2
Posted to microsoft.public.vb.general.discussion,microsoft.public.excel.programming
|
|||
|
|||
Excel automation: Why my code is hijacked?
More details needed. Where are you using this code? What do you mean
"but the code execution never returns after that line. It just dissapears and subsequently not any other code is executed." Returns where? Do you mean you are using calling this Sub from another routine? --JP On Mar 29, 2:34*pm, "Jack" <replyto@it wrote: Hello, I have this routine to disconnect from Excel: Public Sub XLDisconnectFromExcel() * * If Not moExcelWS Is Nothing Then Set moExcelWS = Nothing * * If Not moExcelApp Is Nothing Then Set moExcelApp = Nothing End Sub When the second line of that code is executed and moExcelApp is "Microsoft Excel" then that line of code is executed properly (Excel.exe is process terminated in Task Manager) but the code execution never returns after that line. It just dissapears and subsequently not any other code is executed. What can be wrong? To connect to Excel I use this code: * * Set moExcelApp = GetObject(, "Excel.Application") * * If moExcelApp Is Nothing Then * * * * Set moExcelApp = CreateObject("Excel.Application") * * End If Jack |
#3
Posted to microsoft.public.vb.general.discussion,microsoft.public.excel.programming
|
|||
|
|||
Excel automation: Why my code is hijacked?
First, I don't see the purpose of checking in the first routine.
Public Sub XLDisconnectFromExcel() Set moExcelWS = Nothing Set moExcelApp = Nothing End Sub If either of those objects are already nothing, then it won't hurt. But I don't have a guess why your program hangs up. Maybe you didn't release all the objects??? Maybe you didn't close excel??? Maybe excel is waiting for input from the user??? And I would expect an error if the getobject failed in the second routine. I use this: dim moExcelApp as Object On Error Resume Next Set moExcelApp = GetObject(, "Excel.Application") If Err.Number = 429 Then 'Excel is not running Set moExcelApp = CreateObject("Excel.Application") err.clear end if On Error GoTo 0 Jack wrote: Hello, I have this routine to disconnect from Excel: Public Sub XLDisconnectFromExcel() If Not moExcelWS Is Nothing Then Set moExcelWS = Nothing If Not moExcelApp Is Nothing Then Set moExcelApp = Nothing End Sub When the second line of that code is executed and moExcelApp is "Microsoft Excel" then that line of code is executed properly (Excel.exe is process terminated in Task Manager) but the code execution never returns after that line. It just dissapears and subsequently not any other code is executed. What can be wrong? To connect to Excel I use this code: Set moExcelApp = GetObject(, "Excel.Application") If moExcelApp Is Nothing Then Set moExcelApp = CreateObject("Excel.Application") End If Jack -- Dave Peterson |
#4
Posted to microsoft.public.vb.general.discussion,microsoft.public.excel.programming
|
|||
|
|||
Excel automation: Why my code is hijacked?
Follow up.
The same happens when stepping through this code: On Error Resume Next 'TEMPORARILY ignore errors Set moExcelApp = GetObject(, "Excel.Application") If moExcelApp Is Nothing Then Set moExcelApp = CreateObject("Excel.Application") === after executing that line the code will not stop at the next line (End If) End If Interestingly, I am using the same code in another of my app and it does work fine on the same computer!!!! The Reference is set exactly to the same file and the general settings are the same: Option Explicit Public WithEvents moExcelApp As Excel.Application Dim moExcelWBk As Excel.Workbook Dim moExcelWS As Excel.Worksheet Jack "Jack" <replyto@it wrote in message ... Hello, I have this routine to disconnect from Excel: Public Sub XLDisconnectFromExcel() If Not moExcelWS Is Nothing Then Set moExcelWS = Nothing If Not moExcelApp Is Nothing Then Set moExcelApp = Nothing End Sub When the second line of that code is executed and moExcelApp is "Microsoft Excel" then that line of code is executed properly (Excel.exe is process terminated in Task Manager) but the code execution never returns after that line. It just dissapears and subsequently not any other code is executed. What can be wrong? To connect to Excel I use this code: Set moExcelApp = GetObject(, "Excel.Application") If moExcelApp Is Nothing Then Set moExcelApp = CreateObject("Excel.Application") End If Jack |
#5
Posted to microsoft.public.vb.general.discussion,microsoft.public.excel.programming
|
|||
|
|||
Excel automation: Why my code is hijacked?
What I mean is, that when stepping through the code in debugging mode, after
executing that line of code execution does not return to End Sub where it should. Jack "JP" wrote in message ... More details needed. Where are you using this code? What do you mean "but the code execution never returns after that line. It just dissapears and subsequently not any other code is executed." Returns where? Do you mean you are using calling this Sub from another routine? --JP On Mar 29, 2:34 pm, "Jack" <replyto@it wrote: Hello, I have this routine to disconnect from Excel: Public Sub XLDisconnectFromExcel() If Not moExcelWS Is Nothing Then Set moExcelWS = Nothing If Not moExcelApp Is Nothing Then Set moExcelApp = Nothing End Sub When the second line of that code is executed and moExcelApp is "Microsoft Excel" then that line of code is executed properly (Excel.exe is process terminated in Task Manager) but the code execution never returns after that line. It just dissapears and subsequently not any other code is executed. What can be wrong? To connect to Excel I use this code: Set moExcelApp = GetObject(, "Excel.Application") If moExcelApp Is Nothing Then Set moExcelApp = CreateObject("Excel.Application") End If Jack |
#6
Posted to microsoft.public.vb.general.discussion,microsoft.public.excel.programming
|
|||
|
|||
Excel automation: Why my code is hijacked?
Problem solved.
Just removed the reference, saved the project, restarted the project and added again the same reference. Any thoughts on that? Jack "Jack" <replyto@it wrote in message ... Hello, I have this routine to disconnect from Excel: Public Sub XLDisconnectFromExcel() If Not moExcelWS Is Nothing Then Set moExcelWS = Nothing If Not moExcelApp Is Nothing Then Set moExcelApp = Nothing End Sub When the second line of that code is executed and moExcelApp is "Microsoft Excel" then that line of code is executed properly (Excel.exe is process terminated in Task Manager) but the code execution never returns after that line. It just dissapears and subsequently not any other code is executed. What can be wrong? To connect to Excel I use this code: Set moExcelApp = GetObject(, "Excel.Application") If moExcelApp Is Nothing Then Set moExcelApp = CreateObject("Excel.Application") End If Jack |
#7
Posted to microsoft.public.vb.general.discussion,microsoft.public.excel.programming
|
|||
|
|||
Excel automation: Why my code is hijacked?
I just have a question on why you're using a reference at all.
Since you're using createobject/getobject, it looks as though you could stick with that late binding approach. Jack wrote: Problem solved. Just removed the reference, saved the project, restarted the project and added again the same reference. Any thoughts on that? Jack "Jack" <replyto@it wrote in message ... Hello, I have this routine to disconnect from Excel: Public Sub XLDisconnectFromExcel() If Not moExcelWS Is Nothing Then Set moExcelWS = Nothing If Not moExcelApp Is Nothing Then Set moExcelApp = Nothing End Sub When the second line of that code is executed and moExcelApp is "Microsoft Excel" then that line of code is executed properly (Excel.exe is process terminated in Task Manager) but the code execution never returns after that line. It just dissapears and subsequently not any other code is executed. What can be wrong? To connect to Excel I use this code: Set moExcelApp = GetObject(, "Excel.Application") If moExcelApp Is Nothing Then Set moExcelApp = CreateObject("Excel.Application") End If Jack -- Dave Peterson |
#8
Posted to microsoft.public.vb.general.discussion,microsoft.public.excel.programming
|
|||
|
|||
Excel automation: Why my code is hijacked?
"Jack" <replyto@it wrote in message ... Follow up. The same happens when stepping through this code: On Error Resume Next 'TEMPORARILY ignore errors Set moExcelApp = GetObject(, "Excel.Application") If moExcelApp Is Nothing Then Set moExcelApp = CreateObject("Excel.Application") === after executing that line the code will not stop at the next line (End If) End If Interestingly, I am using the same code in another of my app and it does work fine on the same computer!!!! The Reference is set exactly to the same file and the general settings are the same: Option Explicit Public WithEvents moExcelApp As Excel.Application Dim moExcelWBk As Excel.Workbook Dim moExcelWS As Excel.Worksheet Jack Take a good look at Dave's advice. In a nutshell: Excel is a PITA if you have more than one running. (GetObject) Fully qualify ALL objects with the object reference. Make sure you have cleaned up everything. Explicitly tell it whether you want to save or not. Explicitly call .Exit. While debugging make sure DisplayAlerts is on and when you are no longer debugging make sure they are off. Here's is a slew of tips: Note the suggestion to turn .DisplayAlerts back on when you Exit. http://www.tek-tips.com/viewthread.cfm?qid=90756&page=1 hth -ralph |
#9
Posted to microsoft.public.vb.general.discussion,microsoft.public.excel.programming
|
|||
|
|||
Excel automation: Why my code is hijacked?
"Dave Peterson" wrote in message ... I just have a question on why you're using a reference at all. Since you're using createobject/getobject, it looks as though you could stick with that late binding approach. The OP isn't using Late-binding. He is using Early-binding. The Type (or Interface) of the object reference variable is what determines Late or Early not the call to fetch the coClass. eg: Dim oApp As Object Dim rApp As Excel.Application Set oApp = CreateObject("Excel.Application") ' Late Set rApp = CreateObject("Excel.Application") ' Early -ralph |
#10
Posted to microsoft.public.vb.general.discussion,microsoft.public.excel.programming
|
|||
|
|||
Excel automation: Why my code is hijacked?
"Jack" <replyto@it wrote in message ... Problem solved. Just removed the reference, saved the project, restarted the project and added again the same reference. Any thoughts on that? Jack Whatever was hidden or being held was finally destroyed. Unless you improve your shutdown, you will get bit again. <g -ralph |
#11
Posted to microsoft.public.vb.general.discussion,microsoft.public.excel.programming
|
|||
|
|||
Excel automation: Why my code is hijacked?
I should have been more explicit.
My point was since he was using this: Set moExcelApp = CreateObject("Excel.Application") instead of Set moExcelApp = new Excel.application why not just stick with late binding and drop the reference completely and declare those variables as Objects? Ralph wrote: "Dave Peterson" wrote in message ... I just have a question on why you're using a reference at all. Since you're using createobject/getobject, it looks as though you could stick with that late binding approach. The OP isn't using Late-binding. He is using Early-binding. The Type (or Interface) of the object reference variable is what determines Late or Early not the call to fetch the coClass. eg: Dim oApp As Object Dim rApp As Excel.Application Set oApp = CreateObject("Excel.Application") ' Late Set rApp = CreateObject("Excel.Application") ' Early -ralph -- Dave Peterson |
#12
Posted to microsoft.public.vb.general.discussion,microsoft.public.excel.programming
|
|||
|
|||
Excel automation: Why my code is hijacked?
Dave Peterson wrote:
I should have been more explicit. My point was since he was using this: Set moExcelApp = CreateObject("Excel.Application") instead of Set moExcelApp = new Excel.application why not just stick with late binding and drop the reference completely and declare those variables as Objects? He would lose intellisense, and have to look all the parameters up to be sure they were correct, or wait for runtime errors. Also late binding all the calls would slow the performance down quite a bit. The advantage of using create object with Excel is that it is not version specific. Back to the original post, shouldn't there be a moExcelApp.Quit before the Set moExcelApp = Nothing or is that not needed? |
#13
Posted to microsoft.public.vb.general.discussion,microsoft.public.excel.programming
|
|||
|
|||
Excel automation: Why my code is hijacked?
I develop my code with the reference. But when I release it to others, I change
it late binding. I couldn't live without the intellisense, either. And who knows about quitting the application? I figured that there wasn't really enough code in that post to venture a guess (no saving, no closing, no releasing other objects???). Steve Gerrard wrote: Dave Peterson wrote: I should have been more explicit. My point was since he was using this: Set moExcelApp = CreateObject("Excel.Application") instead of Set moExcelApp = new Excel.application why not just stick with late binding and drop the reference completely and declare those variables as Objects? He would lose intellisense, and have to look all the parameters up to be sure they were correct, or wait for runtime errors. Also late binding all the calls would slow the performance down quite a bit. The advantage of using create object with Excel is that it is not version specific. Back to the original post, shouldn't there be a moExcelApp.Quit before the Set moExcelApp = Nothing or is that not needed? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automation code | Excel Discussion (Misc queries) | |||
keyboard shortcut hijacked? | Excel Programming | |||
Workshoot Menu Bar Hijacked!! | Excel Programming | |||
Code Librarian automation? | Excel Programming | |||
Automation Code Problem from Access to Excel | Excel Programming |