Results 1 to 15 of 15
  1. #1
    Lounger
    Join Date
    Aug 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    pjReset in VBA (2002)

    Ok, I hate to ask this simple one but I can't get VBA to accept the pjReset command,

    MSProject.PjToolbarAction.pjReset

    I am running a lot of macros that write to excel. I need to be able to "hit the reset" button between runs without opening up the VBA editor (since users will be running these macros).

    Any help is appriciated.

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: pjReset in VBA (2002)

    I am not sure if this will help, but try :

    With Application.PjToolbarAction
    .Reset
    End With
    Regards,

    Gary
    (It's been a while!)

  3. #3
    Lounger
    Join Date
    Aug 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: pjReset in VBA (2002)

    oh well... it told me -- "object doesn't support this property or method"....

    There has to be a way. I need it to release the instance of Excel (which it doesn't do even with a quit command)....

    Arrgg.

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: pjReset in VBA (2002)

    Did you try to set the excel app = Nothing in your code to terminate the application?

    I.e, in access, if using project,

    prjApp.Quit

    Set prjProject = Nothing
    Set prjApp = Nothing

    HTH
    Regards,

    Gary
    (It's been a while!)

  5. #5
    Lounger
    Join Date
    Aug 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: pjReset in VBA (2002)

    Yes. I am doing that....

  6. #6
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: pjReset in VBA (2002)

    <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15> ... now I am confused. Can we start from the beginning. What is it exactly you are trying to do? You are sending data to excel from MS Project. Why would you be resetting the toolbar in MS Project? Just curious. Of course if you figured out what you were trying to do by now, please post the answer for us to review.

    Thanks
    Regards,

    Gary
    (It's been a while!)

  7. #7
    Lounger
    Join Date
    Aug 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: pjReset in VBA (2002)

    I am creating multiple graphs in excel from project. There is some memory bug that MS Project doesn't release between runs. If I run once, it works fine. If I run again without closing MS Project everything if there except the graph.

    I found out that if I physically hit the reset button it releases the memory. So I figured that if there was a command to simulate the reset then I would use it. I just can't get project to simulate it.

    George

  8. #8
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: pjReset in VBA (2002)

    Any chance you can post a stripped down version of your project so I can try it out. I would also like to review the code to see if I can come up with a solution.
    Regards,

    Gary
    (It's been a while!)

  9. #9
    Lounger
    Join Date
    Aug 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: pjReset in VBA (2002)

    Please see attached text file.
    Attached Files Attached Files

  10. #10
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: pjReset in VBA (2002)

    George,

    I don't see why the code wouldn't work, although, you are beyond my capabilities with what you are doing. I am going to post a note to the moderators and mvp's with more experience with excel and VBA to see if we can get a solution.
    Regards,

    Gary
    (It's been a while!)

  11. #11
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: pjReset in VBA (2002)

    Well Gary has awakened me. I normally stay away from the Project board because the only copy of Project that I have is version 4! Version 4 won't let me create a Reference to Excel so I cannot try out any code, but starting with the line
    Workbooks.Open FileName:="C:PMOEV_GraphsKey Metrics.xls"
    most all of the code needs to be rewritten.

    Never use an Excel collection (Workbooks, Sheets) without prefixing it with one of the the XL objects that you have created: xlApp, xlBook, xlRng (you probably will need to create an xlSheet object also). This will allow you to get rid of all of the Activates. Instead of doing an Activate, just create an object like you did with xlBook. I would have a variable for each of your workbooks. Finally change ActiveWorkbook to xlBook or whichever workbook you are using.

    If I were you, I would take a major step backwards. Just take the few lines of code that starts Excel, move just one thing (like the project start date) to Excel, close Excel, and cleanup. See if you can run this code multiple times. Then add just a little bit more and recheck. Right now, I think that you have too big of a monster to fix. It's OK to build a monster like this, but you need to do it in very small steps. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: pjReset in VBA (2002)

    I believe the End statement is what you need. It resets your project (and alas also all global variables).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: pjReset in VBA (2002)

    Hi,
    Further to Sammy's points, attached is a rough example of how your code ought to look - without knowing exactly what you're doing it's hard to create the best code! There is one bit I noticed where you have a loop incrementing x by 1 within which you copy sheets(1) from one book to another - is this meant to be Sheets(x) or do you actually want several copies of the same sheet?
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #14
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: pjReset in VBA (2002)

    Thanks for taking the time to make those corrections, Rory. It looks very nice now. If only it runs for gehaney.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  15. #15
    Lounger
    Join Date
    Aug 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: pjReset in VBA (2002)

    Thanks for the infomation and the excellent code. I worked it into the macro and it it still does the things I want it to.

    It still doesn't "reset" for me though, and I have told the project managers to close project and reopen it to solve this issue.

    Thanks

Posting Permissions

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