I wish to capture a screen shot of a Microsoft Project view (table data and Gantt portion of the view), save to a pdf so the PDF can be loaded as an OLE Object onto an excel worksheet. I have tried EditCopy and EditCopyPicture, but both of these work through the Office Clipboard. The office Clipboard is limited to 24 images and does not self cleanse itself. There is also no apparent ability to tell the clipboard as to whether to keep/delete any image when placed onto the clipboard. There does not appear to be any way of clearing the Office Clipboard using VBA either, though there are examples of using windows code to clear the clipboard, but I suspect this may be the Windows Clipboard, which is distinct from the Office Clipboard. The issue with my current process is that it uses the office clipboard, which I would like to eliminate because I see a time penalty once the office clipboard is filled, assuming due the swapping out of an old image to make room for the new image.
I am using a 64 bit windows machine, with Microsoft Office Project 2019 and Office Pro 2019 suite.
Since the file type that I want to load as an OLEObject is a PDF or xps, is there a way I can use the Fileprint or Printout commands to save a current view in Microsoft Project into a pdf or xps file which I will then incorporate into my process . I would have a need to be able to define the start and end dates for the Gantt display and be able to define a filename that is saved, as well as define the number of pages I want the PDF/XPS export file to be to make it as readable as possible. I am currently using C:\export\export.xps as a file name and file path.
What would be ideal would be a process to create the PDF and load onto the excel spreadsheet as an OLEObject is one step, but I have not found any code that can accomplish this. I have ported a lot of the code to be executed by excel in a macro-enabled spreadsheet, which is saving me 90% of the processing time that MS Project was taking for this portion of the code. If I am able to save the pdf of the MS Project screen, I will pick up the PDF and continue the process within the excel code. I may need to add Application.UpdateDisplay code before and after the Fileprint command to avoid seeing a dialog. Here is what I have for working code. The only portion not working is my being able to create a pdf of what is displayed on the filter MS project screen:
'show 12 columns and move the splitter
Application.SetSplitBar ShowColumns:=11
'adjust timescale to show weeks
TimescaleEdit MajorUnits:=0, MinorUnits:=3, MajorLabel:=0, MinorLabel:=17, MinorTicks:=True,
Separator:=True, TierCount:=2
TierCount:=2
If Order >= 20 Then
'ScaleRow = Int(100 * 18 / Order)
ScaleRow = Int(100 * 24 / Order)
Else
ScaleRow = 70
End If
TimescaleEdit Enlarge:=ScaleRow, Separator:=True, TierCount:=2
SelectRow
localfin = Now()
Debug.Print "Iteration Prep for SRA Summary Page- " & (((localfin - localst) * 24 * 60 * 60) & "
sec!")
localst = Now()
SelectAll
Application.WrapText (3)
Sort Key1:="Number10", Ascending1:=True
CalculateAll
fullpath = "C:\Export\export.pdf"
Debug.Print "MinDate= " & MinDate & " MaxDate= " & MaxDate
Application.PrintOut copies:=1, Preview:=False, ActivePrinter:="Microsoft Print To PDF",
Fromdate:=MinDate, todate:=MaxDate, PrToFileName:=fullpath, PrintToFile:=True
localfin = Now()
Debug.Print "Sort & PrintToPDF- " & (((localfin - localst) * 24 * 60 * 60) & " sec!")
localst = Now()
MinDate and Maxdate are the earliest and latest dates showing on the filtered view of tasks. I am calculating execution times throughout my procedure to identify where I should try to optimize the coding. Scalerow is a scaling factor whereby I am trying to balance the share of the screen that the text fields require versus the right side of the screen for the Gantt chart. I can also affect the Timescale data to change to either weekly, monthly or quarterly display for the Gantt depanding on the number of weeks between the Mindate and MaxDate. I can also determine the number of pages at a reasonable scaling that I will need to capture the screen data for later display.
I have experimented with documentexport, but think this command is limited to exporting the text portion displayed of a Gantt chart.
The crosoft documentation ("https://docs.microsoft.com/en-us/office/vba/api/Project.Application.DocumentExport") suggests that it should work for Microsoft project, but I have not been able to save anything to a PDF/XPS file using this command. All I want/need to do is be able to create a pdf file (perhaps by selecting a printer, setting it as the default, and printing, though I have not been successful in finding any code to help me in getting there). I am trying to use the "Microsoft Print to PDF" printer.
Any help is appreciated.
