将多个 Excel 工作表范围转换为 PDF VBA

英勇

下面的代码是获取 Col"E" 的状态如果它是 = Include 那么它对应的工作表范围将被转换为 PDF。

我在结束了尝试，但它不工作收到错误invalid procedure call or argument就行了

rng.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=saveLocation

我们将不胜感激您的帮助以解决问题。

Sub SelectSheets_Ranges()

  Dim sh As Worksheet, lastR As Long, rng As Range, arr, arrSplit, i As Long, k As Long
  
  Set sh = ActiveSheet
  lastR = sh.Range("C" & sh.Rows.Count).End(xlUp).Row
  
  ReDim arr(lastR - 1)
  For i = 6 To lastR
        If sh.Range("E" & i).Value = "Include" Then
            arr(k) = sh.Range("C" & i).Value & "|" & sh.Range("D" & i).Value: k = k + 1
        End If
  Next i
  ReDim Preserve arr(k - 1)
  For i = 0 To UBound(arr)
        arrSplit = Split(arr(i), "|")
        Set rng = Worksheets(arrSplit(0)).Range(arrSplit(1))


'Create and assign variables
Dim saveLocation As String

saveLocation = "C:\Users\marks\OneDrive\Documents\myPDFFile.pdf"

'Save a range as PDF
rng.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=saveLocation

  
  Next
End Sub

法尼杜鲁

请尝试下一个代码。它保存在ThisWorkbook路径中，将 pdf 文件命名为“myPDFFile_sheetName.pdf”。每个创建的文件都将在默认的 pdf 应用程序中打开。如果工作正常，您可以适当更改最后一个参数：


Sub SelectSheets_Ranges_ExpPdf()
  Dim sh As Worksheet, lastR As Long, rng As Range, arr, arrSplit, i As Long, k As Long
  
  Set sh = ActiveSheet
  lastR = sh.Range("C" & sh.Rows.Count).End(xlUp).Row
  
  ReDim arr(lastR - 1)
  For i = 6 To lastR
        If sh.Range("E" & i).Value = "Include" Then
            arr(k) = sh.Range("C" & i).Value & "|" & sh.Range("D" & i).Value: k = k + 1
        End If
  Next i
  If k > 0 Then
        ReDim Preserve arr(k - 1)
  Else
        MsgBox "No appropriate range (containing ""Include"") could be found...:exit sub"
  End If
  Dim boolHide As Boolean, boolProt As Boolean
  ActiveWorkbook.Unprotect "4321" 'in order to unprotect he workbook structure
  For i = 0 To UBound(arr)
        boolHide = False: boolProt = False
        arrSplit = Split(arr(i), "|")
        Set rng = Worksheets(arrSplit(0)).Range(arrSplit(1))
        
        If ActiveWorkbook.Sheets(arrSplit(0)).ProtectContents Then _
                ActiveWorkbook.Sheets(arrSplit(0)).Unprotect "4321": boolProt = True
                Debug.Print arrSplit(0)
        If ActiveWorkbook.Sheets(arrSplit(0)).Visible <> xlSheetVisible Then _
                ActiveWorkbook.Sheets(arrSplit(0)).Visible = xlSheetVisible: boolHide = True
        
        'Create and assign variables
        Dim saveLocation As String
        
        saveLocation = ThisWorkbook.Path & "\myPDFFile_" & arrSplit(0) & ".pdf"
        
        'Save a range as PDF
        rng.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
          saveLocation, Quality:=xlQualityStandard, IgnorePrintAreas:=False, OpenAfterPublish:=True
         If boolHide Then ActiveWorkbook.Sheets(arrSplit(0)).Visible = xlSheetHidden
         If boolProt Then ActiveWorkbook.Sheets(arrSplit(0)).Protect "4321"
  Next
  ActiveWorkbook.Protect "4321"
End Sub

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。