DocumentExport将pdf复制到excel页面，但打开pdf副本，但无法关闭

问题描述

我有一个Microsoft Project vba应用程序，我想在其中使用标记"字段复制选择的任务，以将所有先前任务标识为目标任务，该目标任务在下面称为目标".当我追溯到网络仅包含未完成的任务时，控制权将传递给一个例程，该例程使用DocumentExport创建复制的文件并将其保存为pdf.然后，使用ActiveSheet.OLEObjects.add将此PDF并复制到特定的excel选项卡，其中"A3"单元格是要放置文件的左上角.

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.

我当前代码的摘录:

    target = ActiveCell.Task
    SaveFilePath = "C:\Macros\"
    SaveFileName = SaveFilePath & "Target-" & target & ".pdf"
    SaveFilePath = "C:\Macros\"
    SaveFileName = SaveFilePath & "Target-" & target & ".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 = "&18&B" & GetFontFormatCode("Calibri") & "Status Date=" & Format(ActiveProject.StatusDate, "mm/dd/yy") & " Task Name= " & SelTask.Name & " ID:" & SelTask.ID & " UID:" & 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

如果将Link属性设置为false，则不会复制到excel

If I set the Link property to false, the copy to excel does not happen

    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 = " & 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

"LFin"是目标任务的完成日期，我将从中收集所有它的前任任务.我将完成日期用作最新完成"(LFIN)来绑定命令中的"ToDate".

"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.

该错误与"ActiveSheet.OLEObjects.Add(fileName:= SaveFilename，Link:= True _)命令一起出现，在其中打开PDF并将其复制到指定的excel选项卡，其中单元格"A3"是粘贴图像.

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.

在此代码段中，我没有任何代码可以关闭PDF，因此在尝试删除打开的文件时出现错误.我在各种板上看到了很多讨论，在这些板上，如果文件是由另一个应用程序打开的，则MS Project VBA无法删除它，因为它没有文件的句柄(??).如果我手动关闭PDF，请在调试器中关闭错误通知，然后按"Run/Continue"(运行/继续)，将删除PDF并循环回到主例程，就像我想要的一样，但是我必须再次关闭新创建的例程PDF，清除对话框，然后选择运行/继续".

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.

此代码中唯一无法正常工作的部分(当前在此代码中缺失)是能够将PDF复制到Excel之后关闭它，因为不再需要它.我只看到过非常复杂的代码，该代码可以获取PDF的句柄，然后使您可以关闭特定文件，而不会影响任何其他打开或不属于此过程的PDF文件.

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.

有人有什么想法吗?我首先开始使用CopyToClipboard，但是此命令只能将16行MS Project计划复制到剪贴板.然后，我尝试了ExportAsFixedFormat，但是FromDate和ToDate条目对显示的图像没有影响.

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.

使用DocumentExport和Application.OLEObjects.Add，我可以将时间表的未限制页面复制到剪贴板，然后粘贴到仅显示所需日期的excel标签中.这是我能够获得的最接近的日期输出看起来像.我无法找到与Application.OLEObjects.Add命令关联的命令，该命令可用于关闭由Application.OLEObjects.Add创建的PDF文件.您确实想打开PDF文件，以便可以将其复制到Excel选项卡中，这确实是有道理的，但是令人惊讶的是，在达到其目的之后，没有一种简便的方法来关闭该PDF文件.

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.

推荐答案

问题归结为:

打开pdf文件的原因是代码告诉了它.通过在刚刚添加的OLEObject上使用 Activate 方法，它会激活它-意思是打开pdf文件.

The reason the pdf file opens is that the code is telling it to. By using the Activate method on the OLEObject just added, it activates it--meaning in opens the pdf file.

解决方案是简单地将 OLEObjects.Add 方法添加到此:

The solution is to simply the OLEObjects.Add method to this:

ActiveSheet.OLEObjects.Add FileName:=SaveFileName

这篇关于DocumentExport将pdf复制到excel页面，但打开pdf副本，但无法关闭的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！