时间戳

首页 > 解决方案 > 在报表中使用 VBA 循环根据查询字段创建多个 PDF

vba - 在报表中使用 VBA 循环根据查询字段创建多个 PDF

问题描述

我一直在查看此站点上的其他已回答问题，以找到解决我的问题的方法，但没有任何运气，因此我决定发布此问题：

我有一个基于选择查询“R03 00 sel Rpt Office Report main”的访问报告“R03 01 Office Report main”。我的目标是使用查询“R03 00 sel Rpt Office Report main”中的字段 [Office] 为该字段中的每个唯一记录生成一个 pdf。下面是我一直在尝试使用的代码，但运气不佳。请帮忙！

这是选择查询 SQL：

SELECT tbl_Office_BLC_Data.Office, tbl_Office_BLC_Data.RM, tbl_Office_Data_Rpt.Clientonename, tbl_Office_BLC_Data.RAG_Status AS Rag_Stat, tbl_Office_Data_Rpt.Segment AS Producttwoname, tbl_Office_Data_Rpt.Revenue, tbl_Office_Data_Rpt.Net_Cont, tbl_Office_Data_Rpt.Variable, tbl_Office_Data_Rpt.Var_Cont, tbl_Office_Data_Rpt.[Revenue-1], tbl_Office_Data_Rpt.[Net_Cont-1], tbl_Office_Data_Rpt.[Variable-1], tbl_Office_Data_Rpt.[Var_Cont-1], tbl_Office_Data_Rpt.[Revenue-2], tbl_Office_Data_Rpt.[Net_Cont-2], tbl_Office_Data_Rpt.[Variable-2], tbl_Office_Data_Rpt.[Var_Cont-2], tbl_Office_Data_Rpt.Rev_Ex_Fx, tbl_Office_Data_Rpt.[Rev_Ex_Fx-1], tbl_Office_Data_Rpt.[Rev_Ex_Fx-2], tbl_Office_Data_Rpt.Net_Cont_Ex_Fx, tbl_Office_Data_Rpt.Net_Marg_Ex_Int, tbl_Office_Data_Rpt.[Net_Cont_Ex_Fx-1], tbl_Office_Data_Rpt.Var_Marg_Ex_Int, tbl_Office_Data_Rpt.[Net_Cont_Ex_Fx-2], tbl_Office_Data_Rpt.Net_Marg_Ex_Fx, tbl_Office_Data_Rpt.[Net_Marg_Ex_Fx-1], tbl_Office_Data_Rpt.[Net_Marg_Ex_Fx-2], tbl_Office_Data_Rpt.Variable_Ex_Int, tbl_Office_Data_Rpt.[Variable_Ex_Int-1], tbl_Office_Data_Rpt.[Variable_Ex_Int-2], tbl_Office_Data_Rpt.Var_Cont_Ex_Fx, tbl_Office_Data_Rpt.[Var_Cont_Ex_Fx-1], tbl_Office_Data_Rpt.[Var_Cont_Ex_Fx-2], tbl_Office_Data_Rpt.Var_Marg_Ex_Fx, tbl_Office_Data_Rpt.[Var_Marg_Ex_Fx-1], tbl_Office_Data_Rpt.[Var_Marg_Ex_Fx-2], tbl_Office_Data_Rpt.Var_Cont_Incr, tbl_Office_Data_Rpt.[Var_Cont_Incr-1], tbl_Office_Data_Rpt.[Var_Cont_Incr-2], tbl_Office_Data_Rpt.Var_Marg_Incr, tbl_Office_Data_Rpt.[Var_Marg_Incr-1], tbl_Office_Data_Rpt.[Var_Marg_Incr-2]
FROM tbl_Office_Data_Rpt INNER JOIN tbl_Office_BLC_Data ON tbl_Office_Data_Rpt.Clientonename = tbl_Office_BLC_Data.Clientonename;

谢谢！

这是VBA：

Public Function CurOID(Optional SetOID As Long = 0) As Long

    Static OID As Long

    If SetOID > 0 Then
        OID = SetOID
    End If

    CurOID = OID

End Function

Private Sub Command12_Click()

    Dim MyRs As DAO.Recordset
    Dim fileName As String, pathName As String, todayDate As String

    pathName = "C:\O Reports\"
    todayDate = Format(Date, "MMDDYYYY")
    Set MyRs = CurrentDb.OpenRecordset("SELECT Office FROM [R03 00 sel Rpt Office Report main]")

    With MyRs
        ' .MoveFirst -- unneeded after OpenRecordset()
        Do While Not .EOF
            fileName = "rpt_Office " & !Office & ".pdf"
            Call CurOID(!Office)
            DoCmd.OutputTo acOutputReport, "rpt_Office", acFormatPDF, pathName & fileName
            .MoveNext
        Loop
    End With

Call ShowMyMessageBoxOHRpt

End Sub

标签： vbams-access

解决方案

这样做的方法是循环不同办公室的记录集，使用办公室作为命令的WHERE参数打开报告.OpenReport，然后将此过滤后的报告输出为 PDF。

一些 VBA 可以帮助您：

Sub sReport2PDF()
    On Error GoTo E_Handle
    Dim db As DAO.Database
    Dim rsOffice As DAO.Recordset
    Dim strFolder As String
    Dim strSQL As String
    Set db = DBEngine(0)(0)
    strSQL = "SELECT DISTINCT Office " _
        & " FROM [R03 00 sel Rpt Office Report main] " _
        & " ORDER BY Office;"
    Set rsOffice = db.OpenRecordset(strSQL)
    If Not (rsOffice.BOF And rsOffice.EOF) Then
        strFolder = "J:\test-data\"
        Do
            DoCmd.OpenReport "rpt_Office", acViewPreview, , "Office='" & rsOffice!Office & "'", acHidden
            DoCmd.OutputTo acOutputReport, "rpt_Office", acFormatPDF, strFolder & "rpt_Office" & rsOffice!Office & ".pdf"
            DoCmd.Close acReport, "rpt_Office"
            rsOffice.MoveNext
        Loop Until rsOffice.EOF
    End If
sExit:
    On Error Resume Next
    rsOffice.Close
    Set rsOffice = Nothing
    Set db = Nothing
    Exit Sub
E_Handle:
    MsgBox Err.Description & vbCrLf & vbCrLf & "sReport2PDF", vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume sExit
End Sub

问候，

推荐阅读