Excel VBA:运行时错误5 - 无效的过程调用或参数
问题描述:
我在Excel中有一个宏,它循环选项卡并将选项卡保存为PDF文件。Excel VBA:运行时错误5 - 无效的过程调用或参数
宏观部分的工作,它创建了几个PDF文件,但随后停止并抛出这个错误:
Run Time Error 5 - Invalid Procedure Call or Argument
这里是我的代码:
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 rng As Range
' Prevents screen refreshing.
Application.ScreenUpdating = False
Set wbA = ActiveWorkbook
strPath = wbA.Path
strTime = Format(Now(), "yyyymmdd")
'get active workbook folder, if saved
strPath = wbA.Path
If strPath = "" Then
strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"
' Begin the loop.
For Each wsA In ActiveWorkbook.Worksheets
'replace spaces and periods in sheet name
strName = Replace(wsA.Name, " ", "")
strName = Replace(strName, ".", "_")
If strName = "Macro" Then
MsgBox "That's all folks! :)"
Exit Sub
End If
If strName = "TOUCHPOINTS" Then
strName = "Touchpoints by markets"
End If
If strName = "VIDEOHOURS" Then
strName = "Viewing Hours by markets"
End If
If strName = "TARGETS" Then
strName = "Shares by markets"
End If
If strName = "SHARESCHANNELS" Then
strName = "IGNORE ME"
End If
If strName = "TOP10PREMIERES" Then
strName = "Top 10 Premieres by markets"
End If
If strName = "SHARETREND" Then
strName = "Share trends last 13 months"
End If
If strName = "COMPETITION" Then
strName = "Share overview international media companies"
End If
If strName = "COMPETITIONSHARETREND" Then
strName = "Share trends factual competitors last 13 months"
End If
If strName = "PUT" Then
strName = "PUT level"
End If
If strName = "CHANNELRANKER" Then
strName = "Top 20 Channels by Market"
End If
'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
End If
Next wsA
' Enables screen refreshing.
Application.ScreenUpdating = True
End Sub
答
可能错误是在这里: If myFile <> "False" Then
我想它应该是这样 - >If myFile Then
或If len(myFile) >8 Then
但是,代码看起来可行。刚刚尝试与选择案例来重建它,它看起来的方式更好,速度稍快:
Select Case strName
Case "Macro"
MsgBox "That's all"
Exit Sub
Case "TOUCHPOINTS"
strName = "Touchpoints by markets"
Case Else
Debug.Print "I don't know -> "; strName
End Select
也许你会发现错误,则。
+1
@ YowE3K - 它看起来像我得到了三个正确的1:D – Vityata
那么你在哪里得到错误? :) – Vityata
^^和'Debug.Print myFile'打印的最后一件东西是什么? – YowE3K