By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,316 Members | 1,301 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,316 IT Pros & Developers. It's quick & easy.

Output a report to PDF format to save or send as email

P: 12
I am in the process of converting macros in Access 2000 databases to VBA code for use in Access 2007. The macros open reports which are formatted to go to the Adobe PDF printer using the print command. The converted macro VBA code only opens the report in normal view and the user has to go to the Office Button and save the file as a PDF file. The original macro automated this process for 45 consecutive reports. I need to modify or add to the new VBA code so that it automates this process by outputing the file to PDF format (without opening the file) and prompts the user for the location to save the PDF file. I need assistance in writing the VBA code to automate the output of the reports so that all the user has to respond to is a prompt for the file location to save the PDF file (as before with the macro). The following code is from the macro to VBA conversion process -

DoCmd.OpenReport "rptmyreport", acViewNormal, " ", " "

The report (each report) opens and the user has to go to the Office button, Save As PDF, specify the location, then close the report. This is overwhelming for someone do this process for 45 reports at a time.

I think I have part of the code necessary to output to a PDF file; see below -

DoCmd.OutputTo acReport, "rptmyreport", acFormatPDF, " ", "False"

Is this correct and is this the only line of code that I need? I am using Access 2007 with the Office SP2. I am just learning VBA, mostly by example, and I would appreciate any help or guidance to a tutorial site. Thank you.

Apr 26 '10 #1
Share this Question
Share on Google+
17 Replies

Expert 100+
P: 931
What you are asking for is possible, and actually not too complicated. It's something that I've implemented in a couple of my projects. Give me a little time and I'll dig the code up for you.

Apr 27 '10 #2

Expert 100+
P: 931
The basic code to do this is

Expand|Select|Wrap|Line Numbers
  1. Dim fd As FileDialog
  2. Dim strFolder As String
  4. Set fd = Application.FileDialog(msoFileDialogFolderPicker)
  6. If fd.Show = -1 Then
  7.     strFolder = fd.SelectedItems.Item(1)
  8. Else
  9.     Exit Sub
  10. End If
  12. DoCmd.OutputTo acOutputReport, "rptTrackingReport", acFormatPDF, strFolder & "\rptTrackingReport.PDF"
  14. Set fd = Nothing

This brings up a dialog box that allows you to pick a folder location. That gets stored in "strFolder".

You will need to go to Tools > References and check off Microsoft Office 12.0 Object Library to use the FileDialog object.

I was doing this for a single tracking report in my database, but if you're doing it for 45 consecutive files, you'll probably want to find a way to put the DoCmd inside a loop and modify the "\rptTrackingReport.PDF" parameter so that each PDF comes out with a name corresponding to the correct report.

Apr 27 '10 #3

P: 12
Thank you, Pat, for the code. I think this is just what I was looking for. I do have a question about the If...Else statement. Does the If..Else statement determine whether or not the file dialog box displays and also does it only have to be stated once?

I will try to write the loop for the DoCmd; however, since I am still new to this I may take the repetitive but easy way out and write the DoCmd statement for each report. Is there an online tutorial or good VBA for Access reference you can recommend that could assist me? You've been a tremendous help. Thanks again.

Apr 28 '10 #4

Expert 100+
P: 931

The purpose of the If...Then is to determine whether the user hit the "Save" or "Cancel" button. So, if fd.Show = -1 then the user hit Save and the folder path gets stored in the string variable; I am having Access exit the subroutine if Cancel is hit, but you might have other default behavior that you want to occur.

There are probably many ways you can loop through the reports. One thing you can do is to give the reports meaningful names that follow some kind of pattern, such as rptTracking1, rptTracking2, rptTracking3, etc. Then you could setup a simple For loop as follows:

Expand|Select|Wrap|Line Numbers
  1. Dim j as Integer
  3. For j = 1 to 45
  4.      DoCmd.OutputTo acOutputReport, "rptTrackingReport", acFormatPDF, strFolder & "\rptTracking" & CStr(j) & ".PDF"
  5. Next j

Here \rptTracking & CStr(j) results in rptTracking1, rptTracking2, and so on up to rptTracking45 (the CStr function simply converts an integer to a string, and the code will work just using "j", but I like to use CStr(j) for consistency).

Something else I would point out is that if these reports are all laid out in the same manner, but just contain different data, you make one just one report and then filter the record source for the report each time you run it, setting the filter in such a manner to give you only the records that are relevant for that particular report. In this way you can avoid explicitly keeping 45 reports in your database that all do basically the same thing. This may not be your situation but I point it out just in case it is.

For VBA references, I don't have any in mind specifically. Perhaps someone else on the forum here could guide you in that respect.

Apr 28 '10 #5

P: 12

I tried the solution you proposed and it doesn't work in this environment with all of the other processes the macro is doing. I did put together another command in VBA that will bring up the dialog box where the user can change the folder to save the specified report. The save name, though, is the same as the object name.

DoCmd.OutputTo acOutputReport, "1 rptStaffingTable", acFormatPDF, , , , , acExportQualityPrint

In the original Access 2000 macro that I converted the print command picked up the report's caption and entered it in the name box in the dialog box. For the above report, the file should be saved as StaffingTableYYPP as that is the caption. If I enter a filename after the acFormatPDF argument in the above code, the report is saved in the default location; the user doesn't have the opportunity to change folders. Do you know how I can capture the report caption property to have it displayed in the file name box when the OutputTo dialog box is displayed? I feel as if I'm so close to a solution and I am under a deadline to convert the macros and have them work as close to the way they did in 2000 (you know, without much user re-training!). I appreciate all of your help.

Apr 28 '10 #6

Expert 100+
P: 931
Macros are made with ease of use in mind. When you are trying to convert them to Visual Basic code, a little more work is involved - but not much. What you want to do is about ten lines of code.

The code that I provided you with previously is what will allow you to save the report somewhere other than the default location. This piece of code that you posted:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OutputTo acOutputReport, "1 rptStaffingTable", acFormatPDF, , , , , acExportQualityPrint not going to do what you want because you haven't even supplied a path and filename. In my version of this statement:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OutputTo acOutputReport, "rptTrackingReport", acFormatPDF, strFolder & "\rptTracking" & CStr(j) & ".PDF"

..."strFolder" provides the path to the folder that the user selected when fd.Show brought up a dialog box. This is then built on using the concatenation operator "&", to add a filename to the folder path. In your case, it will look like

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OutputTo acOutputReport, "1 rptStaffingTable", acFormatPDF, strFolder & "\StaffingTableYYPP.PDF"

But this is dependent upon picking out the folder location first and storing it in strFolder, as I indicated previously.

Getting the report caption can be done by using Report_ReportName.Caption. In your case, you have a space in your report name: Report_[1 rptStaffingTable].Caption. If having the report caption be the filename is your goal, then it's a matter of writing

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OutputTo acOutputReport, "1 rptStaffingTable", acFormatPDF, strFolder & "\" & Report_[1 rptStaffingTable].Caption & ".PDF"

No need to insert the caption into folder picker dialog box. Please let me know how this works out for you. If it is the concatenation operator "&" that is confusing you here, then let me know and I can explain a little bit about how combining strings of text works.

Apr 28 '10 #7

P: 12

The variable to capture the report caption gave me a syntax error (expected end of statement) until I changed it to this

strFolder & "\" & Report.[1 rptStaffingTable].Caption & ".PDF"

Also I have to have Exit Function instead of Exit Sub in the If ... Else clause.

But the main issue now is that the code halts with the error "Object required" when it gets to the DoCmd.OutputTo statement.

Again, you've provided so much good information, but I must bother you again about exactly what I need. I don't want to automatically save each report to a designated folder under the exact caption name each time. The option to choose the folder location should occur at the same time the caption is displayed in the file name box so the user can check and have the option to change either or both depending on the process that the report is being run for. I need the folder picker routine to choose the location and I need to capture the report caption so that the file can be correctly named for that location. Please let me know if this is confusing. I'm trying to get the process to run as it did with Access 2000 macros using arguments from the OpenReport action that specified that the report could be opened to print as a PDF file. Access 2007 would require that the user bring up the Save As command from the Office Button and enter both the file location and the name of the report. This was all in one macro for 45 reports. Will I need to break the process down and create a macro for each report? Is there some way I can capture individual report information for each instance of the OutputTo command or is there another command or way to open a report to have it saved as a PDF file in a specific (not default) location? Thanks again for your help.

Apr 28 '10 #8

Expert 100+
P: 931
Can you please post all the code you've got so far so that we are on the same page?

Apr 29 '10 #9

P: 12

Following is the code. It includes yours added to the code generated from converting the macro in Access 2007.

' mac_1_StaffingTable_Update_and_Print_to_PDF_MT
Function mac_1_StaffingTable_Update_and_Print_to_PDF_MT()
On Error GoTo mac_1_StaffingTable_Update_and_Print_to_PDF_MT_Err
Dim fd As FileDialog
Dim strFolder As String

Set fd = Application.FileDialog(msoFileDialogFolderPicker)

If fd.Show = -1 Then
strFolder = fd.SelectedItems.Item(1)
Exit Function
End If

DoCmd.SetWarnings False
' qryStaffingTable2 make table tblStaffingTable_Mt
' DoCmd.OpenQuery "qryStaffingTable2 make table tblStaffingTable_Mt", acViewNormal, acEdit
' DoCmd.Close acQuery, "qryStaffingTable2 make table tblStaffingTable_Mt"
' 1 rptStaffingTable save to g:\workfile\pdfreports\staffingtable\StaffingTable YYPP.PDF
' DoCmd.OpenReport "1 rptStaffingTable", acViewNormal, "", ""
' Var1 = reportobject.Caption
' DoCmd.OutputTo acOutputReport, "1 rptStaffingTable", acFormatPDF, , , , , acExportQualityPrint
DoCmd.OutputTo acOutputReport, "1 rptStaffingTable", acFormatPDF, strFolder & "\" & Report.[1 rptStaffingTable].Caption & ".PDF", , , , acExportQualityPrint


MsgBox "Complete Mail to Danielle", vbOKOnly, "Staffing Table "

Set fd = Nothing

mac_1_StaffingTable_Update_and_Print_to_PDF_MT_Exi t:
Exit Function

mac_1_StaffingTable_Update_and_Print_to_PDF_MT_Err :
MsgBox Error$
Resume mac_1_StaffingTable_Update_and_Print_to_PDF_MT_Exi t

End Function

As you can see there were other things that are supposed to happen before saving the report to PDF format. This isn't the macro with the 45 reports of course. I wanted to try it on one report to see what the prompts and output look like.

Apr 29 '10 #10

P: 12

The following is the (extensive) vba code from the converted macro that prints several reports as PDFs; the old 2000 macro prompted the user for a folder location, entered the report caption in the file name box, and allowed the user to change it when necessary. The VBA code just opens the report and the user has to go to the Office Button to save the file as a PDF where the user can enter location and file name parameters. There is a lot of code. I was looking to modify the one DoCmd.OpenReport ... line for each report to code which would allow me to print to the Adobe PDF printer or save the file as a PDF.

' mcrBiweeklyReports_PDF_inForm
Function mcrBiweeklyReports_PDF_inForm()
On Error GoTo mcrBiweeklyReports_PDF_inForm_Err

DoCmd.Echo True, ""
DoCmd.SetWarnings False
' Email rptACSD-HQemployeesOnLeaveFullPayperiod_for_transit subsidy to g:\workfile\shared\ta_staff\bi_rpts\TempRoster\ACS D-OnLeave_pp.pdf email to Jim Saccamondo
DoCmd.OpenReport "rptACSD-HQemployeesOnLeaveFullPayperiod_for_transit subsidy", acViewNormal, "", ""
' rptWorkersComp-LWOP to \\sharedncs_hrd_server\hrd\\workfile\shared\ta_sta ff\bi_rpts\TempRoster\workerscomp-lwop.pdf
DoCmd.OpenReport "rptWorkersComp-LWOP", acViewNormal, "", ""
' rptMT_NTE_Tickle to PDF \\sharedncs_hrd_servers\hrd\workfile\SHARED\EMPLOY \VICKI\MT_Tickles\MT_NTE_TICKLE.PDF for Vicki Dabbs
DoCmd.OpenReport "rptMT_NTE_Tickle", acViewNormal, "", ""
' qryPromotionsPP_Clean table
DoCmd.OpenQuery "qryPromotionsPP_Clean table", acViewNormal, acEdit
' Import \\prodncs_hrd_server\hrd\hrd\focus\database\promsa l.txt
DoCmd.TransferText acImportFixed, "Promsal Import Specification", "tblPromsal_NFCdownload", "\\prodncs_hrd_server\hrd\hrd\focus\database\proms al.txt", False, ""
' rptPromotionsPP_CheckSalary print PDF to temproster then SCRIPT to m:\workfile\shared\ppsb\htm_reports\PromotionsPP_C heckSalary.pdf for Jodee/Julie
DoCmd.OpenReport "rptPromotionsPP_CheckSalary", acViewNormal, "", ""
' qryABCO_Clean_tblABCO_fromNFCdownload_ABCOdwn
DoCmd.OpenQuery "qryABCO_Clean_tblABCO_fromNFCdownload_ABCOdwn ", acViewNormal, acEdit
' import \\prodncs_hrd_server\hrd\hrd\focus\database\ABCOdw n.xls into tblABCO_fromNFCdownload_ABCOdwn
DoCmd.TransferSpreadsheet acImport, 8, "tblABCO_fromNFCdownload_ABCOdwn", "\\prodncs_hrd_server\hrd\hrd\focus\database\ABCOd wn.xls", True, ""
' qryABCO_outrcvbl_paypersdata to \\sharedncs_hrd_server\hrd\workfile\shared\ta_staf f\bi_rpts\TempRoster\ABCO.xls
DoCmd.TransferSpreadsheet acExport, 8, "qryABCO_outrcvbl_paypersdata", "\\sharedncs_hrd_server\hrd\workfile\shared\ta_sta ff\bi_rpts\TempRoster\ABCO.xls", True, ""
' rptUnion-Improper Union Dues print PDF to temproster then SCRIPT to m:\workfile\shared\Labor\access\pdfreports\Imprope r_Dues.pdf for Labor Branch
DoCmd.OpenReport "rptUnion-Improper Union Dues", acViewNormal, "", ""
' rptUnionded for PDFWriter print PDF to temproster then SCRIPT to m:\workfile\shared\Labor\access\pdfreports\Unionde d.pdf for Labor Branch
DoCmd.OpenReport "rptUnionded for PDFWriter", acViewNormal, "", ""
' rptsephlth for PDFWriter print PDF to temproster then SCRIPT to m:\workfile\shared\Labor\access\pdfreports\sephlth .pdf for Labor Branch
DoCmd.OpenReport "rptsephlth for PDFWriter", acViewNormal, "", ""
' rptEmployeeRoster_RetirementType_FIN_w/SALARY print PDF to temproster then SCRIPT to \\prodncs_hrd_server\hrd\hrd\FOCUS\DATABASE\FIN\FI N_Roster\EmpRoster_RetireType.pdf
DoCmd.OpenReport "rptEmployeeRoster_RetirementType_FIN_w/SALARY", acViewNormal, "\\prodncs_hrd_server\hrd\hrd\FOCUS\DATABASE\FIN\F IN_Roster\EmpRoster_RetireType.pdf", ""
' rptDetMinCareer \\sharedncs_hrd_server\hrd\\workfile\shared\ta_sta ff\bi_rpts\TempRoster\rptDetMinCareer
DoCmd.OpenReport "rptDetMinCareer", acViewNormal, "", ""
' rptDetMinIntPerm
DoCmd.OpenReport "rptDetMinIntPerm", acViewNormal, "", ""
' rptDetMinIntTemp
DoCmd.OpenReport "rptDetMinIntTemp", acViewNormal, "", ""
' rptDetMinPerm
DoCmd.OpenReport "rptDetMinPerm", acViewNormal, "", ""
' rptDetMinPTPerm
DoCmd.OpenReport "rptDetMinPTPerm", acViewNormal, "", ""
' qryCompTotals_Clean_tblCOMPTOT_download from NFC
DoCmd.OpenQuery "qryCompTotals_Clean_tblCOMPTOT_download from NFC", acViewNormal, acEdit
' import \\prodncs_hrd_server\hrd\hrd\focus\database\compto t.txt spec=COMPTOT2 EXTENDED Import Specification into tblCOMPTOT_download from NFC
DoCmd.TransferText acImportFixed, "COMPTOT2 EXTENDED Import Specification", "tblCOMPTOT_download from NFC", "\\prodncs_hrd_server\hrd\hrd\focus\database\compt ot.txt", False, ""
' rptSeps2DoC prints PDF to \\sharedncs_hrd_server\hrd\\workfile\shared\ta_sta ff\bi_rpts\TempRoster\Sep2DoC.pdf
DoCmd.OpenReport "rptSeps2DoC", acViewNormal, "", ""
' rptRNOinfo save to PDF in temproster\RNO_Info.pdf
DoCmd.OpenReport "rptRNOinfo", acViewNormal, "", ""
DoCmd.Close acTable, "rptrnoinfo"
' rptSpec_Emp_Pgm 40 not in 141710 for PDFWriter saves to PDF in TempRoster\spec40.pdf
DoCmd.OpenReport "rptSpec_Emp_Pgm 40 not in 141710 for PDFWriter", acViewNormal, "", ""
DoCmd.Close acReport, "rptSpec_Emp_Pgm 40 not in 141710 for PDFWriter"
' rptBUS_HRD<>8888_PDFWriter saves to PDF in TempRoster\hrd_busNOT888.PDF
DoCmd.OpenReport "rptBUS_HRD<>8888_PDFWriter", acViewNormal, "", ""
DoCmd.Close acReport, "rptBUS_HRD<>8888_PDFWriter"
' qrytblLvLiDown_clean table
DoCmd.OpenQuery "qrytblLvLiDown_clean table", acViewNormal, acEdit
' import \\prodncs_hrd_server\hrd\hrd\focus\database\lvlido wn.txt into tblLvLiDown using LvLiDown Import Specs
DoCmd.TransferText acImportFixed, "Lvlidown Import Specification", "tblLvLiDown", "\\prodncs_hrd_server\hrd\hrd\focus\database\lvlid own.txt", False, ""
' rptlvliDown with restored leave PDFWriter print PDF to \workfile\shared\TA_Staff\bi_rpts\TempRoster\leave liability.pdf
DoCmd.OpenReport "rptlvliDown with restored leave PDFWriter", acViewNormal, "", ""
DoCmd.Close acReport, "rptlvliDown with restored leave PDFWriter"
' qryGeoTable1_PPSB - makes tblGeoTable_PPSB
DoCmd.OpenQuery "qryGeoTable1_PPSB", acViewNormal, acEdit
' qryGeoTable3_PPSB_city-no-county - - appends cities with no counties to tblGeoTable_PPSB see Adamana, Arizona
DoCmd.OpenQuery "qryGeoTable3_PPSB_city-no-county", acViewNormal, acEdit
' rptGeoTable_PPSB print to PDF in ta_staff\bi_rpts\TempRoster\GeoTable.pdf
DoCmd.OpenReport "rptGeoTable_PPSB", acViewNormal, "", ""
' rptMRList saves to PDF in TempRoster\mrlist.pdf
DoCmd.OpenReport "rptMRList", acViewNormal, "", ""
DoCmd.Close acReport, "rptMRList"
' rptNtelist saves to PDF in TempRoster\ntelist.pdf
DoCmd.OpenReport "rptNtelist", acViewNormal, "", ""
DoCmd.Close acReport, "rptNtelist"
' rptJobData saves to PDF in TempRoster\jobdata.pdf
DoCmd.OpenReport "rptJobData", acViewNormal, "", ""
DoCmd.Close acReport, "rptJobData"
' rptSeparation Report HQ for PDFWriter saves to PDF in TempRoster\separation_hq.pdf
DoCmd.OpenReport "rptSeparation Report HQ for PDFWriter", acViewNormal, "", ""
DoCmd.Close acReport, "rptSeparation Report HQ for PDFWriter"
' rptSecurityList for Guards for PDFWriter saves to PDF in TempRoster\securitylist.pdf
DoCmd.OpenReport "rptSecurityList for Guards for PDFWriter", acViewNormal, "", ""
DoCmd.Close acReport, "rptSecurityList for Guards for PDFWriter"
' rptPersdata_Dir01_PDFWriter saves to PDF in TempRoster\Persdata_DIR01.pdf
DoCmd.OpenReport "rptPersdata_Dir01_PDFWriter", acViewNormal, "", ""
DoCmd.Close acReport, "rptPersdata_Dir01_PDFWriter"
' rptBus_HQ=8888 for PDFWriter saves to PDF in TempRoster\BUS_hq=8888.pdf
DoCmd.OpenReport "rptBus_HQ=8888 for PDFWriter", acViewNormal, "", ""
DoCmd.Close acReport, "rptBus_HQ=8888 for PDFWriter"
' rptBus_Bureau<>8888 for PDFWriter saves to PDF in TempRoster\Bus_bureauNot8888.pdf
DoCmd.OpenReport "rptBus_Bureau<>8888 for PDFWriter", acViewNormal, "", ""
DoCmd.Close acReport, "rptBus_Bureau<>8888 for PDFWriter"
' qrytblEmployeeExpress from NFC download_clean table
DoCmd.OpenQuery "qrytblEmployeeExpress from NFC download_clean table", acViewNormal, acEdit
' IMPORT \\prodncs_hrd_server\hrd\hrd\FOCUS\DATABASE\bocexd wn.dat INTO tblEmployeeExpress from NFC Download
DoCmd.TransferText acImportFixed, "EmployeeExpressImport from bocexdwn-txt", "tblEmployeeExpress from NFC Download", "\\prodncs_hrd_server\hrd\hrd\FOCUS\DATABASE\bocex dwn.txt", False, ""
' rptEmployeeExpress for PDFWriter save to g:\workfile\shared\T&A_Staff\bi_rpts\TempRoster\Em ployeeExpress.pdf
DoCmd.OpenReport "rptEmployeeExpress for PDFWriter", acViewNormal, "", ""
DoCmd.Close acReport, "rptEmployeeExpress for PDFWriter"
' rptFINANCIALDISCLOSURE saves to PDF in TempRoster\financialdisclosure_Census.pdf
DoCmd.OpenReport "rptFINANCIALDISCLOSURE", acViewNormal, "", ""
DoCmd.Close acReport, "rptFINANCIALDISCLOSURE"
' rptFinancialDisclosure BEA saves to PDF in TempRoster\finacialdisclosure_BEA.PDF
DoCmd.OpenReport "rptFinancialDisclosure BEA", acViewNormal, "", ""
DoCmd.Close acReport, "rptFinancialDisclosure BEA"
' rptFinancialDisclosure ESA saves to PDF in TempRoster\finacialdisclosure_ESA.PDF
DoCmd.OpenReport "rptFinancialDisclosure ESA", acViewNormal, "", ""
DoCmd.Close acReport, "rptFinancialDisclosure ESA"
' rptAWOL for PP PDFWriter saves to PDF in TempRoster\awol.pdf
DoCmd.OpenReport "rptAWOL for PP PDFWriter", acViewNormal, "", ""
DoCmd.Close acReport, "rptAWOL for PP PDFWriter"
' rptExitQuestion Separation for PDFWriter saves to PDF in TempRoster\exit?.pdf
DoCmd.OpenReport "rptExitQuestion Separation for PDFWriter", acViewNormal, "", ""
DoCmd.Close acReport, "rptExitQuestion Separation for PDFWriter"
' rptPartTime>64 saves to PDF in TempRoster\PartTime.pdf
DoCmd.OpenReport "rptPartTime>64", acViewNormal, "", ""
DoCmd.Close acReport, "rptPartTime>64"
' rptFRs_changing_address saves to PDF in TempRoster\FrchgAdr.pdf
DoCmd.OpenReport "rptFRs_changing_address", acViewNormal, "", ""
DoCmd.Close acReport, "rptFRs_changing_address"
' rptPD_Library_PDF saves to PDF in TempRoster\pd_library.pdf
DoCmd.OpenReport "rptPD_Library_PDF", acViewNormal, "", ""
DoCmd.Close acReport, "rptPD_Library_PDF"
' rptPayrollRoster ESA for PDFWriter_to ESA Password save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\ESA_p ayroll_roster.pdf password = isb
DoCmd.OpenReport "rptPayrollRoster ESA for PDFWriter_to ESA Password ", acViewNormal, "", ""
' rptPersonnelRosterESA_for PDFWriter_toESA Password save pdf to \workfile\shared\T&A_Staff\bi_rpts\TempRoster\ESA_ personnell_roster.pdf password = isb
DoCmd.OpenReport "rptPersonnelRosterESA_for PDFWriter_toESA Password ", acViewNormal, "", ""
' rptRotaryESA for PDFWriter_to ESA Password save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\ESA_r otary_roster.pdf password = isb
DoCmd.OpenReport "rptRotaryESA for PDFWriter_to ESA Password ", acViewNormal, "", ""
' XLS qrySeparations3DS_SINCE 1-1-2004 HQ to g:\workfile\shared\ppsb\HTM_Reports\SepToDOC.xls for Jodee
DoCmd.TransferSpreadsheet acExport, 8, "qrySeparations3DS_SINCE 1-1-2004 HQ", "\\sharedncs_hrd_server\hrd\workfile\shared\ppsb\H TM_Reports\SepToDOC.xls", True, ""
' qryCOOP_EmpN_Duty_State-County-City
DoCmd.TransferSpreadsheet acExport, 8, "qryCOOP_EmpN_Duty_State-County-City", "\\sharedncs_hrd_server\hrd\workfile\shared\ta_sta ff\DisasterPlans\CurrentDutyStationCounts.xls", True, ""
' qryCOOP_EmpCT_Duty_State
DoCmd.TransferSpreadsheet acExport, 8, "qryCOOP_EmpCT_Duty_State", "\\sharedncs_hrd_server\hrd\workfile\shared\ta_sta ff\DisasterPlans\CurrentDutyStationCounts.xls", True, ""
' qryCOOP_EmpCT_Duty_State-County
DoCmd.TransferSpreadsheet acExport, 8, "qryCOOP_EmpCT_Duty_State-County", "\\sharedncs_hrd_server\hrd\workfile\shared\ta_sta ff\DisasterPlans\CurrentDutyStationCounts.xls", True, ""
' qryCOOP_EmpCT_Duty_State-County-City
DoCmd.TransferSpreadsheet acExport, 8, "qryCOOP_EmpCT_Duty_State-County-City", "\\sharedncs_hrd_server\hrd\workfile\shared\ta_sta ff\DisasterPlans\CurrentDutyStationCounts.xls", True, ""
' all 4 COOP queries will go into \\sharedncs_hrd_server\hrd\workfile\shared\ta_staf f\DisasterPlans\CurrentDutyStationCounts.xls
' qryCOOP_EmpCT_ADR_State
DoCmd.TransferSpreadsheet acExport, 8, "qryCOOP_EmpCT_ADR_State", "\\sharedncs_hrd_server\hrd\workfile\shared\ta_sta ff\DisasterPlans\CurrentADRCounts.xls", True, ""
' qryCOOP_EmpCT_ADR_State-County
DoCmd.TransferSpreadsheet acExport, 8, "qryCOOP_EmpCT_ADR_State-County", "\\sharedncs_hrd_server\hrd\workfile\shared\ta_sta ff\DisasterPlans\CurrentADRCounts.xls", True, ""
' qryCOOP_EmpCT_ADR_State-County-City
DoCmd.TransferSpreadsheet acExport, 8, "qryCOOP_EmpCT_ADR_State-County-City", "\\sharedncs_hrd_server\hrd\workfile\shared\ta_sta ff\DisasterPlans\CurrentADRCounts.xls", True, ""
' qryCOOP_EmpN_ADR_State-County-City
DoCmd.TransferSpreadsheet acExport, 8, "qryCOOP_EmpN_ADR_State-County-City", "\\sharedncs_hrd_server\hrd\workfile\shared\ta_sta ff\DisasterPlans\CurrentADRCounts.xls", True, ""
' all 4 COOP queries will go into \\sharedncs_hrd_server\hrd\workfile\shared\ta_staf f\DisasterPlans\CurrentADRCounts.xls
' qryFitness_63 make tblFitness_all
DoCmd.OpenQuery "qryFitness_63 make tblFitness_all", acViewNormal, acEdit
' qryFitness_65 ESA append tblFitness_all
DoCmd.OpenQuery "qryFitness_65 ESA append tblFitness_all", acViewNormal, acEdit
' export qryFitness_Excel to ta_staff\bi-rpts\temproster\Fitness.xls
DoCmd.TransferSpreadsheet acExport, 8, "qryFitness_Excel", "\\sharedncs_hrd_server\hrd\workfile\shared\TA_Sta ff\bi_rpts\TempRoster\Fitness.xls", False, ""
' rptRegionalDirector_CompEarned save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\Regio nalDirector_CompEarned.pdf
DoCmd.OpenReport "rptRegionalDirector_CompEarned", acViewNormal, "", ""
DoCmd.Close acReport, "rptRegionalDirector_CompEarned"
' rptRegionalDirector_CompBalances save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\Regio nalDirector_CompBalances.pdf
DoCmd.OpenReport "rptRegionalDirector_CompBalances", acViewNormal, "", ""
DoCmd.Close acReport, "rptRegionalDirector_CompBalances"
' rptActionsShowingRetirementCode save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\Actio nsShowingRetCode
DoCmd.OpenReport "rptActionsShowingRetirementCode", acViewNormal, "", ""
DoCmd.Close acReport, "rptActionsShowingRetirementCode"
' rptPromosHQ save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\Promo sHQ.pdf email Veronica Legrade, Mary Kennedy, Jennifer McDaniel, April Davis, Rona Scarlett, Peggy Dillon, Gail Smith
DoCmd.OpenReport "rptPromosHQ", acViewNormal, "", ""
DoCmd.Close acReport, "rptPromosHQ"
' rptNonCitizen save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\qryNo nCitizen.pdf
DoCmd.OpenReport "rptNonCitizen", acViewNormal, "", ""
DoCmd.Close acReport, "rptNonCitizen"
' rptNegSickLeaveHQ save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\NegSi ckLeaveHQ.pdf
DoCmd.OpenReport "rptNegSickLeaveHQ", acViewNormal, "", ""
DoCmd.Close acReport, "rptNegSickLeaveHQ"
' rptSupvlist save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\rptSu pvlist.pdf
DoCmd.OpenReport "rptSupvlist", acViewNormal, "", ""
DoCmd.Close acReport, "rptSupvlist"
' rptMixtour save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\rptMi xtour.pdf
DoCmd.OpenReport "rptMixtour", acViewNormal, "", ""
DoCmd.Close acReport, "rptMixtour"
' rptLwoprif save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\rptLw oprif.pdf
DoCmd.OpenReport "rptLwoprif", acViewNormal, "", ""
DoCmd.Close acReport, "rptLwoprif"
' rptClassep save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\rptCl assep.pdf
DoCmd.OpenReport "rptClassep", acViewNormal, "", ""
DoCmd.Close acReport, "rptClassep"
' rptTraine save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\rptTr aine.pdf - tells supervisory probational period employees
DoCmd.OpenReport "rptTraine", acViewNormal, "", ""
DoCmd.Close acReport, "rptTraine"
' rptTraine_BEA save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\rptTr aine_BEA.pdf - tells supervisory probational period employees
DoCmd.OpenReport "rptTraine_BEA", acViewNormal, "", ""
DoCmd.Close acReport, "rptTraine_BEA"
' rptTraine_ESA save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\ rptTraine_ESA.pdf - tells supervisory probational period employees
DoCmd.OpenReport "rptTraine_ESA", acViewNormal, "", ""
DoCmd.Close acReport, "rptTraine_ESA"
' rptPmsomath save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\rptPm somath.pdf
DoCmd.OpenReport "rptPmsomath", acViewNormal, "", ""
DoCmd.Close acReport, "rptPmsomath"
' rptREIM_over1000_week1 save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\qryRE IM_over1000_week1.pdf to Julie Tayman
DoCmd.OpenReport "rptREIM_over1000_week1", acViewNormal, "", ""
DoCmd.Close acReport, "rptREIM_over1000_week1"
' rptREIM_over1000_week2 save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\qryRE IM_over1000_week2.pdf to Julie Tayman
DoCmd.OpenReport "rptREIM_over1000_week2 ", acViewNormal, "", ""
DoCmd.Close acReport, "rptREIM_over1000_week2 "
' rptLWOPTA save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\rptLW OPTA.pdf
DoCmd.OpenReport "rptLWOPTA", acViewNormal, "", ""
DoCmd.Close acReport, "rptLWOPTA"
' rptState_address_tax_diff save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\rptSt ate_address_tax_diff.pdf to Cindy Borza
DoCmd.OpenReport "rptState_address_tax_diff ", acViewNormal, "", ""
DoCmd.Close acReport, "rptState_address_tax_diff "
' rptState_address_tax_diff_BEA save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\rptSt ate_address_tax_diff_BEA.pdf
DoCmd.OpenReport "rptState_address_tax_diff_BEA", acViewNormal, "", ""
DoCmd.Close acReport, "rptState_address_tax_diff_BEA"
' rptState_address_tax_diff_ESA save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\rptSt ate_address_tax_diff_ESA.pdf
DoCmd.OpenReport "rptState_address_tax_diff_ESA", acViewNormal, "", ""
DoCmd.Close acReport, "rptState_address_tax_diff_ESA"
' rptUnion deducations-BUS 7777 or 8888 save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\rptUn ion deducations-BUS 7777 or 8888.pdf - Julie
DoCmd.OpenReport "rptUnion deducations-BUS 7777 or 8888 ", acViewNormal, "", ""
DoCmd.Close acReport, "rptUnion deducations-BUS 7777 or 8888 "
' rptUnion DutyStation 241520 Union 2782 save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\rptUn ion DutyStation 241520 Union 2782.pdf - Julie
DoCmd.OpenReport " rptUnion DutyStation 241520 Union 2782 ", acViewNormal, "", ""
DoCmd.Close acReport, "rptUnion DutyStation 241520 Union 2782 "
' rptTickleforPrint BEA save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\rptTi ckleforPrintBEA.pdf
DoCmd.OpenReport "rptTickleforPrint BEA", acViewNormal, "", ""
DoCmd.Close acReport, "rptTickleforPrint BEA"
' rptBSeparateBEA save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\rptBS eparateBEA.pdf
DoCmd.OpenReport "rptBSeparateBEA", acViewNormal, "", ""
DoCmd.Close acReport, "rptBSeparateBEA"
' NEWCDBEA save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\NEWCD BEA.pdf
DoCmd.OpenReport "NEWCDBEA", acViewNormal, "", ""
DoCmd.Close acReport, "NEWCDBEA"
' rptbSeparateESA save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster
DoCmd.OpenReport "rptbSeparateESA", acViewNormal, "", ""
DoCmd.Close acReport, "rptbSeparateESA"
' rptTickleforPrint ESA save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\rptTi ckleforPrint ESA.pdf
DoCmd.OpenReport " rptTickleforPrint ESA", acViewNormal, "", ""
DoCmd.Close acReport, "rptTickleforPrint ESA"
' NEWCDESA save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoste\NEWCDE SA.pdf
DoCmd.OpenReport "NEWCDESA", acViewNormal, "", ""
DoCmd.Close acReport, "NEWCDESA"
' SepInfo ESA save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\SepIn fo ESA.pdf
DoCmd.OpenReport "SepInfo ESA", acViewNormal, "", ""
DoCmd.Close acReport, "SepInfo ESA"
' rptNegSickLeaveESA save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\rptNe gSickLeaveESA.pdf
DoCmd.OpenReport "rptNegSickLeaveESA", acViewNormal, "", ""
DoCmd.Close acReport, "rptNegSickLeaveESA"
' rptBEA_COMP_FullReport print to PDF in TempRoster - password protected (isb) this is temporary until BEA's Comp report can be put into CHRIS email to Jack Jetmund
DoCmd.OpenReport "rptBEA_COMP_FullReport", acViewNormal, "", ""
DoCmd.Close acReport, "rptBEA_COMP_FullReport"
' rptStimulusJobs_Count save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\rptSt imulusJobsCount.pdf
DoCmd.OpenReport "rptStimulusJobs_Count", acViewNormal, "", ""
DoCmd.Close acReport, "rptStimulusJobs_Count"
' rptStimulus_PPActions_Names save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\rptSt imulusPPActionsNames.pdf
DoCmd.OpenReport "rptStimulus_PPActions_Names", acViewNormal, "", ""
DoCmd.Close acReport, "rptStimulus_PPActions_Names"
' rptStimulus_PPActions_Names_3_Details save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\rptSt imulusPPActionsNames_3_Details.pdf
DoCmd.OpenReport "rptStimulus_PPActions_Names_3_Details", acViewNormal, "", ""
DoCmd.Close acReport, "rptStimulus_PPActions_Names_3_Details"
' qryStimulus_ARRA_Employees_2 saves auto to workfile\shared\tA_staff\bi_rpts\temproster\Stimul us_ARRA_Employees.xls
DoCmd.TransferSpreadsheet acExport, 8, "qryStimulus_ARRA_Employees_2", "\\sharedncs_hrd_server\hrd\workfile\shared\ta_sta ff\bi_rpts\temproster\Stimulus_ARRA_Employees.xls" , False, ""
' rptState_address_tax_diff_HQnotMDDCVA save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\rptSt ate_address_tax_diff_HQnotMDDCVA.pdf
DoCmd.OpenReport "rptState_address_tax_diff_HQnotMDDCVA", acViewNormal, "", ""
DoCmd.Close acReport, "rptState_address_tax_diff_HQnotMDDCVA"
' rptDUTY_Home_address_diff_FRs save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\rptDU TY_Home_address_diff_FRs.pdf
DoCmd.OpenReport "rptDUTY_Home_address_diff_FRs", acViewNormal, "", ""
DoCmd.Close acReport, "rptDUTY_Home_address_diff_FRs"
' rptHQ_State_tax_notMDDCVA save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\rptHQ _State_tax_notMDDCVA.pdf
DoCmd.OpenReport "rptHQ_State_tax_notMDDCVA", acViewNormal, "", ""
DoCmd.Close acReport, "rptHQ_State_tax_notMDDCVA"
' rptLCO_Mgr_Count save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\rptLC O_Mgr_Count.pdf
DoCmd.OpenReport "rptLCO_Mgr_Count", acViewNormal, "", ""
DoCmd.Close acReport, "rptLCO_Mgr_Count"
' rptAnnualLeaveCat_0 save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\rptAn nualLeaveCat_0
DoCmd.OpenReport "rptAnnualLeaveCat_0", acViewNormal, "", ""
DoCmd.Close acReport, "rptAnnualLeaveCat_0"
' rptAnnualLeaveCat_0_BEA save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\rptAn nualLeaveCat_0_BEA
DoCmd.OpenReport "rptAnnualLeaveCat_0_BEA", acViewNormal, "", ""
DoCmd.Close acReport, "rptAnnualLeaveCat_0_BEA"
' rptAnnualLeaveCat_0_ESA save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\rptAn nualLeaveCat_0_ESA
DoCmd.OpenReport "rptAnnualLeaveCat_0_ESA", acViewNormal, "", ""
DoCmd.Close acReport, "rptAnnualLeaveCat_0_ESA"
' rptCOLA save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\rptCO LA.pdf
DoCmd.OpenReport "rptCOLA", acViewNormal, "", ""
DoCmd.Close acReport, "rptCOLA"
' qryRoster_Reg-TP-Detail_HRD_2 export Excel to workfile\shared\T&A_Staff\bi_rpts\TempRoster\2010R oster_14-HRD.xls
DoCmd.TransferSpreadsheet acExport, 8, "qryRoster_Reg-TP-Detail_HRD_2", "I:\bi_rpts\TempRoster\2010Roster_14-HRD.xls", True, ""
' qryOrg16tbl active to WEB_Census_2 (no dummies) g:\workfile\shared\systems\danielle\orgstructure_C ensus.html
DoCmd.TransferText acExportHTML, "", "qryOrg16tbl active to WEB_Census_2 (no dummies)", "\\sharedncs_hrd_server\hrd\workfile\shared\system s\danielle\orgstructure_Census.html", False, ""
' to get rid of other dummies add to tblOrg16 Active Dummies
' qryOrg16tbl active to WEB_BEA g:\workfile\shared\systems\danielle\orgstructure_B EA.html
DoCmd.TransferText acExportHTML, "", "qryOrg16tbl active to WEB_BEA", "\\sharedncs_hrd_server\hrd\workfile\shared\system s\danielle\orgstructure_BEA.html", False, ""
' qryOrg16tbl active to WEB_ESA g:\workfile\shared\systems\danielle\orgstructure_E SA.html
DoCmd.TransferText acExportHTML, "", "qryOrg16tbl active to WEB_ESA", "\\sharedncs_hrd_server\hrd\workfile\shared\system s\danielle\orgstructure_ESA.html", False, ""
' qryGG_GH_GM_Gr_15_CENSUS TO \\sharedncs_hrd_server\hrd\workfile\SHARED\ppsb\HT M_Reports\GR15_GG-GH-GM_CENSUS.XLS
DoCmd.TransferSpreadsheet acExport, 8, "qryGG_GH_GM_Gr_15_CENSUS", "\\sharedncs_hrd_server\hrd\workfile\SHARED\ppsb\H TM_Reports\GR15_GG-GH-GM_CENSUS.XLS", True, ""
' qryGG_GH_GM_Gr_15_ESA TO \\sharedncs_hrd_server\hrd\workfile\SHARED\ppsb\HT M_Reports\GR15_GG-GH-GM_ESA.XLSR15_GG-GH-GM.XLS
DoCmd.TransferSpreadsheet acExport, 8, "qryGG_GH_GM_Gr_15_ESA", "\\sharedncs_hrd_server\hrd\workfile\SHARED\ppsb\H TM_Reports\GR15_GG-GH-GM_ESA.XLS", True, ""
' qryBranchCounts_Biweekly_JeffMiller to \\sharedncs_hrd_server\hrd\workfile\SHARED\PEB\acc ess\pdfreports\BranchCounts.xls
DoCmd.TransferSpreadsheet acExport, 8, "qryBranchCounts_Biweekly_JeffMiller", "\\sharedncs_hrd_server\hrd\workfile\SHARED\PEB\ac cess\pdfreports\BranchCounts.xls", True, ""
' qrypaypers_npc_comma to \\prodncs_hrd_server\hrd\hrd\focus\database\payper s_npc_comma.txt
DoCmd.TransferText acExportDelim, "", "qrypaypers_npc_comma", "\\prodncs_hrd_server\hrd\hrd\focus\database\paype rs_npc_comma.txt", False, ""
' qrypaypers_npc_comma to \\\hrb ecs staff\hrb ecs reports\access2000\pdf_reports\paypers_npc_comma.t xt
DoCmd.TransferText acExportDelim, "", "qrypaypers_npc_comma", "\\\hrb ecs staff\hrb ecs reports\access2000\pdf_reports\paypers_npc_comma.t xt", False, ""
' rptEOD_ALaccruals save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\EOD_A ccruals_pp.pdf
DoCmd.OpenReport "rptEOD_ALaccruals", acViewNormal, "", ""
' rptNOA_882_Tracking save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\SCD_A ctions_pp.pdf
DoCmd.OpenReport "rptNOA_882_Tracking", acViewNormal, "", ""
' rptRotary_NPC_PDF save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\ROSTE R_NPC.PDF
DoCmd.OpenReport "rptRotary_NPC_PDF", acViewNormal, "", ""
' rptRotary_NPC_SSNO_PDF save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\ROSTE R_NPC_SSNO.PDF
DoCmd.OpenReport "rptRotary_NPC_SSNO_PDF", acViewNormal, "", ""
' rptTickleforPrint_NPC_PDF save pdf to workfile\shared\T&A_Staff\bi_rpts\TempRoster\Tickl e_NPC.PDF
DoCmd.OpenReport "rptTickleforPrint_NPC_PDF", acViewNormal, "", ""
' rptAccLabels_Census-1798
DoCmd.OpenReport "rptAccLabels_Census-1798", acViewPreview, "", ""
' rptAccLabels_BEA+ESA
DoCmd.OpenReport "rptAccLabels_BEA+ESA", acViewPreview, "", ""
' rptTempEmp_FEHBCovElig_addressLabels give to PPSB for Breezy Cornio this should relate to a NFC report they print off the same biweekly Monday.
DoCmd.OpenReport "rptTempEmp_FEHBCovElig_addressLabels", acViewPreview, "", ""
DoCmd.Echo True, ""
DoCmd.SetWarnings True

Exit Function

MsgBox Error$
Resume mcrBiweeklyReports_PDF_inForm_Exit

Apr 29 '10 #11

Expert 100+
P: 931
The macro code isn't what I'm interested in.

I think you have to forget everything about the macro and try the approach which I have laid out for you. I wrote about a dozen lines of code for you to use which will accomplish the sequence of actions you have asked for:
  1. Open folder picker dialog and select a location
  2. Output a report to the chosen location using the report caption as the filename

And again, the code is

Expand|Select|Wrap|Line Numbers
  1. Dim fd As FileDialog
  2. Dim strFolder As String
  4. Set fd = Application.FileDialog(msoFileDialogFolderPicker)
  6. If fd.Show = -1 Then
  7.     strFolder = fd.SelectedItems.Item(1)
  8. Else
  9.     Exit Sub
  10. End If
  12. DoCmd.OutputTo acOutputReport, "Report Name", acFormatPDF, strFolder & "\" & Report.[Report Name].Caption & ".PDF"
  14. Set fd = Nothing

This set of code will work for one report; what I'm trying to find out is whether or not this is working for you...for one report. If it is, then we can move on to look at how to do it for all 45 reports.

You seem to keep coming back to the point about putting the report caption in the dialog box, but I'm not sure why. The only purpose of the dialog box in what I'm showing you is to pick out a single folder where all the reports will go...and that's it. The caption becomes the filename at the point where you export the report using DoCmd. All the reports will go in the selected folder, under the filename determined by the report caption.

If this isn't what you're looking for, you need to tell me.

Apr 29 '10 #12

P: 12
Hi Pat,

Thanks again for the code. I actually can't forget about the macro because it will be used to run the code and the users are looking for the process to be the same in Access 2007 as with the Access 2000 macro. Also, I keep mentioning the need for the report caption because it is used as the file name so that the users don't have to remember a file name for all of the files they output to PDFs (the file name is not the same as the name of the report object).

Thank you again; I'm going to use what you've given me as you suggested.

Apr 29 '10 #13

Expert 100+
P: 931
I'm not sure what you mean by saying that the macro will still be used to run the code. The code that I have posted is meant to be placed in the On Click event of a command button on a form, for instance, and run that way. Are the users in fact interacting with the database via a form?

If you run the code the way I've writtten it for a report in your database, it will output the report to a file using the report caption as a filename...

Apr 29 '10 #14

P: 12
I am working with 27+ Access 2000 databases that are part of processes that run in the HR department. We have migrated to Office 2007 and some of the macro code in these Access 2000 databases would not be supported. I researched and found that converting the macros to VBA code would resolve the problem in the near term. Some of the databases have forms with macros associated with command buttons. However, in most of the files, the users go directly to the queries to make changes and to the macros to create tables and save PDF format reports. What I have been doing is using the Access 2007 macro converter to convert the macros to VBA code. Then I edit the macro to run the code referenced in the function in the VBA module. The user still runs the process from the macro and not a command button on a form.

The OpenReport action in the macros is still valid and operates as before. It's some of the other commands in the same macros, such as TransferTxt, that are no longer valid that have to be converted to VBA. I have found that the OpenReport action in macros is also a VBA command. The reports have been formatted to print to Adobe PDF. Is there VBA code that you have used that uses DoCmd.OpenReport .... and brings up the same dialog box as it does in the macro?

Apr 29 '10 #15

Expert 100+
P: 931
I'm a little confused by the process your users go through to produce the reports.

I have never used macros and cannot advise you on what to do in regard to them. I think most experienced Access programmers will steer you away from macros, not just because they are limited in what they are able to do, but also because they are meant to be used without the end user "looking under the hood" so to speak.

And I think that's where you're going astray. It seems like you are looking at the macro-generated code and trying to mimic it line by line. What I have been trying to do is help you solve your problem without doing this.

Although it would probably be more work than what you originally expected, it might be better to just solve your problem from scratch, standardizing the manner in which the users generate the reports across all the databases and eliminating the macros.

Apr 30 '10 #16

Expert 100+
P: 931

I'm sorry that I haven't been able to fully bring this to solution. I have made a small database with a table, report and form that illustrates my point, and I would like to attach it here for you to actually see my thinking in action; however, I am having a problem with Access freezing upon closing it.

I'm going to post separately about this issue to see if I can resolve it. If I can resolve it, I will attach the file to this thread for you to take a look at.

Apr 30 '10 #17

Expert 100+
P: 931

I apologize for the delay. I created a minimal test database to illustrate my point for you, but there was a small problem with it which I just managed to clear up with the help of a few other folks here on the site.

The file is zipped and attached here. Open the form in regular view mode and click the "Export Report" command button. A folder dialog pops up. Upon navigating to the location where you want the report saved and hitting "OK", the report in the database will export to that location as a PDF, with the report's caption as the filename. Click on Database Tools > Visual Basic to examine the code.

As I stated previously, your situation is clearly going to require a little more work because 1) you have 45 reports and 2) there seems to be no standardized way for the users to export the reports. I do think these issues can be overcome, but I also think you need to leave the macro behind and just focus on doing it from scratch as though the macro didn't exist.

My objective here was to show you the basic process for accomplishing your goal as I understand it.

May 4 '10 #18

Post your reply

Sign in to post your reply or Sign up for a free account.