DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Re: Rob's Solution...

  1. #1
    Kerwin Guest

    Re: Rob's Solution...


    Thanks for this tip Rob!

    Not sure I quite understand what defines an ambiguous statement? Putting
    your client references into a procedure on the server side such as
    "with myObj end with" corrects this?

    I have several hundred lines of code to go through, that may have this
    error... plus it will also create some extra overhead in the size and
    organization of the code...

    I am wondering if there is a Windows API call to the "Task Manager" that
    I can make to simply cancel the active "Process" of excel. I will have
    to investigate... I will post my results if I descover any.

    Again, thanks for help...


    "Rob Bruce" <rob@rb-ad.dircon.co.uk> wrote:
    >Hi Kerwin,
    >
    >I'm going to have to bung something about this on my web site since I'm
    >answering this question about once a day now on various groups!
    >
    >This usually occurs because of an ambiguous object reference in your client
    >code. Say you create an Excel object using:
    >
    >Dim xlApp as Excel.Application
    >dim xlWbk as Excel.Workbook
    >Set xlApp = new Excel.Application
    >
    >and then you go on to do stuff like:
    >
    >set xlWbk = xlApp.Workbooks.Add
    >
    >Everything's fine so far. But then you import some code from a VBA project
    >and it includes an ambiguous line such as
    >
    >xlWbk.Worksheets(1).Range(cells(1), cells(4)).value = "Some Repeated Text"
    >
    >Now this will work without an error, but the Cells method that appears twice
    >here is unqualified so what happens is that COM goes off and searches
    >through the referenced libraries, in priority order, until it finds a method
    >or property that fits the bill. If Excel is higher in priority (ie. appears
    >high in the Project | References dialog) than any other library with a Cells
    >method or property, then the Excel definition will be used. Unfortunately,
    >during the search, COM opens a hidden and unkillable handle to the Excel
    >application object. It is this handle remaining live that prevents Excel
    >from closing.
    >
    >The solution is to trawl through your automation code looking for ambiguous
    >references and to (great Microsoft word coming up) _disambiguate_ them.

    In
    >the example above
    >
    >with xlWbk.Worksheets(1)
    > .Range(.cells(1), .cells(4)).value = "Some Repeated Text"
    >end with
    >
    >would do the job nicely.
    >
    >You should be aware that I have also seen this happen with ambiguous
    >constants/enums, but this is more usual (and I don't know why) with Word.
    >
    >Therefore, as a failsafe you should use constants such as Excel.xlNormal
    >rather than the ambiguous xlNormal.
    >
    >Hope this nails it for you.
    >
    >--
    >Rob
    >
    >Excel VBA Programming Stuff:
    >www.rb-ad.dircon.co.uk/rob/excelvba/
    >
    >"Kerwin" <kerwinkassulker@hotmail.com> wrote in message
    >news:3ae855be$1@news.devx.com...
    >>
    >> Hi...
    >> Have a strange problem I have been wrestling with... I cannot seem
    >> to get excel out of memory after closing applications. I am using a
    >> reference to excel from an external application (i.e. such as VB 6).
    >> I use xlApp as an obj ref., and then issue the following statments
    >>
    >> xlApp.Quit
    >> set xlApp = nothing
    >>
    >> According to all documentation I have read, this should be all I
    >> need to do to quit the ecxel application, and then release the obj.
    >> reference. YET, when you check memory, the hidden excel application
    >> still resides.
    >>
    >> I have tried different methods thru. immediate code, but cannot close
    >> it down programmatically. I end up using the windows "task manager" to
    >> close it out.
    >>
    >> Any ideas?
    >>
    >> I forward my thanks!
    >>
    >> Kerwin
    >> Mountain View, CA.

    >
    >



  2. #2
    Rob Bruce Guest

    Re: Rob's Solution...

    Hello Again,

    > Not sure I quite understand what defines an ambiguous statement? Putting
    > your client references into a procedure on the server side such as
    > "with myObj end with" corrects this?


    Well, no. Maybe the fact that I used With obscured the fact that both of the
    calls to Cells in my version

    with xlWbk.Worksheets(1)
    .Range(.cells(1), .cells(4)).value = "Some Repeated Text"
    end with

    have DOTS in front of them which explicitly connects them to the parent
    worksheet object. Maybe it would have been clearer to use the longer

    xlWbk.Worksheets(1).Range( _
    xlWbk.Worksheets(1).cells(1), _
    xlWbk.Worksheets(1).cells(4)).value = _
    "Some Repeated Text"

    Is that more explicit?

    > I have several hundred lines of code to go through, that may have this
    > error... plus it will also create some extra overhead in the size and
    > organization of the code...
    >
    > I am wondering if there is a Windows API call to the "Task Manager" that
    > I can make to simply cancel the active "Process" of excel. I will have
    > to investigate... I will post my results if I descover any.


    Have you considered the potential memory leaks that might be involved in
    this, not to mention the number of temp files an unclean end to an Excel
    process might leave on the user's disk? I would guess that you would have
    big problems porting this between versions of Windows, too. Best to sift
    through those hundreds of lines of code, I think.

    --
    Rob

    Excel VBA Programming Stuff:
    www.rb-ad.dircon.co.uk/rob/excelvba/




  3. #3
    Peter Higgins Guest

    Re: Rob's Solution...


    Hello Rob;
    Thanks for the solution and great explanation of why com leaves excel in
    memory. You solved one puzzle I have attempted to crack several times. The
    ambiguous reference is pretty subtle.

    Liked your web site. Export clean HTML looks very useful. Keep up the good
    work.

    "Rob Bruce" <rob@rb-ad.dircon.co.uk> wrote:
    >Hello Again,
    >
    >> Not sure I quite understand what defines an ambiguous statement? Putting
    >> your client references into a procedure on the server side such as
    >> "with myObj end with" corrects this?

    >
    >Well, no. Maybe the fact that I used With obscured the fact that both of

    the
    >calls to Cells in my version
    >
    >with xlWbk.Worksheets(1)
    > .Range(.cells(1), .cells(4)).value = "Some Repeated Text"
    >end with
    >
    >have DOTS in front of them which explicitly connects them to the parent
    >worksheet object. Maybe it would have been clearer to use the longer
    >
    >xlWbk.Worksheets(1).Range( _
    > xlWbk.Worksheets(1).cells(1), _
    > xlWbk.Worksheets(1).cells(4)).value = _
    > "Some Repeated Text"
    >
    >Is that more explicit?
    >
    >> I have several hundred lines of code to go through, that may have this
    >> error... plus it will also create some extra overhead in the size and
    >> organization of the code...
    >>
    >> I am wondering if there is a Windows API call to the "Task Manager" that
    >> I can make to simply cancel the active "Process" of excel. I will have
    >> to investigate... I will post my results if I descover any.

    >
    >Have you considered the potential memory leaks that might be involved in
    >this, not to mention the number of temp files an unclean end to an Excel
    >process might leave on the user's disk? I would guess that you would have
    >big problems porting this between versions of Windows, too. Best to sift
    >through those hundreds of lines of code, I think.
    >
    >--
    >Rob
    >
    >Excel VBA Programming Stuff:
    >www.rb-ad.dircon.co.uk/rob/excelvba/
    >
    >
    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center