title: DocumentExport copies pdf to an excel page but leaves a copy of the pdf open, which I cannot close
body: <p>I have a Microsoft project vba application where I want to copy a selection of tasks using the "marked" field to identify all of the predecessor tasks to a target task, identified as the "target" below. When I have traced the network back to include only incompleted tasks, control passes to a routine which uses DocumentExport to create a copied file and save it to a pdf. Then, using ActiveSheet.OLEObjects.add, take this PDF and copy to a specific excel Tab with the "A3" cell being the top/left corner for the file to be placed.</p>
  
  <p>excerpts of my current code:</p>
  
  <pre><code>    target = ActiveCell.Task
      SaveFilePath = "C:\Macros\"
      SaveFileName = SaveFilePath &amp; "Target-" &amp; target &amp; ".pdf"
      SaveFilePath = "C:\Macros\"
      SaveFileName = SaveFilePath &amp; "Target-" &amp; target &amp; ".pdf"
  
      Application.FilePageSetupView Name:=".MarkedPred_View", allsheetcolumns:=True, BestPageFitTimescale:=True
      Application.FilePageSetupPage Name:=".MarkedPred_View", Portrait:=False, PagesTall:=6, PagesWide:=1, PaperSize:=pjPaperLegal, FirstPageNumber:=False
      StrHeader = "&amp;18&amp;B" &amp; GetFontFormatCode("Calibri") &amp; "Status Date=" &amp; Format(ActiveProject.StatusDate, "mm/dd/yy") &amp; " Task Name= " &amp; SelTask.Name &amp; " ID:" &amp; SelTask.ID &amp; " UID:" &amp; SelTask.UniqueID
      Application.FilePageSetupHeader Name:=".MarkedPred_View", Alignment:=pjCenter, Text:=StrHeader
      Application.FilePageSetupLegend Name:=".MarkedPred_View", LegendOn:=pjNoLegend
  
      DocumentExport SaveFileName, pjPDF, FromDate:=EarliestStart - 30, ToDate:=LFin + 30
  
      xlsheet.Range("A3").Select
      ActiveSheet.OLEObjects.Add(FileName:=SaveFileName, Link:=True _
              , DisplayAsIcon:=False).Activate
  </code></pre>
  
  <p>If I set the Link property to false, the copy to excel does not happen</p>
  
  <pre><code>    sbDeleteAFile (SaveFileName)
  
      Sub DeleteAFile(ByVal FileToDelete As String)
           IsFileOpen (FileToDelete)
               SetAttr FileToDelete, vbNormal
               Kill FileToDelete
      End Sub
  
      Function IsFileOpen(FileName As String)
      Dim filenum As Integer, errnum As Integer
  
      OutputStr = ("1587 - IsFileOpen - started for = " &amp; FileName)       'added
      Call Txt_Append(MyFile, OutputStr)
  
      On Error Resume Next   ' Turn error checking off.
           filenum = FreeFile()   ' Get a free file number.
           ' Attempt to open the file and lock it.
           Open FileName For Input Lock Read As #filenum
           Close filenum          ' Close the file.
           errnum = Err           ' Save the error number that occurred.
           On Error GoTo 0        ' Turn error checking back on.
  
  
          ' Check to see which error occurred.
          Select Case errnum
  
  
              Case 0
                 IsFileOpen = False
                 'Open (Filename)
                 ' Error number for "Permission Denied."
                 ' File is already opened by another user.
                 OutputStr = ("1587 - IsFileOpen - is NOT Open")       'added
                 Call Txt_Append(MyFile, OutputStr)
             Case 70
                 IsFileOpen = True
  
                ' Another error occurred.
             Case Else
                 OutputStr = ("1587 - IsFileOpen - IS Open")       'added
                 Call Txt_Append(MyFile, OutputStr)
                 Error errnum
           End Select
  
      End Function
  </code></pre>
  
  <p>"LFin" is the finish date of the target task, from which I am collecting all of its predecessors.  I am using the finish date as the "Latest Finish" (LFIN) to bound the "ToDate" in the command.</p>
  
  <p>The error appears with the "ActiveSheet.OLEObjects.Add (fileName:=SaveFilename, Link:=True _" command, where the PDF is opened and copied to the specified excel tab with cell "A3" being the point of the paste for the image.</p>
  
  <p>I do not have any code to close the PDF in this snippet so I get an error when I try to delete an open file. I have seen lots of discussion on various boards where if a file is opened by another application, MS Project VBA cannot delete it as it does not have the handle to the file (??).  If I manually close the PDF, close the error notification in the debugger and then press "Run/Continue" , the PDF is deleted and cycles back through the main routine, just like I want it to but I have to again close the newly created PDF, clear the dialog and select Run/Continue.</p>
  
  <p>The only section of this code which does not work as desired (and is currently missing in this code) is having the ability to close the PDF after it has been copied to Excel as it is no longer needed.  I have only seen very complicated code which gets the handle of the PDF and then allows you to close the specific file without affecting any other PDF files which may also be open and are not part of this process.</p>
  
  <p>Does anyone have any ideas? I first started using CopyToClipboard, but this command only can copy 16 rows of MS Project schedule to the clipboard.  Then, I tried ExportAsFixedFormat, but the FromDate and ToDate entries have no effect on the displayed image. </p>
  
  <p>Using DocumentExport and Application.OLEObjects.Add  allows me to copy unlimted pages of schedule to the clipboard and paste into an excel tab showing the desired dates only.This is the closest I have been able to come to get what I want the output to look like.  I have been unable to find an associated command to Application.OLEObjects.Add command which I can use to close the PDF file created by the Application.OLEObjects.Add.  It certainly makes sense that you want to open the PDF file so it can be copied to the Excel tab, but it is surprising  there is not also an easy way to close that PDF file after it has served its purpose.</p>