时间戳

首页 > 解决方案 > 如何创建一个按钮来生成 pdf 文件并在另一个工作表上更新记录

excel - 如何创建一个按钮来生成 pdf 文件并在另一个工作表上更新记录

问题描述

我想在 sheet1 上创建一个按钮以将工作表 1“模板”打印为 pdf，并通过填写下一个空行来更新 sheet2“记录”上的记录，但我的公式中似乎存在错误......请帮助

Private Sub CommandButton1_Click()

    Dim invoiceRng As Range
    'Setting range to be printed
    Dim FPath As String
    Dim FName As String
    Dim Amendment As String
    Dim eRow As String
    Set invoiceRng = Range("A1:F61")
    FPath = "D:\"
    FName = Sheets("Template").Range("F3")
    Amendment = Sheets("Template").Range("F4")
    'setting the fulli qualified name. The resultent pdf will be saved where the main file exists.
    invoiceRng.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=FPath & FName & "-" & Amendment & ".pdf", _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=True, _
    OpenAfterPublish:=False

    'Find last row
    eRow = Sheets("Record").Cells(Rows.Count, 1).End(x1Up).Offset(1, 0).Row
    'Copy the data
    Sheets("Template").Range("B8").Copy
    'Activate the destination worksheet
    Sheets("Record").Activate
    'Select the target range
    Range("B", eRow).Select
    'Paste in the target desitnation
    ActiveSheet.Paste

    'confirmation message with file info
    MsgBox "PDF file has been created " _

End Sub

标签： excelvba

解决方案

将范围导出为 PDF

  • 如果您编写完全合格的代码，则可以使用任何工作表上的命令按钮运行它。

标准模块 例如Module1

Option Explicit

Sub exportToPDF()

    Const FPath As String = "D:\"
    ' Define workbook.
    Dim wb As Workbook
    Set wb = ThisWorkbook ' The workbook containing this code.
    
    ' Source Worksheet
    
    ' Define Source Worksheet.
    Dim src As Worksheet
    Set src = wb.Worksheets("Template")
    ' Define Invoice Range.
    Dim invoiceRng As Range
    Set invoiceRng = src.Range("A1:F61")
    ' Define File Name.
    Dim FName As String
    FName = src.Range("F3")
    ' Define Amendment.
    Dim Amendment As String
    Amendment = src.Range("F4")
    ' Export to PDF
    invoiceRng.ExportAsFixedFormat _
      Type:=xlTypePDF, _
      Filename:=FPath & FName & "-" & Amendment & ".pdf", _
      Quality:=xlQualityStandard, _
      IncludeDocProperties:=True, _
      IgnorePrintAreas:=True, _
      OpenAfterPublish:=True

    ' Target Worksheet.
    
    ' Define Target Worksheet.
    Dim tgt As Worksheet
    Set tgt = wb.Worksheets("Record")
    ' Find First Empty Row.
    Dim eRow As Long
    eRow = tgt.Cells(tgt.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
    ' Copy value.
    tgt.Cells(eRow, "B").Value = src.Range("B8").Value
    ' Or:
    'tgt.Range("B" & eRow).Value = srcRange("B8").Value
    
    ' Confirmation message with file info
    
    MsgBox "PDF file has been created "

End Sub

表模块例如Template

Option Explicit

Private Sub CommandButton1_Click()
    exportToPDF
End Sub

推荐阅读