I"m not sure what version of Excel you want. See below
acFormatType for Docmd.OutputTo (https://ss64.com/access/acformattype.html)Name
|
Description
|
acFormatHTML |
HTML format |
acFormatRTF |
Rich Text Format |
acFormatSNP |
Access report snapshot |
acFormatTXT |
Plain text |
acFormatDAP |
Data Access Page (2003 only) |
acFormatXLS |
Excel 2000-2003 |
acFormatXLSB |
Excel Binary Workbook |
acFormatXLSX |
Excel 2007 Workbook |
acFormat12XLSX |
Excel 2013 Workbook & later |
acFormatXPS |
XPS Format (Access 2007+) |
acFormatPDF |
.PDF (Access 2007+) |
Here are two versions of your code
Instead of "PDFFormat(*.pdf)", use acFormatPDF
Instead of "ExcelWorkbook(*.xlsx)", use acFormatXLS for Excel 2000-2003 or acFormatXLSX for Excel 2007 Workbook
Code:
Option Compare Database
Option Explicit
'-----------------------------------------------------------'
' Save Daily and Archive Reports to File '
' '
'-----------------------------------------------------------'
Function RunAllReports()
On Error GoTo RunAllReports_Err
Dim PDFTimeStamp As String
'==========================================='
' '
'Save Todays Priority Reports as PDF '
' '
'==========================================='
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9510", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Today\Priority Report - 9510.pdf", False, , , acExportQualityPrint
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9515", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Today\Priority Report - 9515.pdf", False, , , acExportQualityPrint
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9520", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Today\Priority Report - 9520.pdf", False, , , acExportQualityPrint
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9530", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Today\Priority Report - 9530.pdf", False, , , acExportQualityPrint
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9540", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Today\Priority Report - 9540.pdf", False, , , acExportQualityPrint
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9550", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Today\Priority Report - 9550.pdf", False, , , acExportQualityPrint
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9560", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Today\Priority Report - 9560.pdf", False, , , acExportQualityPrint
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9570", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Today\Priority Report - 9570.pdf", False, , , acExportQualityPrint
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9580", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Today\Priority Report - 9580.pdf", False, , , acExportQualityPrint
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9590", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Today\Priority Report - 9590.pdf", False, , , acExportQualityPrint
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9990", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Today\Priority Report - 9990.pdf", False, , , acExportQualityPrint
'==========================================='
' '
'Save Archive copy of Todays PDF '
' '
'==========================================='
PDFTimeStamp = Format(Now(), "YYYY-MMM-DD-HH-MM-SS")
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9510", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Archive\9510\Archive Priority Report_9510_" & PDFTimeStamp & ".pdf"
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9515", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Archive\9515\Archive Priority Report_9515_" & PDFTimeStamp & ".pdf"
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9520", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Archive\9520\Archive Priority Report_9520_" & PDFTimeStamp & ".pdf"
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9530", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Archive\9530\Archive Priority Report_9530_" & PDFTimeStamp & ".pdf"
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9540", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Archive\9540\Archive Priority Report_9540_" & PDFTimeStamp & ".pdf"
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9550", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Archive\9550\Archive Priority Report_9550_" & PDFTimeStamp & ".pdf"
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9560", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Archive\9560\Archive Priority Report_9560_" & PDFTimeStamp & ".pdf"
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9570", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Archive\9570\Archive Priority Report_9570_" & PDFTimeStamp & ".pdf"
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9580", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Archive\9580\Archive Priority Report_9580_" & PDFTimeStamp & ".pdf"
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9590", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Archive\9590\Archive Priority Report_9590_" & PDFTimeStamp & ".pdf"
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9990", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Archive\9990\Archive Priority Report_9990_" & PDFTimeStamp & ".pdf"
'==========================================='
' '
'Save All DC Archive file in Excel format '
' '
'==========================================='
'for Excel 2000-2003 (*.XLS)
DoCmd.OutputTo acOutputTable, "tbl999_READY_to_PRINT", acFormatXLS, "T:\Enterprise All\DCAPR Reports\Archive\Archive - Daily Priority Report All DCs " & PDFTimeStamp & ".xls"
' OR
'for Excel 2013 & later (*.XLSX)
'DoCmd.OutputTo acOutputTable, "tbl999_READY_to_PRINT", acFormatXLSX, "T:\Enterprise All\DCAPR Reports\Archive\Archive - Daily Priority Report All DCs " & PDFTimeStamp & ".xlsx"
RunAllReports_Exit:
Exit Function
RunAllReports_Err:
MsgBox Error$
Resume RunAllReports_Exit
End Function
This version has "DoEvents" to have a delay between commands
Code:
Option Compare Database
Option Explicit
'-----------------------------------------------------------'
' Save Daily and Archive Reports to File '
' '
'-----------------------------------------------------------'
Function RunAllReports()
On Error GoTo RunAllReports_Err
Dim PDFTimeStamp As String
'==========================================='
' '
'Save Todays Priority Reports as PDF '
' '
'==========================================='
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9510", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Today\Priority Report - 9510.pdf", False, , , acExportQualityPrint
DoEvents
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9515", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Today\Priority Report - 9515.pdf", False, , , acExportQualityPrint
DoEvents
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9520", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Today\Priority Report - 9520.pdf", False, , , acExportQualityPrint
DoEvents
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9530", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Today\Priority Report - 9530.pdf", False, , , acExportQualityPrint
DoEvents
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9540", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Today\Priority Report - 9540.pdf", False, , , acExportQualityPrint
DoEvents
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9550", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Today\Priority Report - 9550.pdf", False, , , acExportQualityPrint
DoEvents
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9560", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Today\Priority Report - 9560.pdf", False, , , acExportQualityPrint
DoEvents
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9570", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Today\Priority Report - 9570.pdf", False, , , acExportQualityPrint
DoEvents
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9580", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Today\Priority Report - 9580.pdf", False, , , acExportQualityPrint
DoEvents
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9590", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Today\Priority Report - 9590.pdf", False, , , acExportQualityPrint
DoEvents
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9990", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Today\Priority Report - 9990.pdf", False, , , acExportQualityPrint
'==========================================='
' '
'Save Archive copy of Todays PDF '
' '
'==========================================='
PDFTimeStamp = Format(Now(), "YYYY-MMM-DD-HH-MM-SS")
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9510", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Archive\9510\Archive Priority Report_9510_" & PDFTimeStamp & ".pdf"
DoEvents
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9515", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Archive\9515\Archive Priority Report_9515_" & PDFTimeStamp & ".pdf"
DoEvents
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9520", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Archive\9520\Archive Priority Report_9520_" & PDFTimeStamp & ".pdf"
DoEvents
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9530", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Archive\9530\Archive Priority Report_9530_" & PDFTimeStamp & ".pdf"
DoEvents
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9540", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Archive\9540\Archive Priority Report_9540_" & PDFTimeStamp & ".pdf"
DoEvents
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9550", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Archive\9550\Archive Priority Report_9550_" & PDFTimeStamp & ".pdf"
DoEvents
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9560", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Archive\9560\Archive Priority Report_9560_" & PDFTimeStamp & ".pdf"
DoEvents
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9570", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Archive\9570\Archive Priority Report_9570_" & PDFTimeStamp & ".pdf"
DoEvents
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9580", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Archive\9580\Archive Priority Report_9580_" & PDFTimeStamp & ".pdf"
DoEvents
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9590", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Archive\9590\Archive Priority Report_9590_" & PDFTimeStamp & ".pdf"
DoEvents
DoCmd.OutputTo acOutputReport, "rpt_DCAPR_Report_9990", acFormatPDF, "T:\Enterprise All\DCAPR Reports\Archive\9990\Archive Priority Report_9990_" & PDFTimeStamp & ".pdf"
DoEvents
'==========================================='
' '
'Save All DC Archive file in Excel format '
' '
'==========================================='
'for Excel 2000-2003 (*.XLS)
DoCmd.OutputTo acOutputTable, "tbl999_READY_to_PRINT", acFormatXLS, "T:\Enterprise All\DCAPR Reports\Archive\Archive - Daily Priority Report All DCs " & PDFTimeStamp & ".xls"
' OR
'for Excel 2013 & later (*.XLSX)
'DoCmd.OutputTo acOutputTable, "tbl999_READY_to_PRINT", acFormatXLSX, "T:\Enterprise All\DCAPR Reports\Archive\Archive - Daily Priority Report All DCs " & PDFTimeStamp & ".xlsx"
RunAllReports_Exit:
Exit Function
RunAllReports_Err:
MsgBox Error$
Resume RunAllReports_Exit
End Function