将工作簿中的每个工作表保存为单独的pdf [英] Save each worksheet in workbook as an individual pdf

问题描述

我想循环使用工作簿中的所有工作表，并将它们作为单独的pdf保存在与工作簿相同的路径中。文件以工作表名称命名。

以下代码可以运行到wsA.ExportAsFixedFort行。我得到的错误信息是：

 运行时错误'91'：对象变量或块变量未设置
  

但我无法弄清楚问题是什么...

任何建议？

  Option Explicit 
 
 Sub WorksheetLoop（）
 
 Dim wsA As Worksheet 
 Dim wbA As Workbook 
 Dim strTime As String 
 Dim strName As String 
 Dim strPath As String 
 Dim strFile As String 
 Dim strPathFile As String 
 Dim myFile As Variant 
 Dim WS_Count As Integer 
 Dim I As Integer 
 
'将WS_Count设置为等于活动工作簿中的工作表数。 
设置wbA = ActiveWorkbook 
 WS_Count = wbA.Worksheets.Count 
 strPath = wbA.Path 
 strTime =格式（Now（），yyyymmdd\_hhmm）
 
'获取活动工作簿文件夹，如果保存
 strPath = wbA.Path 
如果strPath =然后
 strPath = Application.DefaultFilePath 
 End If 
 strPath = strPath& \
 
'开始循环。 
对于I = 1到WS_Count 
 
'替换表单名称中的空格和句点
 strName =替换（wbA.Worksheets（I）.Name，，）
 strName =替换（strName，。，_）
 
'为savng文件创建默认名称
 strFile = strName& _& strTime& .pdf
 myFile = strPath& strFile 
 
 Debug.Print myFile 
 
'如果选择了文件夹，则导出为PDF 
如果myFile<> False然后
 wsA.ExportAsFixedFormat _ 
类型：= xlTypePDF，_ 
文件名：= myFile，_ 
质量：= xlQualityStandard，_ 
 IncludeDocProperties：= True ，_ 
 IgnorePrintAreas：= False，_ 
 OpenAfterPublish：= False 
'确认消息与文件信息
 MsgBoxPDF文件已创建：_ 
& vbCrLf _ 
& myFile 
结束如果
 
下一个I 
 
 End Sub 
  

解决方案

尝试改变如下：

 如果myFile< > False然后
 ActiveSheet.ExportAsFixedFormat _ 
类型：= xlTypePDF，_ 
 FileName：= myFile，_ 
质量：= xlQualityStandard，_ 
 IncludeDocProperties：= True ，_ 
 IgnorePrintAreas：= False，_ 
 OpenAfterPublish：= False 
  

然后尝试找到一种如何循环浏览表格，这是一个微不足道的VBA任务。
一般来说，在您的代码 wsA 未设置。因此，您收到错误。

I want to loop over all worksheets in a workbook and save them as individual pdfs in the same path as the workbook. The files are named after the worksheet name.

This code below works up until the "wsA.ExportAsFixedFort" line. The error message i get is:

Run-time error '91': Object variable or With block variable not set

But i cant figure out what the issue is...

Any suggestions?

Option Explicit

Sub WorksheetLoop()

Dim wsA As Worksheet
Dim wbA As Workbook
Dim strTime As String
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
Dim WS_Count As Integer
Dim I As Integer

' Set WS_Count equal to the number of worksheets in the active workbook.
Set wbA = ActiveWorkbook
WS_Count = wbA.Worksheets.Count
strPath = wbA.Path
strTime = Format(Now(), "yyyymmdd\_hhmm")

'get active workbook folder, if saved
strPath = wbA.Path
If strPath = "" Then
  strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"

' Begin the loop.
For I = 1 To WS_Count

    'replace spaces and periods in sheet name
    strName = Replace(wbA.Worksheets(I).Name, " ", "")
    strName = Replace(strName, ".", "_")

    'create default name for savng file
    strFile = strName & "_" & strTime & ".pdf"
    myFile = strPath & strFile

    Debug.Print myFile

    'export to PDF if a folder was selected
    If myFile <> "False" Then
        wsA.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=myFile, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
        'confirmation message with file info
        MsgBox "PDF file has been created: " _
          & vbCrLf _
          & myFile
    End If

Next I

End Sub

解决方案

Try to change like this:

If myFile <> "False" Then
            ActiveSheet.ExportAsFixedFormat _
                    Type:=xlTypePDF, _
                    FileName:=myFile, _
                    Quality:=xlQualityStandard, _
                    IncludeDocProperties:=True, _
                    IgnorePrintAreas:=False, _
                    OpenAfterPublish:=False

Then try to find a way how to loop through the sheets, its a trivial VBA task. In general, in your code wsA was not set. Thus, you got an error.

这篇关于将工作簿中的每个工作表保存为单独的pdf的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！