ExportAsFixedFormat有时会失败 [英] ExportAsFixedFormat fails sometimes

问题描述

我有一个包含表格和宏的电子表格，该表格一直有效到最近.第一次看到所描述的行为是我第一次尝试在Excel 2016(Windows 10)中使用此特定功能，尽管我不确定这是原因.

I have a spreadsheet with forms and macros that worked until recently. The first time I saw the described behavior was the first time I tried using this particular function in Excel 2016 (Windows 10), though I'm not sure that's the cause.

我遇到的问题是以下行

Worksheets("Label Template - 100X150").ExportAsFixedFormat Type:=xlTypePDF, _
  Filename:=pdfFilePath, Quality:=xlQualityMinimum, _
  IncludeDocProperties:=False, _
  IgnorePrintAreas:=False, OpenAfterPublish:=True

来自以下宏:

Sub PDFLabelsSheet()
'On Error GoTo errHandler  

'enter name and select folder for file
strFile = "Labels_PrintGroup-" & lstPrintGroup.Value _
            & "_" _
            & Format(Now(), "yyyy-mm-dd\_hhmm") _
            & ".pdf"
strFile = ThisWorkbook.Path & "\" & strFile

Worksheets("Label Template - 100X150").Visible = True
UnprotectTab "Label Template - 100X150"

pdfFilePath = Application.GetSaveAsFilename(InitialFileName:=strFile, _
  FileFilter:="PDF Files (*.pdf), *.pdf", _
  Title:="Select Folder and FileName to save")

If pdfFilePath <> "False" Then

    Worksheets("Label Template - 100X150").Select
    Worksheets("Label Template - 100X150").Range("A1").Select
    Worksheets("Label Template - 100X150").Range("A1").Activate
    Cells.Activate

    Worksheets("Label Template - 100X150").PageSetup.FirstPageNumber = 1

    Worksheets("Label Template - 100X150").ExportAsFixedFormat Type:=xlTypePDF, _
      Filename:=pdfFilePath, Quality:=xlQualityMinimum, _
      IncludeDocProperties:=False, _
      IgnorePrintAreas:=False, OpenAfterPublish:=True

End If

exitHandler:
    ExecutionEnd
    Exit Sub
errHandler:
    MsgBox "Something went wrong, a PDF could not be created", vbCritical
    Resume exitHandler

End Sub

我尝试了ExportAsFixedFormat函数的排列，将其更改为Worksheets("name")，(number)，ActiveSheet ...等.

I've tried permutations of the ExportAsFixedFormat function, changing it to Worksheets("name"), (number), ActiveSheet.... etc.

在执行此特定的Sub之前，工作表未隐藏且不受保护.我尝试将整个范围设置为PrintArea，也尝试选择并激活要导出的范围.

Prior to executing this particular Sub, the worksheet is unhidden and unprotected. I've tried setting the entire range as PrintArea, I've tried selecting and activating the range to be exported as well.

最令人沮丧的部分是它有时会起作用.我已经投入了一些代码，这些代码会吐出PageSetup集合的每个属性，以确保某些东西不会炸毁，因为输出非常严格地依赖于页面设置.

The most frustrating part is it works sometimes. I've gone as far as putting in code that spits out every property of the PageSetup collection to make sure that something is not blowing things up since the output depends so strictly on the page setup.

当我手动导航到此工作表并从文件菜单中选择导出功能时，会出现相同的错误.如果我在该工作表中单击一下，则导出功能和宏工作都会发生.

When I navigate to this worksheet manually and choose the export function from the file menu, I get the same error. If I click around in that worksheet for a bit the Export function as well as the Macro work.

PDFLabelSheets Sub确实从另一个Sub(通过表单的按钮按下调用)调用，该SubSub将大量数据编译到此选项卡中，尽管我已将其排除为罪魁祸首，因为仅连续运行PDF代码将成功然后在不运行其他代码的情况下失败.

The PDFLabelSheets Sub does get called from another Sub (invoked by a form's button press) which compiles a boatload of data into this tab, though I've ruled it out as the culprit as successive runs of just the PDF code will succeed then fail without running the other code.

推荐答案

我最近通过以下方式解决了一个相似的问题:

I’ve recently solved a problem that was similar in the following ways:

• 它涉及到 ExportAsFixedFormat，类型:= xlTypePDF 的故障声明
• 该语句失败，没有错误消息；它只是停止执行VBA代码
• 失败是不一致的，失败或成功的条件不明显

下面是我如何解决它的摘要；也许这将建议您可以使用的一些有用方法.

Below is a summary of how I solved it; perhaps this will suggest some useful approaches you can use.

我的Excel VBA代码在Windows 7下作为后台无人值守的计划任务在后台运行，通常是在没有人登录的情况下进行的.在大多数情况下，该代码可以工作，但有时不起作用.崩溃后我无法捕获屏幕，捕获各种窗口内容也无济于事:没有有用的诊断文本.我最终尝试将程序执行里程碑记录到磁盘文件中，并为每个里程碑重新打开和关闭文件，因此，如果程序随后崩溃，则不会丢失任何文本.这就将问题隔离在 ExportAsFixedFormat 语句中，但没有诊断出问题.

My Excel VBA code was running under Windows 7 as an unattended, end-of-day scheduled task in the background, usually when no one was logged in. Most of the time the code would work, but sometimes it would not. I could not capture the screen after a crash, and capturing the various window contents did not help: there was no useful diagnostic text. I finally tried logging program execution milestones to a disk file, reopening and closing the file for each milestone, so no text would be lost if the program subsequently crashed. That isolated the problem to the ExportAsFixedFormat statement but did not diagnose the problem.

我最终尝试让VBA代码在故障点之前保存工作簿的副本.我希望，在重新打开副本时，我能够看到出了什么问题.那没有帮助.但是，在比较成功和不成功执行时保存的.xlsm文件时，我确实发现了一些奇怪的东西–前者稍大一些.

I finally tried having the VBA code save a copy of the workbook just prior to the point of failure. I was hoping that, on re-opening the copy, I’d be able to see what was wrong. That did not help. However, I did notice something strange when comparing the .xlsm files saved on successful and unsuccessful executions – the former were just a little larger.

在进入.xlsm文件(实际上是.zip文件)时，我注意到在 xl \ printerSettings 子文件夹中， printerSettings1.bin 文件更大.运行成功后.使用SysInternals Strings实用程序检查.bin文件，我发现在不成功的运行中，该文件仅包含我在其中开发应用程序的家用计算机上的默认打印机的名称，而不是其所在的办公计算机.正在执行.在成功运行时，.bin文件还包含办公室中默认打印机的名称.

Digging into the .xlsm files (which are really .zip files), I noticed that, in the xl\printerSettings subfolder, the printerSettings1.bin file was larger after successful runs. Using the SysInternals Strings utility to inspect the .bin files, I found that, in the unsuccessful runs, this file contained only the name of the default printer on the home computer where I had developed the application, which was not the office computer where it was executing. On the successful runs, the .bin file also contained the name of the default printer in the office.

这是关键的区别.在该办公室，主计算机在工作日结束后不久执行Excel程序.通常，在白天，此计算机将打印到连接到网络上另一台计算机的激光打印机(设置为默认打印机)，并且在工作日结束时关闭该另一台计算机.因此，根据时间安排的偶然性，有时收尾的Excel程序会看到有效的可用默认打印机，有时却看不到.

This was the critical difference. In that office, the main computer executes the Excel program shortly after the end of the working day. Ordinarily, during the day this computer prints to a laser printer (set as the default printer) attached to another computer on the network, and that other computer is turned off at the end of the working day. Therefore, depending on accidents of timing, sometimes the end-of-day Excel program would see a valid, available default printer, sometimes not.

某些Excel命令(包括另存为.pdf的命令)仅在活动打印机有效时才能正常工作.在无法设置PageSetup.Orientation = xlLandscape fromMS Project ，涉及对.PageSetup字段的分配.就我而言，无法正常工作意味着崩溃而没有生成任何错误消息.

Some Excel commands, including the command to save as a .pdf, work properly only when the active printer is valid. There is a similar observation at can't set PageSetup.Orientation = xlLandscape from MS Project, concerning assignments to the fields of .PageSetup. In my case, failing to work properly meant crashing without generating any error message.

后来，我发现我的问题不仅是网络上默认激光打印机的不可用；还包括这也是由于打印机定义不规范引起的.我已经添加了代码来记录VBA属性 Application.ActivePrinter ；在崩溃之前，它返回的值是未知打印机(检查控制面板)"，而不是包含打印机的名称.当我重新安装默认打印机时， Application.ActivePrinter 总是返回正确的打印机名称，无论该打印机是否可用，程序都不会崩溃.

I later discovered that my problem was not simply the unavailability of the default laser printer over the network; it was also caused by something irregular about the printer definition. I had added code to log the VBA property Application.ActivePrinter; just prior to a crash, it was returning the value "unknown printer (check your Control Panel)", rather than containing the name of the printer. When I re-installed the default printer, Application.ActivePrinter always returned the proper printer name, whether or not that printer was available for use, and the program stopped crashing.

我的问题是一个真正的Heisenbug-每当我随机重新运行Excel工作簿时，它都会消失.最终证明可以帮助我确认其诊断.在无人值守的情况下运行，有时没有有效的打印机.当我登录并运行帐户时，要么是在工作日，要么是LogMeIn临时将我的家用打印机分配为有效的默认打印机.这最终使我们意识到，只有在执行时没有密切注意程序的情况下程序才会失败.

My problem was a true Heisenbug – it disappeared whenever I re-ran the Excel workbook at random times. That ultimately turned out to help me confirm its diagnosis. Running unattended, sometimes there was no valid printer. When I logged into my account and ran it attended, either it was during the business day or else LogMeIn temporarily assigned my home printer as a valid default printer. This finally made sense of the fact that the program failed only when it wasn’t being closely observed at the time of execution.

总而言之，我认为不一致的打印机可用性和不规则的打印机安装可能是导致无法使用Excel VBA始终导出.pdf文件的潜在原因.

In summary, I’d look at inconsistent printer availability and irregular printer installation as possible underlying causes of failure to consistently export .pdf files using Excel VBA.

这篇关于ExportAsFixedFormat有时会失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！