VBA编译错误:将Excel保存为PDF for Excel 2019的语法错误 [英] VBA Compile Error: Syntax Error for Saving Excel in PDF for Excel 2019
本文介绍了VBA编译错误:将Excel保存为PDF for Excel 2019的语法错误的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
使用Excel 2019运行以下代码时,出现编译错误:语法错误".它与较早版本的Excel兼容,但在2019年不兼容.我该如何解决此问题以及导致问题的原因?
I'm getting a "Compile Error: Syntax Error" when running the code below using Excel 2019. It works with the older version Excel, but not on 2019. How can I fix this and what's causing it?
错误行
ReportSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
ThisWorkbook.Path & "\" & Cell, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=True, OpenAfterPublish:=False
整个代码
Option Explicit
Private Sub CommandButton1_Click()
Dim MyFolder As String, MyFile As String
Dim StartTime As Double
Dim MinutesElapsed As String
Dim Filename As String
Dim Cell As String
Dim Counter As Long
If ThisWorkbook.Sheets("Sheet1").Range("C7").Value = vbNullString Then
MsgBox "Enter Tab Name"
Exit Sub
End If
StartTime = Timer
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.Title = "Select a Folder"
If .Show = True Then
MyFolder = .SelectedItems(1)
End If
If .SelectedItems.Count = 0 Then Exit Sub
Err.Clear
End With
'Turns settings off
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
MyFile = Dir(MyFolder & "\", vbReadOnly)
Do While MyFile <> ""
DoEvents
On Error GoTo 0
Workbooks.Open Filename:=MyFolder & "\" & MyFile, UpdateLinks:=False
Dim ReportSheet As Worksheet
Dim MySheet As String
Dim allColumns As Range
MySheet = ThisWorkbook.Sheets("Sheet1").Range("C7").Value
Set ReportSheet = Sheets(MySheet)
Set allColumns = ReportSheet.Columns("N:S")
allColumns.Hidden = True
With ReportSheet.PageSetup
.Zoom = False
.FitToPagesWide = 1 '.FitToPagesTall = 1
End With
Filename = ActiveWorkbook.Name
Cell = Replace(Filename, ".xlsx", ".PDF")
ReportSheet.Select
ReportSheet.PageSetup.Orientation = xlLandscape
ReportSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
ThisWorkbook.Path & "\" & Cell, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=True, OpenAfterPublish:=False
Counter = Counter + 1
0
Workbooks(MyFile).Close SaveChanges:=False
MyFile = Dir
Loop
'turns settings back on that you turned off before looping folders
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
Application.Calculation = xlCalculationManual
MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")
MsgBox "Successfully Converted " & Counter & " Files in " & MinutesElapsed & " minutes", vbInformation
End Sub
推荐答案
在任何Excel版本(AFAIK)中都不允许在使用行连续符_
时留空行
Leaving a blank line when you are using the line continuation _
character is not allowed in any Excel version (AFAIK)
给您带来麻烦的代码应该是:
The code giving you trouble should be:
ReportSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & Cell, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=True, OpenAfterPublish:=False
尝试正确缩进代码并删除多余的行.
Try indenting your code properly and remove the extra lines.
代码:
Option Explicit
Private Sub CommandButton1_Click()
Dim MyFolder As String, MyFile As String
Dim StartTime As Double
Dim MinutesElapsed As String
Dim Filename As String
Dim Cell As String
Dim Counter As Long
If ThisWorkbook.Sheets("Sheet1").Range("C7").Value = vbNullString Then
MsgBox "Enter Tab Name"
Exit Sub
End If
StartTime = Timer
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.Title = "Select a Folder"
If .Show = True Then
MyFolder = .SelectedItems(1)
End If
If .SelectedItems.Count = 0 Then Exit Sub
Err.Clear
End With
'Turns settings off
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
MyFile = Dir(MyFolder & "\", vbReadOnly)
Do While MyFile <> ""
DoEvents
On Error GoTo 0
Workbooks.Open Filename:=MyFolder & "\" & MyFile, UpdateLinks:=False
Dim ReportSheet As Worksheet
Dim MySheet As String
Dim allColumns As Range
MySheet = ThisWorkbook.Sheets("Sheet1").Range("C7").Value
Set ReportSheet = Sheets(MySheet)
Set allColumns = ReportSheet.Columns("N:S")
allColumns.Hidden = True
With ReportSheet.PageSetup
.Zoom = False
.FitToPagesWide = 1 '.FitToPagesTall = 1
End With
Filename = ActiveWorkbook.Name
Cell = Replace(Filename, ".xlsx", ".PDF")
ReportSheet.Select
ReportSheet.PageSetup.Orientation = xlLandscape
ReportSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & Cell, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=True, OpenAfterPublish:=False
Counter = Counter + 1
Workbooks(MyFile).Close SaveChanges:=False
MyFile = Dir
Loop
'turns settings back on that you turned off before looping folders
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
Application.Calculation = xlCalculationManual
MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")
MsgBox "Successfully Converted " & Counter & " Files in " & MinutesElapsed & " minutes", vbInformation
End Sub
旁注:未检查代码的其他部分
Side note: Didn't review other parts of your code
这篇关于VBA编译错误:将Excel保存为PDF for Excel 2019的语法错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文