vba - 使用 Excel VBA 导出后命名 PDF

Question

我有一个从工作表中获取数据并填充表单的代码。在数据中有重复的条目。 [引用资料图]

Dim i As Long
Dim dataWS As Worksheet, formWS As Worksheet
Dim thisFile As Range, destRange As Range
Dim thisFile2 As Range, destRange2 As Range

FolderPath = "C:\Users\Lenovo\Documents\PAF_Output\"

MkDir FolderPath

Set dataWS = Sheets("Data")
Set formWS = Sheets("Form")
For i = 2 To 5

Set thisFile2 = dataWS.Range("A" & i) 
Set destRange2 = formWS.Range("B4:I4")
thisFile2.Copy destRange2

Set thisFile = dataWS.Range("B" & i) 
Set destRange = formWS.Range("O4:Q4")
thisFile.Copy destRange

Sheets(Array("Form")).Select
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, FileName:=FolderPath & thisFile2.Value & ".pdf", _
openafterpublish:=False, ignoreprintareas:=False


Next i

End Sub

Data

正如你所看到的

FileName:=FolderPath & thisFile2.Value & ".pdf"

这些文件以 A 列中的值命名。尽管如此，在重复条目的情况下，excel 将用第二个文件覆盖第一个文件。我现在要做的是创建一个名称，将 A 列中的值的名称和 B 列中的到达日期值结合起来。像这样......

FileName:=FolderPath & thisFile2.Value & thisFile.Value & ".pdf"

不过，这给了我一个错误。有人能帮助我吗？

Answer 1

您需要格式化您的日期，这样斜线 (/) 就不会出现，因为文件名不能包含这些字符，如下所示，还值得一提的是，您正在从单个单元格复制并粘贴到一个范围中，我怀疑这是您想要达到..:

Dim i As Long
Dim dataWS As Worksheet: Set dataWS = Sheets("Data")
Dim formWS As Worksheet: Set formWS = Sheets("Form")
Dim thisFile As Range, destRange As Range
Dim thisFile2 As Range, destRange2 As Range

FolderPath = "C:\Users\Lenovo\Documents\PAF_Output\"

MkDir FolderPath

For i = 2 To 5
    Set thisFile2 = dataWS.Range("A" & i)
    Set destRange2 = formWS.Range("B4:I4")
    thisFile2.Copy destRange2

    Set thisFile = dataWS.Range("B" & i)
    Set destRange = formWS.Range("O4:Q4")
    thisFile.Copy destRange

    formWS.ExportAsFixedFormat _
    Type:=xlTypePDF, fileName:=FolderPath & thisFile2.Value & Format(thisFile.Value, "MM-dd-yyyy") & ".pdf", openafterpublish:=False, ignoreprintareas:=False
Next i
End Sub

更新:

要进一步整理代码并删除不需要的语句，例如复制和粘贴，请参见下文:

Sub test()
Dim i As Long
Dim dataWS As Worksheet: Set dataWS = Sheets("Data")
Dim formWS As Worksheet: Set formWS = Sheets("Form")

FolderPath = "C:\Users\Lenovo\Documents\PAF_Output\"

If Dir(FolderPath, vbDirectory) = "" Then MkDir FolderPath
'above if the folder doesn't exist then create it

For i = 2 To 5
    formWS.Range("B4:I4") = dataWS.Range("A" & i)
    formWS.Range("O4:Q4") = dataWS.Range("B" & i)
    'above transfer the values from one range to another without copying

    formWS.ExportAsFixedFormat _
    Type:=xlTypePDF, fileName:=FolderPath & thisFile2.Value & Format(thisFile.Value, "MM-dd-yyyy") & ".pdf", openafterpublish:=False, ignoreprintareas:=False
Next i
End Sub