Results 1 to 9 of 9
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Set objApp = Nothing

    I am (obviously) not expert at all in the matter of objects and especially calling other applications, and would appreciate comments and suggestions.

    The last line in this code segment causes a message "This ... has performed an illegal operation" on each and every EXCEL97 workbook opened from within Word97SR2/VBA.


    I decided to try setting the objApp to Nothing after a run in which I ran out of memory resources. "Self", I said, "You've been grabbing memory for each instance of Excel and not giving it back".


    <pre> Dim oItem As Object
    ' We need an object which looks like Microsoft Word
    Dim objApp As New Excel.Application
    ' Let's make it visible to the user, lights and wheels flashing and spinning.
    objApp.Visible = True
    ' Now we need to have a document on the desktop so that we can run
    ' our application macro.
    objApp.Workbooks.Add
    ' Then we can run our application macro.
    objApp.Workbooks.Open (strLocPath & strFile)
    Dim i As Integer
    For Each oItem In objApp.ActiveWorkbook.VBProject.VBComponents
    strStatusBar = strLocPath & strFile & strcSeparator & oItem.Name
    Application.StatusBar = strStatusBar
    strModuleCode = strPackModule(oItem, lngAr)
    strWorkbookCode = strWorkbookCode & strModuleCode
    lngAr(5) = lngAr(5) + 1
    Next oItem
    ' Close the workbook with no changes
    objApp.ActiveWorkbook.Close savechanges:=False
    ' And when we are done, we unload the Word application.
    objApp.Quit
    Set objApp = Nothing ' <<<<<<<<<< Ilegal operation
    </pre>


  2. #2
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set objApp = Nothing

    It could very well be that after you quit the application the application object is gone too. So your statement causes an error because it can no longer reference the object.

    Try setting to nothing BEFORE quiting the app object.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set objApp = Nothing

    I didn't think this would work, and it didn't, sort-of.


    Reversing the sequence evades the Application Error, but it doesn't quit Excel. Pretty soon I'm going to be up over my head in Excel instances. An already I'm vertically-challenged.

    I'm gussing something along the lines of "Quittting" Excel does something final to the object, so that setting the object to Nothing is superfluous, hence the error.


    I'm going to essay with a Set statement separate from the Dim.

  4. #4
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set objApp = Nothing

    Now that I stop to thing about it, you are EXACTLY right. The quit, kills the objec, and so would setting it to nothing. They are redundant.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  5. #5
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Set objApp = Nothing

    How about prefacing the last line with something like:

    If Not objApp Is Nothing Then ...

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set objApp = Nothing

    Thanks. I'll try.

    So far (1) separating the DIM out and (2) scattering DoEvents like Dandelion seeds in Vancouver BC seem to have done the trick.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Set objApp = Nothing

    Be very, very careful of DoEvents. It can get you into a lot of trouble because it will allow other threads/programs/processes to take over an do stuff that might interfere with the routine that issued the DoEvents, especially in a multiuser environment. Been there, been burned.
    Charlotte

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set objApp = Nothing

    Charlotte, thanks for the advice.

    My intention is always to get the thing working by crook, hook not being available, and then withdraw "fixes" like DoEvents to determine the offending operation, hence to isolate the problem prior to resolving it.

    However, I have a history of being so relieved at getting something to work, that I forget and leave the coding in place.

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set objApp = Nothing

    This appears to work.

    The snippet of code is the inner loop of a Word97SR2/VBA function which is trying to obtain all code modules from PowerPoint presentations.


    The TaskExists solution proposed by Gary Frieder works better than the objApp.Quit.


    I essayed with various delay loops, DoEvents etc and have since commented out and then removed them.


    <pre> Dim oItem As Object
    ' We need an object which looks like Microsoft Word
    Dim objApp As New PowerPoint.Application
    On Error Resume Next ' automaton error with ordinals.xls?
    Set objApp = PowerPoint.Application
    ' Let's make it visible to the user, lights and wheels flashing and spinning.
    objApp.Visible = True
    ' Now we need to have a document on the desktop so that'
    ' we can run our application macro.
    objApp.Presentations.Add
    ' Then we can run our application macro.
    objApp.Presentations.Open FileName:=strLocPath & strFile, ReadOnly:=True
    Dim i As Integer
    On Error Resume Next ' "Automaton error" on MBNACard.xls ???
    For Each oItem In objApp.ActivePresentation.VBProject.VBComponents
    strStatusBar = strLocPath & strFile & strcSeparator & oItem.Name
    Application.StatusBar = strStatusBar
    strModuleCode = strPackModule(oItem, lngar)
    strPresentationCode = strPresentationCode & strModuleCode
    lngar(5) = lngar(5) + 1
    Next oItem
    ' Close the Presentation with no changes
    objApp.ActivePresentation.Close
    Dim lngcount As Long
    lngcount = objApp.Windows.Count
    ' And when we are done, we unload the PowerPoint application.
    If Tasks.Exists("Microsoft PowerPoint") = True Then ' Gary Frieder
    Tasks("Microsoft PowerPoint").Close
    End If
    ' Set objApp = Nothing THIS was the offender?
    DoEvents
    </pre>





    >> gary Frieder pointed me <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=ppt&Number=10216&page=0&vie w=collapsed&sb=5>Here</A>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •