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

[cmcsween]

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.OpenRepor t "rptmyrepor t", 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, "rptmyrepor t", 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.

Carol

[patjones]

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.

Pat

[patjones]

The basic code to do this is

Dim fd As FileDialog
Dim strFolder As String

Set fd = Application.FileDialog(msoFileDialogFolderPicker)

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

DoCmd.OutputTo acOutputReport, "rptTrackingReport", acFormatPDF, strFolder & "\rptTrackingReport.PDF"

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 "\rptTrackingRe port.PDF" parameter so that each PDF comes out with a name corresponding to the correct report.

Pat

[cmcsween]

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.

Carol

[patjones]

Hi,

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:

Dim j as Integer

For j = 1 to 45
     DoCmd.OutputTo acOutputReport, "rptTrackingReport", acFormatPDF, strFolder & "\rptTracking" & CStr(j) & ".PDF"
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.

Pat

[cmcsween]

Pat,

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 rptStaffingTabl e", acFormatPDF, , , , , acExportQuality Print

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 StaffingTableYY PP 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.

Carol

[patjones]

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:

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

...is not going to do what you want because you haven't even supplied a path and filename. In my version of this statement:

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

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_ReportNa me.Caption. In your case, you have a space in your report name: Report_[1 rptStaffingTabl e].Caption. If having the report caption be the filename is your goal, then it's a matter of writing

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.

Pat

[cmcsween]

Pat,

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 rptStaffingTabl e].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.

Carol

[patjones]

Can you please post all the code you've got so far so that we are on the same page?

Pat

[cmcsween]

Pat,

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

'------------------------------------------------------------
' mac_1_StaffingT able_Update_and _Print_to_PDF_M T
'
'------------------------------------------------------------
Function mac_1_StaffingT able_Update_and _Print_to_PDF_M T()
On Error GoTo mac_1_StaffingT able_Update_and _Print_to_PDF_M T_Err
Dim fd As FileDialog
Dim strFolder As String

Set fd = Application.Fil eDialog(msoFile DialogFolderPic ker)

If fd.Show = -1 Then
strFolder = fd.SelectedItem s.Item(1)
Else
Exit Function
End If

DoCmd.SetWarnin gs False
' qryStaffingTabl e2 make table tblStaffingTabl e_Mt
' DoCmd.OpenQuery "qryStaffingTab le2 make table tblStaffingTabl e_Mt", acViewNormal, acEdit
' DoCmd.Close acQuery, "qryStaffingTab le2 make table tblStaffingTabl e_Mt"
' 1 rptStaffingTabl e save to g:\workfile\pdf reports\staffin gtable\Staffing TableYYPP.PDF
' DoCmd.OpenRepor t "1 rptStaffingTabl e", acViewNormal, "", ""
' Var1 = reportobject.Ca ption
' DoCmd.OutputTo acOutputReport, "1 rptStaffingTabl e", acFormatPDF, , , , , acExportQuality Print
DoCmd.OutputTo acOutputReport, "1 rptStaffingTabl e", acFormatPDF, strFolder & "\" & Report.[1 rptStaffingTabl e].Caption & ".PDF", , , , acExportQuality Print

Beep

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

Set fd = Nothing

mac_1_StaffingT able_Update_and _Print_to_PDF_M T_Exit:
Exit Function

mac_1_StaffingT able_Update_and _Print_to_PDF_M T_Err:
MsgBox Error$
Resume mac_1_StaffingT able_Update_and _Print_to_PDF_M T_Exit

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.

Carol

[cmcsween]

Pat,

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.OpenRepor t ... line for each report to code which would allow me to print to the Adobe PDF printer or save the file as a PDF.

'------------------------------------------------------------
' mcrBiweeklyRepo rts_PDF_inForm
'
'------------------------------------------------------------
Function mcrBiweeklyRepo rts_PDF_inForm( )
On Error GoTo mcrBiweeklyRepo rts_PDF_inForm_ Err

DoCmd.Echo True, ""
DoCmd.SetWarnin gs False
' Email rptACSD-HQemployeesOnLe aveFullPayperio d_for_transit subsidy to g:\workfile\sha red\ta_staff\bi _rpts\TempRoste r\ACSD-OnLeave_pp.pdf email to Jim Saccamondo
DoCmd.OpenRepor t "rptACSD-HQemployeesOnLe aveFullPayperio d_for_transit subsidy", acViewNormal, "", ""
' rptWorkersComp-LWOP to \\sharedncs_hrd _server\hrd\\wo rkfile\shared\t a_staff\bi_rpts \TempRoster\wor kerscomp-lwop.pdf
DoCmd.OpenRepor t "rptWorkers Comp-LWOP", acViewNormal, "", ""
' rptMT_NTE_Tickl e to PDF \\sharedncs_hrd _servers\hrd\wo rkfile\SHARED\E MPLOY\VICKI\MT_ Tickles\MT_NTE_ TICKLE.PDF for Vicki Dabbs
DoCmd.OpenRepor t "rptMT_NTE_Tick le", acViewNormal, "", ""
' qryPromotionsPP _Clean table
DoCmd.OpenQuery "qryPromotionsP P_Clean table", acViewNormal, acEdit
' Import \\prodncs_hrd_s erver\hrd\hrd\f ocus\database\p romsal.txt
DoCmd.TransferT ext acImportFixed, "Promsal Import Specification", "tblPromsal_NFC download", "\\prodncs_hrd_ server\hrd\hrd\ focus\database\ promsal.txt", False, ""
' rptPromotionsPP _CheckSalary print PDF to temproster then SCRIPT to m:\workfile\sha red\ppsb\htm_re ports\Promotion sPP_CheckSalary .pdf for Jodee/Julie
DoCmd.OpenRepor t "rptPromotionsP P_CheckSalary", acViewNormal, "", ""
' qryABCO_Clean_t blABCO_fromNFCd ownload_ABCOdwn
DoCmd.OpenQuery "qryABCO_Clean_ tblABCO_fromNFC download_ABCOdw n", acViewNormal, acEdit
' import \\prodncs_hrd_s erver\hrd\hrd\f ocus\database\A BCOdwn.xls into tblABCO_fromNFC download_ABCOdw n
DoCmd.TransferS preadsheet acImport, 8, "tblABCO_fromNF Cdownload_ABCOd wn", "\\prodncs_hrd_ server\hrd\hrd\ focus\database\ ABCOdwn.xls", True, ""
' qryABCO_outrcvb l_paypersdata to \\sharedncs_hrd _server\hrd\wor kfile\shared\ta _staff\bi_rpts\ TempRoster\ABCO .xls
DoCmd.TransferS preadsheet acExport, 8, "qryABCO_outrcv bl_paypersdata" , "\\sharedncs_hr d_server\hrd\wo rkfile\shared\t a_staff\bi_rpts \TempRoster\ABC O.xls", True, ""
' rptUnion-Improper Union Dues print PDF to temproster then SCRIPT to m:\workfile\sha red\Labor\acces s\pdfreports\Im proper_Dues.pdf for Labor Branch
DoCmd.OpenRepor t "rptUnion-Improper Union Dues", acViewNormal, "", ""
' rptUnionded for PDFWriter print PDF to temproster then SCRIPT to m:\workfile\sha red\Labor\acces s\pdfreports\Un ionded.pdf for Labor Branch
DoCmd.OpenRepor t "rptUnionde d for PDFWriter", acViewNormal, "", ""
' rptsephlth for PDFWriter print PDF to temproster then SCRIPT to m:\workfile\sha red\Labor\acces s\pdfreports\se phlth.pdf for Labor Branch
DoCmd.OpenRepor t "rptsephlth for PDFWriter", acViewNormal, "", ""
' rptEmployeeRost er_RetirementTy pe_FIN_w/SALARY print PDF to temproster then SCRIPT to \\prodncs_hrd_s erver\hrd\hrd\F OCUS\DATABASE\F IN\FIN_Roster\E mpRoster_Retire Type.pdf
DoCmd.OpenRepor t "rptEmployeeRos ter_RetirementT ype_FIN_w/SALARY", acViewNormal, "\\prodncs_hrd_ server\hrd\hrd\ FOCUS\DATABASE\ FIN\FIN_Roster\ EmpRoster_Retir eType.pdf", ""
' rptDetMinCareer \\sharedncs_hrd _server\hrd\\wo rkfile\shared\t a_staff\bi_rpts \TempRoster\rpt DetMinCareer
DoCmd.OpenRepor t "rptDetMinCaree r", acViewNormal, "", ""
' rptDetMinIntPer m
DoCmd.OpenRepor t "rptDetMinIntPe rm", acViewNormal, "", ""
' rptDetMinIntTem p
DoCmd.OpenRepor t "rptDetMinIntTe mp", acViewNormal, "", ""
' rptDetMinPerm
DoCmd.OpenRepor t "rptDetMinPerm" , acViewNormal, "", ""
' rptDetMinPTPerm
DoCmd.OpenRepor t "rptDetMinPTPer m", acViewNormal, "", ""
' qryCompTotals_C lean_tblCOMPTOT _download from NFC
DoCmd.OpenQuery "qryCompTotals_ Clean_tblCOMPTO T_download from NFC", acViewNormal, acEdit
' import \\prodncs_hrd_s erver\hrd\hrd\f ocus\database\c omptot.txt spec=COMPTOT2 EXTENDED Import Specification into tblCOMPTOT_down load from NFC
DoCmd.TransferT ext acImportFixed, "COMPTOT2 EXTENDED Import Specification", "tblCOMPTOT_dow nload from NFC", "\\prodncs_hrd_ server\hrd\hrd\ focus\database\ comptot.txt", False, ""
' rptSeps2DoC prints PDF to \\sharedncs_hrd _server\hrd\\wo rkfile\shared\t a_staff\bi_rpts \TempRoster\Sep 2DoC.pdf
DoCmd.OpenRepor t "rptSeps2Do C", acViewNormal, "", ""
' rptRNOinfo save to PDF in temproster\RNO_ Info.pdf
DoCmd.OpenRepor t "rptRNOinfo ", acViewNormal, "", ""
DoCmd.Close acTable, "rptrnoinfo "
' rptSpec_Emp_Pgm 40 not in 141710 for PDFWriter saves to PDF in TempRoster\spec 40.pdf
DoCmd.OpenRepor t "rptSpec_Emp_Pg m 40 not in 141710 for PDFWriter", acViewNormal, "", ""
DoCmd.Close acReport, "rptSpec_Emp_Pg m 40 not in 141710 for PDFWriter"
' rptBUS_HRD<>888 8_PDFWriter saves to PDF in TempRoster\hrd_ busNOT888.PDF
DoCmd.OpenRepor t "rptBUS_HRD<>88 88_PDFWriter", acViewNormal, "", ""
DoCmd.Close acReport, "rptBUS_HRD<>88 88_PDFWriter"
' qrytblLvLiDown_ clean table
DoCmd.OpenQuery "qrytblLvLiDown _clean table", acViewNormal, acEdit
' import \\prodncs_hrd_s erver\hrd\hrd\f ocus\database\l vlidown.txt into tblLvLiDown using LvLiDown Import Specs
DoCmd.TransferT ext acImportFixed, "Lvlidown Import Specification", "tblLvLiDow n", "\\prodncs_hrd_ server\hrd\hrd\ focus\database\ lvlidown.txt", False, ""
' rptlvliDown with restored leave PDFWriter print PDF to \workfile\share d\TA_Staff\bi_r pts\TempRoster\ leaveliability. pdf
DoCmd.OpenRepor t "rptlvliDow n with restored leave PDFWriter", acViewNormal, "", ""
DoCmd.Close acReport, "rptlvliDow n with restored leave PDFWriter"
' qryGeoTable1_PP SB - makes tblGeoTable_PPS B
DoCmd.OpenQuery "qryGeoTable1_P PSB", acViewNormal, acEdit
' qryGeoTable3_PP SB_city-no-county - - appends cities with no counties to tblGeoTable_PPS B see Adamana, Arizona
DoCmd.OpenQuery "qryGeoTable3_P PSB_city-no-county", acViewNormal, acEdit
' rptGeoTable_PPS B print to PDF in ta_staff\bi_rpt s\TempRoster\Ge oTable.pdf
DoCmd.OpenRepor t "rptGeoTable_PP SB", acViewNormal, "", ""
' rptMRList saves to PDF in TempRoster\mrli st.pdf
DoCmd.OpenRepor t "rptMRList" , acViewNormal, "", ""
DoCmd.Close acReport, "rptMRList"
' rptNtelist saves to PDF in TempRoster\ntel ist.pdf
DoCmd.OpenRepor t "rptNtelist ", acViewNormal, "", ""
DoCmd.Close acReport, "rptNtelist "
' rptJobData saves to PDF in TempRoster\jobd ata.pdf
DoCmd.OpenRepor t "rptJobData ", acViewNormal, "", ""
DoCmd.Close acReport, "rptJobData "
' rptSeparation Report HQ for PDFWriter saves to PDF in TempRoster\sepa ration_hq.pdf
DoCmd.OpenRepor t "rptSeparat ion Report HQ for PDFWriter", acViewNormal, "", ""
DoCmd.Close acReport, "rptSeparat ion Report HQ for PDFWriter"
' rptSecurityList for Guards for PDFWriter saves to PDF in TempRoster\secu ritylist.pdf
DoCmd.OpenRepor t "rptSecurityLis t for Guards for PDFWriter", acViewNormal, "", ""
DoCmd.Close acReport, "rptSecurityLis t for Guards for PDFWriter"
' rptPersdata_Dir 01_PDFWriter saves to PDF in TempRoster\Pers data_DIR01.pdf
DoCmd.OpenRepor t "rptPersdata_Di r01_PDFWriter", acViewNormal, "", ""
DoCmd.Close acReport, "rptPersdata_Di r01_PDFWriter"
' rptBus_HQ=8888 for PDFWriter saves to PDF in TempRoster\BUS_ hq=8888.pdf
DoCmd.OpenRepor t "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.p df
DoCmd.OpenRepor t "rptBus_Bureau< >8888 for PDFWriter", acViewNormal, "", ""
DoCmd.Close acReport, "rptBus_Bureau< >8888 for PDFWriter"
' qrytblEmployeeE xpress from NFC download_clean table
DoCmd.OpenQuery "qrytblEmployee Express from NFC download_clean table", acViewNormal, acEdit
' IMPORT \\prodncs_hrd_s erver\hrd\hrd\F OCUS\DATABASE\b ocexdwn.dat INTO tblEmployeeExpr ess from NFC Download
DoCmd.TransferT ext acImportFixed, "EmployeeExpres sImport from bocexdwn-txt", "tblEmployeeExp ress from NFC Download", "\\prodncs_hrd_ server\hrd\hrd\ FOCUS\DATABASE\ bocexdwn.txt", False, ""
' rptEmployeeExpr ess for PDFWriter save to g:\workfile\sha red\T&A_Staff\b i_rpts\TempRost er\EmployeeExpr ess.pdf
DoCmd.OpenRepor t "rptEmployeeExp ress for PDFWriter", acViewNormal, "", ""
DoCmd.Close acReport, "rptEmployeeExp ress for PDFWriter"
' rptFINANCIALDIS CLOSURE saves to PDF in TempRoster\fina ncialdisclosure _Census.pdf
DoCmd.OpenRepor t "rptFINANCIALDI SCLOSURE", acViewNormal, "", ""
DoCmd.Close acReport, "rptFINANCIALDI SCLOSURE"
' rptFinancialDis closure BEA saves to PDF in TempRoster\fina cialdisclosure_ BEA.PDF
DoCmd.OpenRepor t "rptFinancialDi sclosure BEA", acViewNormal, "", ""
DoCmd.Close acReport, "rptFinancialDi sclosure BEA"
' rptFinancialDis closure ESA saves to PDF in TempRoster\fina cialdisclosure_ ESA.PDF
DoCmd.OpenRepor t "rptFinancialDi sclosure ESA", acViewNormal, "", ""
DoCmd.Close acReport, "rptFinancialDi sclosure ESA"
' rptAWOL for PP PDFWriter saves to PDF in TempRoster\awol .pdf
DoCmd.OpenRepor t "rptAWOL for PP PDFWriter", acViewNormal, "", ""
DoCmd.Close acReport, "rptAWOL for PP PDFWriter"
' rptExitQuestion Separation for PDFWriter saves to PDF in TempRoster\exit ?.pdf
DoCmd.OpenRepor t "rptExitQuestio n Separation for PDFWriter", acViewNormal, "", ""
DoCmd.Close acReport, "rptExitQuestio n Separation for PDFWriter"
' rptPartTime>64 saves to PDF in TempRoster\Part Time.pdf
DoCmd.OpenRepor t "rptPartTime>64 ", acViewNormal, "", ""
DoCmd.Close acReport, "rptPartTime>64 "
' rptFRs_changing _address saves to PDF in TempRoster\Frch gAdr.pdf
DoCmd.OpenRepor t "rptFRs_changin g_address", acViewNormal, "", ""
DoCmd.Close acReport, "rptFRs_changin g_address"
' rptPD_Library_P DF saves to PDF in TempRoster\pd_l ibrary.pdf
DoCmd.OpenRepor t "rptPD_Library_ PDF", acViewNormal, "", ""
DoCmd.Close acReport, "rptPD_Library_ PDF"
' rptPayrollRoste r ESA for PDFWriter_to ESA Password save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ ESA_payroll_ros ter.pdf password = isb
DoCmd.OpenRepor t "rptPayrollRost er ESA for PDFWriter_to ESA Password ", acViewNormal, "", ""
' rptPersonnelRos terESA_for PDFWriter_toESA Password save pdf to \workfile\share d\T&A_Staff\bi_ rpts\TempRoster \ESA_personnell _roster.pdf password = isb
DoCmd.OpenRepor t "rptPersonnelRo sterESA_for PDFWriter_toESA Password ", acViewNormal, "", ""
' rptRotaryESA for PDFWriter_to ESA Password save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ ESA_rotary_rost er.pdf password = isb
DoCmd.OpenRepor t "rptRotaryE SA for PDFWriter_to ESA Password ", acViewNormal, "", ""
' XLS qrySeparations3 DS_SINCE 1-1-2004 HQ to g:\workfile\sha red\ppsb\HTM_Re ports\SepToDOC. xls for Jodee
DoCmd.TransferS preadsheet acExport, 8, "qrySeparations 3DS_SINCE 1-1-2004 HQ", "\\sharedncs_hr d_server\hrd\wo rkfile\shared\p psb\HTM_Reports \SepToDOC.xls", True, ""
' qryCOOP_EmpN_Du ty_State-County-City
DoCmd.TransferS preadsheet acExport, 8, "qryCOOP_EmpN_D uty_State-County-City", "\\sharedncs_hr d_server\hrd\wo rkfile\shared\t a_staff\Disaste rPlans\CurrentD utyStationCount s.xls", True, ""
' qryCOOP_EmpCT_D uty_State
DoCmd.TransferS preadsheet acExport, 8, "qryCOOP_EmpCT_ Duty_State", "\\sharedncs_hr d_server\hrd\wo rkfile\shared\t a_staff\Disaste rPlans\CurrentD utyStationCount s.xls", True, ""
' qryCOOP_EmpCT_D uty_State-County
DoCmd.TransferS preadsheet acExport, 8, "qryCOOP_EmpCT_ Duty_State-County", "\\sharedncs_hr d_server\hrd\wo rkfile\shared\t a_staff\Disaste rPlans\CurrentD utyStationCount s.xls", True, ""
' qryCOOP_EmpCT_D uty_State-County-City
DoCmd.TransferS preadsheet acExport, 8, "qryCOOP_EmpCT_ Duty_State-County-City", "\\sharedncs_hr d_server\hrd\wo rkfile\shared\t a_staff\Disaste rPlans\CurrentD utyStationCount s.xls", True, ""
' all 4 COOP queries will go into \\sharedncs_hrd _server\hrd\wor kfile\shared\ta _staff\Disaster Plans\CurrentDu tyStationCounts .xls
' qryCOOP_EmpCT_A DR_State
DoCmd.TransferS preadsheet acExport, 8, "qryCOOP_EmpCT_ ADR_State", "\\sharedncs_hr d_server\hrd\wo rkfile\shared\t a_staff\Disaste rPlans\CurrentA DRCounts.xls", True, ""
' qryCOOP_EmpCT_A DR_State-County
DoCmd.TransferS preadsheet acExport, 8, "qryCOOP_EmpCT_ ADR_State-County", "\\sharedncs_hr d_server\hrd\wo rkfile\shared\t a_staff\Disaste rPlans\CurrentA DRCounts.xls", True, ""
' qryCOOP_EmpCT_A DR_State-County-City
DoCmd.TransferS preadsheet acExport, 8, "qryCOOP_EmpCT_ ADR_State-County-City", "\\sharedncs_hr d_server\hrd\wo rkfile\shared\t a_staff\Disaste rPlans\CurrentA DRCounts.xls", True, ""
' qryCOOP_EmpN_AD R_State-County-City
DoCmd.TransferS preadsheet acExport, 8, "qryCOOP_EmpN_A DR_State-County-City", "\\sharedncs_hr d_server\hrd\wo rkfile\shared\t a_staff\Disaste rPlans\CurrentA DRCounts.xls", True, ""
' all 4 COOP queries will go into \\sharedncs_hrd _server\hrd\wor kfile\shared\ta _staff\Disaster Plans\CurrentAD RCounts.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_Exce l to ta_staff\bi-rpts\temproster \Fitness.xls
DoCmd.TransferS preadsheet acExport, 8, "qryFitness_Exc el", "\\sharedncs_hr d_server\hrd\wo rkfile\shared\T A_Staff\bi_rpts \TempRoster\Fit ness.xls", False, ""
' rptRegionalDire ctor_CompEarned save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ RegionalDirecto r_CompEarned.pd f
DoCmd.OpenRepor t "rptRegionalDir ector_CompEarne d", acViewNormal, "", ""
DoCmd.Close acReport, "rptRegionalDir ector_CompEarne d"
' rptRegionalDire ctor_CompBalanc es save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ RegionalDirecto r_CompBalances. pdf
DoCmd.OpenRepor t "rptRegionalDir ector_CompBalan ces", acViewNormal, "", ""
DoCmd.Close acReport, "rptRegionalDir ector_CompBalan ces"
' rptActionsShowi ngRetirementCod e save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ ActionsShowingR etCode
DoCmd.OpenRepor t "rptActionsShow ingRetirementCo de", acViewNormal, "", ""
DoCmd.Close acReport, "rptActionsShow ingRetirementCo de"
' rptPromosHQ save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ PromosHQ.pdf email Veronica Legrade, Mary Kennedy, Jennifer McDaniel, April Davis, Rona Scarlett, Peggy Dillon, Gail Smith
DoCmd.OpenRepor t "rptPromosH Q", acViewNormal, "", ""
DoCmd.Close acReport, "rptPromosH Q"
' rptNonCitizen save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ qryNonCitizen.p df
DoCmd.OpenRepor t "rptNonCitizen" , acViewNormal, "", ""
DoCmd.Close acReport, "rptNonCiti zen"
' rptNegSickLeave HQ save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ NegSickLeaveHQ. pdf
DoCmd.OpenRepor t "rptNegSickLeav eHQ", acViewNormal, "", ""
DoCmd.Close acReport, "rptNegSickLeav eHQ"
' rptSupvlist save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ rptSupvlist.pdf
DoCmd.OpenRepor t "rptSupvlis t", acViewNormal, "", ""
DoCmd.Close acReport, "rptSupvlis t"
' rptMixtour save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ rptMixtour.pdf
DoCmd.OpenRepor t "rptMixtour ", acViewNormal, "", ""
DoCmd.Close acReport, "rptMixtour "
' rptLwoprif save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ rptLwoprif.pdf
DoCmd.OpenRepor t "rptLwoprif ", acViewNormal, "", ""
DoCmd.Close acReport, "rptLwoprif "
' rptClassep save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ rptClassep.pdf
DoCmd.OpenRepor t "rptClassep ", acViewNormal, "", ""
DoCmd.Close acReport, "rptClassep "
' rptTraine save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ rptTraine.pdf - tells supervisory probational period employees
DoCmd.OpenRepor t "rptTraine" , acViewNormal, "", ""
DoCmd.Close acReport, "rptTraine"
' rptTraine_BEA save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ rptTraine_BEA.p df - tells supervisory probational period employees
DoCmd.OpenRepor t "rptTraine_BEA" , acViewNormal, "", ""
DoCmd.Close acReport, "rptTraine_ BEA"
' rptTraine_ESA save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ rptTraine_ESA.p df - tells supervisory probational period employees
DoCmd.OpenRepor t "rptTraine_ESA" , acViewNormal, "", ""
DoCmd.Close acReport, "rptTraine_ ESA"
' rptPmsomath save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ rptPmsomath.pdf
DoCmd.OpenRepor t "rptPmsomat h", acViewNormal, "", ""
DoCmd.Close acReport, "rptPmsomat h"
' rptREIM_over100 0_week1 save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ qryREIM_over100 0_week1.pdf to Julie Tayman
DoCmd.OpenRepor t "rptREIM_over10 00_week1", acViewNormal, "", ""
DoCmd.Close acReport, "rptREIM_over10 00_week1"
' rptREIM_over100 0_week2 save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ qryREIM_over100 0_week2.pdf to Julie Tayman
DoCmd.OpenRepor t "rptREIM_over10 00_week2 ", acViewNormal, "", ""
DoCmd.Close acReport, "rptREIM_over10 00_week2 "
' rptLWOPTA save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ rptLWOPTA.pdf
DoCmd.OpenRepor t "rptLWOPTA" , acViewNormal, "", ""
DoCmd.Close acReport, "rptLWOPTA"
' rptState_addres s_tax_diff save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ rptState_addres s_tax_diff.pdf to Cindy Borza
DoCmd.OpenRepor t "rptState_addre ss_tax_diff ", acViewNormal, "", ""
DoCmd.Close acReport, "rptState_addre ss_tax_diff "
' rptState_addres s_tax_diff_BEA save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ rptState_addres s_tax_diff_BEA. pdf
DoCmd.OpenRepor t "rptState_addre ss_tax_diff_BEA ", acViewNormal, "", ""
DoCmd.Close acReport, "rptState_addre ss_tax_diff_BEA "
' rptState_addres s_tax_diff_ESA save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ rptState_addres s_tax_diff_ESA. pdf
DoCmd.OpenRepor t "rptState_addre ss_tax_diff_ESA ", acViewNormal, "", ""
DoCmd.Close acReport, "rptState_addre ss_tax_diff_ESA "
' rptUnion deducations-BUS 7777 or 8888 save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ rptUnion deducations-BUS 7777 or 8888.pdf - Julie
DoCmd.OpenRepor t "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_r pts\TempRoster\ rptUnion DutyStation 241520 Union 2782.pdf - Julie
DoCmd.OpenRepor t " rptUnion DutyStation 241520 Union 2782 ", acViewNormal, "", ""
DoCmd.Close acReport, "rptUnion DutyStation 241520 Union 2782 "
' rptTickleforPri nt BEA save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ rptTickleforPri ntBEA.pdf
DoCmd.OpenRepor t "rptTickleforPr int BEA", acViewNormal, "", ""
DoCmd.Close acReport, "rptTickleforPr int BEA"
' rptBSeparateBEA save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ rptBSeparateBEA .pdf
DoCmd.OpenRepor t "rptBSeparateBE A", acViewNormal, "", ""
DoCmd.Close acReport, "rptBSeparateBE A"
' NEWCDBEA save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ NEWCDBEA.pdf
DoCmd.OpenRepor t "NEWCDBEA", acViewNormal, "", ""
DoCmd.Close acReport, "NEWCDBEA"
' rptbSeparateESA save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster
DoCmd.OpenRepor t "rptbSeparateES A", acViewNormal, "", ""
DoCmd.Close acReport, "rptbSeparateES A"
' rptTickleforPri nt ESA save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ rptTickleforPri nt ESA.pdf
DoCmd.OpenRepor t " rptTickleforPri nt ESA", acViewNormal, "", ""
DoCmd.Close acReport, "rptTickleforPr int ESA"
' NEWCDESA save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoste\N EWCDESA.pdf
DoCmd.OpenRepor t "NEWCDESA", acViewNormal, "", ""
DoCmd.Close acReport, "NEWCDESA"
' SepInfo ESA save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ SepInfo ESA.pdf
DoCmd.OpenRepor t "SepInfo ESA", acViewNormal, "", ""
DoCmd.Close acReport, "SepInfo ESA"
' rptNegSickLeave ESA save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ rptNegSickLeave ESA.pdf
DoCmd.OpenRepor t "rptNegSickLeav eESA", acViewNormal, "", ""
DoCmd.Close acReport, "rptNegSickLeav eESA"
' rptBEA_COMP_Ful lReport 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 Jack.Jetmund@be a.gov
DoCmd.OpenRepor t "rptBEA_COMP_Fu llReport", acViewNormal, "", ""
DoCmd.Close acReport, "rptBEA_COMP_Fu llReport"
' rptStimulusJobs _Count save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ rptStimulusJobs Count.pdf
DoCmd.OpenRepor t "rptStimulusJob s_Count", acViewNormal, "", ""
DoCmd.Close acReport, "rptStimulusJob s_Count"
' rptStimulus_PPA ctions_Names save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ rptStimulusPPAc tionsNames.pdf
DoCmd.OpenRepor t "rptStimulus_PP Actions_Names", acViewNormal, "", ""
DoCmd.Close acReport, "rptStimulus_PP Actions_Names"
' rptStimulus_PPA ctions_Names_3_ Details save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ rptStimulusPPAc tionsNames_3_De tails.pdf
DoCmd.OpenRepor t "rptStimulus_PP Actions_Names_3 _Details", acViewNormal, "", ""
DoCmd.Close acReport, "rptStimulus_PP Actions_Names_3 _Details"
' qryStimulus_ARR A_Employees_2 saves auto to workfile\shared \tA_staff\bi_rp ts\temproster\S timulus_ARRA_Em ployees.xls
DoCmd.TransferS preadsheet acExport, 8, "qryStimulus_AR RA_Employees_2" , "\\sharedncs_hr d_server\hrd\wo rkfile\shared\t a_staff\bi_rpts \temproster\Sti mulus_ARRA_Empl oyees.xls", False, ""
' rptState_addres s_tax_diff_HQno tMDDCVA save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ rptState_addres s_tax_diff_HQno tMDDCVA.pdf
DoCmd.OpenRepor t "rptState_addre ss_tax_diff_HQn otMDDCVA", acViewNormal, "", ""
DoCmd.Close acReport, "rptState_addre ss_tax_diff_HQn otMDDCVA"
' rptDUTY_Home_ad dress_diff_FRs save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ rptDUTY_Home_ad dress_diff_FRs. pdf
DoCmd.OpenRepor t "rptDUTY_Home_a ddress_diff_FRs ", acViewNormal, "", ""
DoCmd.Close acReport, "rptDUTY_Home_a ddress_diff_FRs "
' rptHQ_State_tax _notMDDCVA save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ rptHQ_State_tax _notMDDCVA.pdf
DoCmd.OpenRepor t "rptHQ_State_ta x_notMDDCVA", acViewNormal, "", ""
DoCmd.Close acReport, "rptHQ_State_ta x_notMDDCVA"
' rptLCO_Mgr_Coun t save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ rptLCO_Mgr_Coun t.pdf
DoCmd.OpenRepor t "rptLCO_Mgr_Cou nt", acViewNormal, "", ""
DoCmd.Close acReport, "rptLCO_Mgr_Cou nt"
' rptAnnualLeaveC at_0 save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ rptAnnualLeaveC at_0
DoCmd.OpenRepor t "rptAnnualLeave Cat_0", acViewNormal, "", ""
DoCmd.Close acReport, "rptAnnualLeave Cat_0"
' rptAnnualLeaveC at_0_BEA save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ rptAnnualLeaveC at_0_BEA
DoCmd.OpenRepor t "rptAnnualLeave Cat_0_BEA", acViewNormal, "", ""
DoCmd.Close acReport, "rptAnnualLeave Cat_0_BEA"
' rptAnnualLeaveC at_0_ESA save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ rptAnnualLeaveC at_0_ESA
DoCmd.OpenRepor t "rptAnnualLeave Cat_0_ESA", acViewNormal, "", ""
DoCmd.Close acReport, "rptAnnualLeave Cat_0_ESA"
' rptCOLA save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ rptCOLA.pdf
DoCmd.OpenRepor t "rptCOLA", acViewNormal, "", ""
DoCmd.Close acReport, "rptCOLA"
' qryRoster_Reg-TP-Detail_HRD_2 export Excel to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ 2010Roster_14-HRD.xls
DoCmd.TransferS preadsheet acExport, 8, "qryRoster_ Reg-TP-Detail_HRD_2", "I:\bi_rpts\Tem pRoster\2010Ros ter_14-HRD.xls", True, ""
' FOR DANIELLE TO POST ON THE WEB
' qryOrg16tbl active to WEB_Census_2 (no dummies) g:\workfile\sha red\systems\dan ielle\orgstruct ure_Census.html
DoCmd.TransferT ext acExportHTML, "", "qryOrg16tb l active to WEB_Census_2 (no dummies)", "\\sharedncs_hr d_server\hrd\wo rkfile\shared\s ystems\danielle \orgstructure_C ensus.html", False, ""
' to get rid of other dummies add to tblOrg16 Active Dummies
' qryOrg16tbl active to WEB_BEA g:\workfile\sha red\systems\dan ielle\orgstruct ure_BEA.html
DoCmd.TransferT ext acExportHTML, "", "qryOrg16tb l active to WEB_BEA", "\\sharedncs_hr d_server\hrd\wo rkfile\shared\s ystems\danielle \orgstructure_B EA.html", False, ""
' qryOrg16tbl active to WEB_ESA g:\workfile\sha red\systems\dan ielle\orgstruct ure_ESA.html
DoCmd.TransferT ext acExportHTML, "", "qryOrg16tb l active to WEB_ESA", "\\sharedncs_hr d_server\hrd\wo rkfile\shared\s ystems\danielle \orgstructure_E SA.html", False, ""
' qryGG_GH_GM_Gr_ 15_CENSUS TO \\sharedncs_hrd _server\hrd\wor kfile\SHARED\pp sb\HTM_Reports\ GR15_GG-GH-GM_CENSUS.XLS
DoCmd.TransferS preadsheet acExport, 8, "qryGG_GH_GM_Gr _15_CENSUS", "\\sharedncs_hr d_server\hrd\wo rkfile\SHARED\p psb\HTM_Reports \GR15_GG-GH-GM_CENSUS.XLS", True, ""
' qryGG_GH_GM_Gr_ 15_ESA TO \\sharedncs_hrd _server\hrd\wor kfile\SHARED\pp sb\HTM_Reports\ GR15_GG-GH-GM_ESA.XLSR15_G G-GH-GM.XLS
DoCmd.TransferS preadsheet acExport, 8, "qryGG_GH_GM_Gr _15_ESA", "\\sharedncs_hr d_server\hrd\wo rkfile\SHARED\p psb\HTM_Reports \GR15_GG-GH-GM_ESA.XLS", True, ""
' qryBranchCounts _Biweekly_JeffM iller to \\sharedncs_hrd _server\hrd\wor kfile\SHARED\PE B\access\pdfrep orts\BranchCoun ts.xls
DoCmd.TransferS preadsheet acExport, 8, "qryBranchCount s_Biweekly_Jeff Miller", "\\sharedncs_hr d_server\hrd\wo rkfile\SHARED\P EB\access\pdfre ports\BranchCou nts.xls", True, ""
' qrypaypers_npc_ comma to \\prodncs_hrd_s erver\hrd\hrd\f ocus\database\p aypers_npc_comm a.txt
DoCmd.TransferT ext acExportDelim, "", "qrypaypers_npc _comma", "\\prodncs_hrd_ server\hrd\hrd\ focus\database\ paypers_npc_com ma.txt", False, ""
' qrypaypers_npc_ comma to \\npcoacluster. npc.census.gov\ hrb ecs staff\hrb ecs reports\access2 000\pdf_reports \paypers_npc_co mma.txt
DoCmd.TransferT ext acExportDelim, "", "qrypaypers_npc _comma", "\\npcoacluster .npc.census.gov \hrb ecs staff\hrb ecs reports\access2 000\pdf_reports \paypers_npc_co mma.txt", False, ""
' rptEOD_ALaccrua ls save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ EOD_Accruals_pp .pdf
DoCmd.OpenRepor t "rptEOD_ALaccru als", acViewNormal, "", ""
' rptNOA_882_Trac king save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ SCD_Actions_pp. pdf
DoCmd.OpenRepor t "rptNOA_882_Tra cking", acViewNormal, "", ""
' NPC PDF REPORTS
' rptRotary_NPC_P DF save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ ROSTER_NPC.PDF
DoCmd.OpenRepor t "rptRotary_NPC_ PDF", acViewNormal, "", ""
' rptRotary_NPC_S SNO_PDF save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ ROSTER_NPC_SSNO .PDF
DoCmd.OpenRepor t "rptRotary_NPC_ SSNO_PDF", acViewNormal, "", ""
' rptTickleforPri nt_NPC_PDF save pdf to workfile\shared \T&A_Staff\bi_r pts\TempRoster\ Tickle_NPC.PDF
DoCmd.OpenRepor t "rptTickleforPr int_NPC_PDF", acViewNormal, "", ""
' OPF ACCESSION LABELS - KEEP THESE LAST REPORT WILL SHOW ON SCREEN -MANUALLY FEED AVERY 5160 LABELS IN PRINTER
' rptAccLabels_Ce nsus-1798
DoCmd.OpenRepor t "rptAccLabels_C ensus-1798", acViewPreview, "", ""
' rptAccLabels_BE A+ESA
DoCmd.OpenRepor t "rptAccLabels_B EA+ESA", acViewPreview, "", ""
' rptTempEmp_FEHB CovElig_address Labels give to PPSB for Breezy Cornio this should relate to a NFC report they print off the same biweekly Monday.
DoCmd.OpenRepor t "rptTempEmp_FEH BCovElig_addres sLabels", acViewPreview, "", ""
DoCmd.Echo True, ""
DoCmd.SetWarnin gs True


mcrBiweeklyRepo rts_PDF_inForm_ Exit:
Exit Function

mcrBiweeklyRepo rts_PDF_inForm_ Err:
MsgBox Error$
Resume mcrBiweeklyRepo rts_PDF_inForm_ Exit


Carol

[patjones]

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

Dim fd As FileDialog
Dim strFolder As String
 
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
 
If fd.Show = -1 Then
    strFolder = fd.SelectedItems.Item(1)
Else
    Exit Sub
End If
 
DoCmd.OutputTo acOutputReport, "Report Name", acFormatPDF, strFolder & "\" & Report.[Report Name].Caption & ".PDF"
 
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.

Pat

[cmcsween]

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.

Carol

[patjones]

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...

Pat

[cmcsween]

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.OpenRepor t .... and brings up the same dialog box as it does in the macro?

Carol