locked
Project 2010 Help with Macro to set Preview/Printer settings RRS feed

  • Question

  • I just upgaded to Project Professional 2010 from 2007.  The 2007 macros I have don't work properly with 2010.  I have tried using the macro record function but it does not capture the change printer mode. 

    Here's what I'm trying to do with the macro:

    1. Set the view, group, filter etc...

    2. Go to Print Preview to change the printer setting from the default printer "5500 HP Laser" to the "Adobe PDF" printer.  (2007 once you changed the printer in a session it would remain that way, 2010 automatically resets back to the computers default printer each time.)

    3. Set the start and finish dates.  (This report is to show an eight week window.  I go in every week to the VBA Editor and reset the dates.  VBA's are not my strong suit.  It would be nice if it could set its own dates - minus one week for start and plus eight weeks for finish from a "Today" date.)

    This is the simple macro that worked in 2007 version.  I would appreciate any guidance.          

    Sub Eight_Week()  2007 version
    ' Macro Recorded 8/23/06 by Steve.
        CalculateAll
        ViewApply Name:="Gantt Chart Titles"
        SelectSheet
        TableApply Name:="Entry IPT Title"
        GroupApply Name:=" POC Gov"
        FilterApply Name:=" 8 week Report"
        '5 tabs to type the first date
        '1 tab to type in the last date
        '3 more tabs then ~ (enter) to hit preview
        SendKeys "{TAB}{TAB}{TAB}{TAB}{TAB}1/28/11{TAB}4/1/11{TAB}{TAB}{TAB}~"
        FilePrint

    End Sub

    Steve

    Saturday, February 19, 2011 2:30 PM

Answers

  • Hi,

    only a thought ... Not sure if you need the print preview displayed?

    Have you tried:
        CalculateAll
        ViewApply Name:="Gantt Chart Titles"
        SelectSheet
        TableApply Name:="Entry IPT Title"
        GroupApply Name:=" POC Gov"
        FilterApply Name:=" 8 week Report"
        TimescaleEdit MajorUnits:=1, ..... whatever you need
        DocumentExport  FileName:="C:\Users\<Username>\Documents\Projekt1.pdf", FromDate:="21.02.11 08:00", ToDate:="19.04.11 08:00"
        FileSaveAs Name:="", FormatID:=""

    If you want to record it: I created this macro by following: File - Save as - PDF file (*.pdf) in Save as file type, entered from and to into Document Export Options.

    Regards
    Barbara

     

    Monday, February 21, 2011 2:26 PM

All replies

  • Steve,

    I don't use a printer with Project so I can't test this code, but try it and see if it gets what you need.

    Sub Eight_Week()  2010 version
        Dim Past as Date, Future as Date

        Past=ActiveProject.CurrentDate-7 'sets start one calendar week back

        Future=ActiveProject.CurrentDate+56 'sets finish 8 calendar weeks ahead
        CalculateAll
        ViewApply Name:="Gantt Chart Titles"
        TableApply Name:="Entry IPT Title"
        GroupApply Name:=" POC Gov"
        FilterApply Name:=" 8 week Report"
        FilePrintSetup "Adobe PDF"
        FilePrint FromDate:= Past, ToDate:= Future, Preview:=true

    End Sub

    John

     

    Saturday, February 19, 2011 4:46 PM
  • John,

    Thank you for the input.  But its does not seem to work.  It's running without an error.  But it's not changing the printer setting or populating the date fields?  The Preview Window reamains at the default printer and the date settings button remain at "Print Entire Project" with the original dates.

    Steve 

    Saturday, February 19, 2011 6:04 PM
  • Steve,

    My guess is that it is a bug. There have been other issues with VBA in Project 2010. Microsoft is aware of some issues but I don't know if this is one of them. At some point an update will be released, but again I don't know when.

    I don't have a good alternate automated approach for you other than set up the print manually each week (a pain but not that bad), or if possible use the modified macro in Project 2007.

    John

    Saturday, February 19, 2011 7:47 PM
  • John,

    Disregard the routine of setting up the views, tables, filters etc... , that all works.  I find it difficult to believe that in MSP2010 there is no way to set the Preview/Print dialog to start/finish dates other than manually each time.  I have different Project files that require status reports for stakeholders and workgroups.  Some are 8 week, 16 week, quarterly tailor to meet the needs of the meetings or reviews being conducted.  In MSP2007 the work around was to open the print dialog box and use the SendKey command to move the cursor around with the (~) enter to preview.  Might there be a different way to approach this?   

    Steve

    Sunday, February 20, 2011 9:51 PM
  • Steve,

    Believe me, I understand your frustration. I'm sure you've learned as I have that the latest isn't always the greatest. Project 2010 still has bugs and as I noted previously some of those bugs are manifested in VBA. Just so you don't feel you are alone, one of the premier Project MVPs, Rod Gill, expressed utter frustration with all the issues in VBA for Project 2010. Rod is the author of the book "VBA programming for Microsoft Project".

    I sent a message to a contact at Microsoft about this issue. I hope to hear something back this next week but at best it will only be recognition that the problem is known and potentially some idea as to when the issue will be fixed.

    I wish I could be of more help but I just don't know of any "workaround" other than what I noted previously.

    John

    Sunday, February 20, 2011 11:44 PM
  • John,

    I sincere appreciate your help. It's great people like you who voluneer your time to work with us. Thank you so much. 

    Steve

    Monday, February 21, 2011 2:35 AM
  • Hi,

    Bad "news" : it didn't work in 2003 either (I had to invent the Sendkeys routine too) thus I never tried it in 2007, now I find out it doesn't work in 2010 either - and if the sendkeys trick doesn't work any longer we're really stuck.

    Monday, February 21, 2011 7:23 AM
  • Jan,

    It may still work, but I can not find the right sequence of strokes for {TABS}, {~}, {Up} and {Down} etc..., to lock in the input and refresh the settings. Clearly, the "FilePrint FromDate:= X/XX/XX, ToDate:= X/XX/XX, Preview:=true" is not working as expected or there is a missing keystroke?  The dates in the Preview dialog box do not update.

    Monday, February 21, 2011 12:37 PM
  • Hi,

    only a thought ... Not sure if you need the print preview displayed?

    Have you tried:
        CalculateAll
        ViewApply Name:="Gantt Chart Titles"
        SelectSheet
        TableApply Name:="Entry IPT Title"
        GroupApply Name:=" POC Gov"
        FilterApply Name:=" 8 week Report"
        TimescaleEdit MajorUnits:=1, ..... whatever you need
        DocumentExport  FileName:="C:\Users\<Username>\Documents\Projekt1.pdf", FromDate:="21.02.11 08:00", ToDate:="19.04.11 08:00"
        FileSaveAs Name:="", FormatID:=""

    If you want to record it: I created this macro by following: File - Save as - PDF file (*.pdf) in Save as file type, entered from and to into Document Export Options.

    Regards
    Barbara

     

    Monday, February 21, 2011 2:26 PM
  • Steve,

    I didn't mention in my last response but I did try the code in Project 2007 and it did seem to set the from and to dates as well as call up the print preview. So if the functionality was added in Project 2007, there is definitely a glitch in that part of VBA for Project 2010.

    One thought that came to mind about alternate approaches that may be of interest to you. If you really need the Gantt display it may be possible to set up your filter and then copy the selected tasks to a new project file, (call it a temporary report file), with calculation set to "off". You may also have to set some constraints on tasks in the report file so they don't move. Then you should be able to print that file. It's a bit kludgy but it might be a viable workaround and the whole process can be automated with VBA.

    Also, if you don't really need the Gantt display, you might consider exporting the tabular data to Excel and then print from there. Whenever I've done custom reports for someone, I always export the data to Excel using VBA and then format as needed.

    The bottom line is that there are some options.

    John 

    Monday, February 21, 2011 3:43 PM
  • Barbara / John,

    Microsoft, what have you done to us!!!  I have been planning & scheduling with Project for over 15 years in the ship building industry.  The question of having to seeing it in Preview mode first is, I feel is essential.  (I know I'm preaching to the choir.)  It doesn't matter if its a PDF, Printer or Plotter size presentation document.  Preview is the QA step in publishing the project to the stakeholders and working groups.  It's to ensure the pages are formatted correctly, timescale is set properly, and most importantly the correct tasks & fields at being shown.  To add the element of DocumentExport or creating a temporary report file just adds a layer where publishing errors may occur.  It is a possible work around if all else fails.  I do thank you for the ideas... 

    Monday, February 21, 2011 5:34 PM
  • Barbara,

    I took both your advices and added John's DIM input for dates.  It does create a working PDF report to the desktop.  I will use the same concept to change my other macros as a work around the Preview/Print dialog box.

    Sub Test_Eight_Report()

        Dim Past As Date, Future As Date
        Past = ActiveProject.CurrentDate - 7 'sets start one calendar week back
        Future = ActiveProject.CurrentDate + 56 'sets finish 8 calendar weeks ahead

        CalculateAll
        ViewApply Name:="Gantt Chart Titles"
        SelectSheet
        TableApply Name:="Entry IPT Title"
        GroupApply Name:=" POC Gov"
        FilterApply Name:=" 8 Week Report"
        DocumentExport FileName:="C:\Documents and Settings\Desktop\Test File TEST3.pdf", FromDate:=Past, ToDate:=Future
       

    End Sub

    I did remove the last line ("FileSaveAs Name:="", FormatID:="") because the macro was hanging up.  Seems to be working without it.

    Steve

    Monday, February 21, 2011 7:33 PM
  • Good to read. For sure it's not the optimum solution, but at least it is working.

    I came across your question while I was looking for differences in behaviour between printing as PDF, and saving as PDF File. So it was a quick test how a macro for this looks like (that's the reason for the last line) - and I saw that start and finish dates are parameters in this solution.

    Good luck for your other macros!
    Barbara

    Monday, February 21, 2011 8:19 PM