在报表中使用VBA循环根据查询字段创建多个PDF
在报表中使用VBA循环根据查询字段创建多个PDF
提问于 2020-05-14 22:58:24
我一直在审查这个网站上的其他回答问题，以找到我的问题的解决方案，但没有任何运气，所以我决定发布这个问题：
我有一个基于select查询"R03 00sel report main“的Access报告”RPT01Office Report main“。我的目标是在查询"R03 00sel Rpt Office Report main“中使用field Office，为该字段中的每个唯一记录生成一个pdf。下面是我一直在尝试使用的代码，但没有太多运气。请帮帮我！
下面是Select Query 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 FunctionPrivate 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回答 1
Stack Overflow用户
发布于 2020-05-15 01:27:33
为此，方法是循环不同办公室的记录集，使用办公室作为.OpenReport命令的WHERE参数打开报告，然后将此过滤后的报告输出为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致以敬意，
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/61800578
复制相关文章
相似问题
腾讯云开发者
