Option B: EXCEL PRL eFORM USER GUIDE TABLE OF CONTENTS: ----------------- 1) Overview 2) Installation 3) Starting The Program 4) Registration Form 5) Using The Spreadsheets - Overview 6) Epa Registration Number Button 7) Saving Files & File Naming 8) How To Use "Email Report To Nysdec Via Internet" Feature 9) Report Submission 10) Contact Info APPENDIX A) File Naming ----------- 1) OVERVIEW: ----------- The Excel eForms are designed to assist you in electronically recording and reporting Pesticide Reporting Law (PRL) information to the NYS Department of Environmental Conservation. Each time you run an eForm you are asked to enter or verify some basic contact information. You can then view and fill out electronic forms that are similar to the four paper forms used to report pesticide usage and sale. --------------- 2) INSTALLATION: --------------- To install the Excel eForms, run "optionB\setup.exe" from the CD Rom or optionB.exe if you downloaded the files. If you've installed the software previously, you must uninstall the existing version first. Go to the START menu; choose Settings -> Control Panel -> Add/Remove Programs. In the Add/ Remove dialog, navigate to optionB and click on it. Then click the Change/Remove button and follow the prompts. ---Vista Operating System---: To install the software on computers running the Vista opertating system, log on as administrator or with administrator rights. After logging on, right click on the installation file and select "Run As Administrator". Contact your system administrator or PSUR (see Contact Info section) if further assistance is needed. Continue installing the software in accordance with the instructions above or see the readme.txt file that is installed in: C:\Program Files\NYSPRL\OptionB NOTE: You must have MicroSoft Excel 97 or higher to use the eForms. ----------------------- 3) STARTING THE PROGRAM: ----------------------- To start the program double-click on the PRL_OptionB icon that was created on your Desktop by the installation program. You can also run the program by clicking on the START button and navigating to Programs / Pesticide Reporting / PRL_OptionB (If you used the default settings when you installed the program, otherwise look in the program group you used). Another option is to run the program PRL_OptionB.exe which is by default installed in "C:Program Files\NYSPRL\optionB\" (you may have changed this setting during installation to another folder). -------------------- 4) REGISTRATION FORM: -------------------- When you open the program, you will be asked for contact and registration information. You are required to fill in a contact name, phone number, email address and at least one of the following: -Certification ID Number -Commercial Permit Number. Commercial Permit Numbers always begin with 'R' and Certification ID Numbers begin with 'A','C' or 'T' (private applicators are not required to report). Most business have only one or two of these ID numbers. You may also enter a Business Registration ID number, but this is not a required field for any of the reporting forms. However it may facilitate locating your report should you have questions after you have submitted it. If you enter a Certification ID Number, you must also enter the applicator's name. If you enter a Commercial Permit or Business Registration ID Number, you must enter the business name. The information on the registration screen does not need to be changed to report for multiple applicators. Use eForm 26AE (List of Applicators and Technicians) to report additional applicators. This program can be used for multiple reporting years. From September 1 through the end of the year the report year defaults to the current year. During the remainder of the year, it defaults to the previous year. To change the year, click on the Change Year button. You will be prompted for a new report year (enter the four digit year), the program will then ask you to confirm the change. Once the year is changed, it will become the year used for all subsequent work. After you change the reporting year, a new directory will be created to store your report files (default directory: C:\prlYYYY, where "YYYY" = current report year). The software does not overwrite report files created for previous years. Each time you go from the initial screen (the registration form) into a report form, the appropriate registration information will be copied into the header area of the new form automatically. For Form 26E the Certification ID from the registration screen is placed in the Certification ID field by default. On Forms 25E and 27E, the Commercial Permit Number from the registration screen is used. The business registration number is copied over for form 26AE . The applicator name and certification number are also copied into the first row of a blank form 26AE. After they have been moved over, the user can change these fields by positioning the mouse in the field and entering the new value. This new value will be associated with all the rows on the form when the file is saved. Changing an ID on the form will not change the value entered on the Registration form. ------------------------------------ 5) USING THE SPREADSHEETS - OVERVIEW: ------------------------------------ IMPORTANT: The Excel eForm templates rely on the use of MACROS. You may be asked if you want to enable Macros when you open these forms. ALWAYS turn Macros on when using this software. If you do not say Yes, most of the features within the templates will not operate. If the program does not ask you to enable Macros at startup or if you have additional questions, see the following page on the web site for more information about enabling Macros: http://www.nysprl.com/macro_instrs.htm or click on the Macro Security Setting button on the registration form. After entering the required information on the registration form, check one of the form types in the "Select a form to open" box. Next click the Blank Form button to create a new PRL form of that form type or the Existing Form button to open a previously created PRL form. Please make sure you only open and use the form types that apply to your specific pesticide activity. The form types are: -> Form 44-15-26E (Applicator/Technician Pesticide Annual Report) -> Form 44-15-26AE (List of Commercial Applicators and Technicians) -> Form 44-15-27E (Annual Report for Pesticide Sales to Certified Private Applicators) -> Form 44-15-25E (Annual Report for Restricted Pesticides Sales) As of version 3.0.3 (released Nov. 6, 2007), the program no longer checks to see if other files have been created for the registration information. However you may change the file name when you save the form if you do not want to overwrite an exisiting file. Before entering any data, please read and follow the NYSDEC instructions for the specific eForm(s) that applies to your pesticide activity. These instructions explain the requirements for proper completion of each type of PRL eForm. Click the eGuidelines button on the report form to view these documents at any time. The documents are also installed on your computer. See: "C:\prlYYYY\optionA\eGuidelines25E.txt" "C:\prlYYYY\optionA\eGuidelines26E.txt" "C:\prlYYYY\optionA\eGuidelines26AE.txt" "C:\prlYYYY\optionA\eGuidelines27E.txt" Legend: YYYY = current report year Start by entering the required data in the form fields. In general, all fields should be filled out. In many places throughout the program, if you pause your mouse pointer over an area of interest, hints about a field or column and its intended purpose will be displayed. Many of the features of the Excel eForms are common to any spreadsheet program. Cut, Paste, Copy and Delete can be used to edit individual cells (use of the words "ditto" or "same", or use of ditto marks will not be accepted). There are also Copy Row and Delete Selected Row/s buttons. To use the Copy Row feature, position the mouse on the row you wish to copy and click the Copy Row button. The row is copied to the first empty row in the spreadsheet. To use the Delete Selected Row/s button, select any column in the row or rows that you want to delete. Then click the button. The "units" column on forms 25E, 26E and 27E has a drop down list for easy entry of units. When you click in this column, a box will appear with a scrollable arrow on the right side. By clicking the arrow with your mouse, you can scroll down and select the appropriate unit of measurement for your product. The "county code" column on forms 26E and 27E has a pop-up window which contains all the NY counties and their codes. Simply click the appropriate county with your mouse and the county code will be filled in on the sheet. You may deactivate this feature by clicking the Disable this Pop-Up button, which is located on the pop-up window. The pop-up will be disabled until you close the form. Also located on the pop-up is a button that allows you to set a default value each time you click in the county code field. Just click the Use Default button and enter a default value. The value will be inserted into the county field and the pop-up will not display. Date columns must be entered in one of these formats: MM/DD/YYYY, MM-DD-YYYY, MM-DD or MM/DD where MM=month, DD=day, YYYY & YY=year. If the year is omitted, the application will use the year from the CURRENT date which may be different than the REPORTING YEAR. When you make an improper entry on the report forms, you will be given an appropriate error message and the cursor will be returned to the field in error. --------------------------------- 6) EPA REGISTRATION NUMBER BUTTON: --------------------------------- Forms 25E, 26E and 27E, have a button at the bottom of the window entitled EPA Reg. Number. When you click on this button, the EPA Number Maintenance Form appears. This form allows you to enter the EPA Registration Number and Product name for up to 50 pesticides that you commonly use. To add an entry to the list, click the Add button. You will be prompted to enter an EPA Number. Please note that all EPA Registration Numbers are separated by hyphens/dashes i.e.: 3125-474-538 or 3125-474. After you are finished, click Ok, and you will then be prompted to enter a Product name. When finished, click Ok and the program will add this pesticide to the list. Continue adding EPA Numbers to the list or click the "Save, Back to Report" button. Once you return to your report form, you will see the list of EPA numbers and products displayed at the top of the report form. To use the list, click in a column in the row you want populated with the EPA number; then click on any pesticide in the list. The EPA Reg. Number and Product name will appear in the appropriate columns of the row you clicked in. The list may be edited at any time by clicking on the EPA Reg. Number button. Highlight the product you would like to edit, and then click Edit. You can then change the information. If you make any changes to the list that you do not want activated, you can simply click the "Quit w/o Saving" button. ---------------- 7) SAVING FILES: ---------------- To save the file after data has been entered, click on the Save icon in the toolbar or go to Save on the File menu. Each time you click Save, the information you have entered will be written to disk as a text file in the proper submission format. Also created will be a file called identity.txt (you must include this file with your submitted files). This file contains the information you entered into the registration form. When you click Save, the Save As window is displayed. While it is possible to change to another directory, it is best if you keep all of your files in the default PRL data directory, "C\prlYYYY" (where YYYY is the reporting year, 2007 for example). Then, when it comes time to submit the data to NYSDEC, you can simply copy the data from this one location. "C\prlYYYY" is where the files will be saved initially if you do not change to another directory. Each time you save a form, the program will prefill the File Name box on the Save As window with a name based on the form type and key field (Certification ID or Commercial Permit Number depending on the form type). If a file with this name already exists and you do not want to overwrite it, you must change the default file name. If you want to change the file name, it is important to retain the first letter (A, M, P or S) of the default name. This identifies to us the form type that you are submitting. For a complete explanation of the file naming convention see Appendix A - FileNaming. If a file with the same name already exists, you will be prompted with a warning message after trying to save the file. When saved, the file is stored in the directory "C\prlYYYY\" where YYYY is the reporting year (2007 for example). ------------------------------------------------------------ 8) HOW TO USE "EMAIL REPORT TO NYSDEC VIA INTERNET" FEATURE: ------------------------------------------------------------ After clicking the "Email Report To Nysdec Via Internet" button, a warning message is displayed. The message informs you that the email feature may not work in some network environments because of the environment's security features or perhaps your computer is not on the Internet. If you have any trouble using this feature you may simply attach the files to an email and send them to us that way (see below for more info). Next you will be prompted by "Did you save your files in the PRLYYYY directory?": Yes or No. This is the default directory where the program stores your files unless you have changed it by choosing another location. If you answer No and you are not running the Office 97 version, then a list of directories in the standard Windows format will display. Simply navigate to the directory where your files are located, select the directory and click Done. If you answer No and you are running option B for office 97, then you will be prompted to "Enter path in the form c:\xxx\xxx". You should enter the path to the folder containing your report files. Do not enter any file names, just the path to the files ("C:\myfiles\decreports" for example). After you've responded to the "Did you save..." dialog, the NYSPRL email form will display. There are four steps to follow on the form: 1. You will see a list of files with check boxes next to them. Check the files you wish to include with the email. Please review the "PRL File Names" description in the text box at the right side of the window. Submission of invalid file names may cause delays in processing your report. 2. Enter your email address in the designated text box. 3. If you have any notes or messages you would like to include with the email, enter them in the indicated box. We suggest that you include your business name and business or permit number if you have one or, if you're reporting for a single applicator, the applicator name and certification number. Also please include a contact name and phone number. 4. Click on the Send button. A progress bar will display followed by a message indicating the results of sending the email (Success or Failure). If the transmission should fail for some reason, please use one of the other submission methods listed below (see Report Submission section). -------------------- 9) REPORT SUBMISSION: -------------------- After you have finished entering your data into the forms, they can be submitted to NYSDEC along with the REQUIRED FILE "C:\prlYYYY\identity.txt" (where YYYY is the Reporting Year, 2007 for example). This file contains your registration information. Without it, we may not not able to recognize which business or applicator filed the report and therefore may not be able to credit the business for having filed. Your report can be transferred to DEC by e-mail, diskette, CD-ROM, FTP protocol or using the "Email Report To Nysdec Via Internet" feature of the software (see previous section). The email address for filing reports is prlfiles@nysprl.com. If you desire to submit data via FTP, please contact the Help Desk for instructions and connection information: 1-800-281-7538 toll free within New York State 1-607-257-5706 toll call outside New York State. Please mail any physical media to NYSDEC at: NYSDEC Pesticide Reporting Section P.O. Box 10699 12201-5699 Each diskette or CD-ROM sent out should include a label with the full name of the certified applicator and/or business of origin and a contact phone number. PLEASE NOTE: ALL SUBMISSIONS MUST ALSO CONTAIN THE IDENTITY TEXT FILE (IDENTITY.TXT) THAT WAS CREATED FOR YOU FROM THE REGISTRATION SCREEN WITHIN THE SOFTWARE. THIS FILE IS LOCATED IN THE "C:\PRLYYYY\IDENTITY.TXT" (WHERE YYYY IS THE REPORTING YEAR, 2007 FOR EXAMPLE) DIRECTORY. ---------------- 10) CONTACT INFO: ---------------- We have tried to make these eForm templates as easy to use as possible. If you encounter problems installing or using them, please call the electronic reporting Help Desk at PSUR, the contractor who will process electronic submissions. PSUR will return your call and assist you in installing and using the electronic reporting options. For more information, please contact PSUR by phone: 1-800-281-7538 (toll-free in New York State) 1-607-257-5707 (a toll call) outside of New York State or contact them by e-mail at: prlsupport@nysprl.com You can also view additional information at the PRL web page: http://www.nysprl.com The site's Frequently Asked Questions section may be useful: http://www.nysprl.com/NYSPRL-FAQ.html To receive the electronic filing specifications by US mail or for assistance in understanding and complying with Pesticide Reporting Laws, please contact the Pesticide Reporting Section of NYSDEC at 1-518-402-8748. You may also contact NYSDEC by E-mail at prl@gw.dec.state.ny.us Thank you for using the new PRL eForms. ------------------------ APPENDIX A) FILE NAMING: ------------------------ By default, the name given to a new file follows the naming convention described below. The pattern used for the file names is Form Prefix + ID number + sequence number of "00" + extension of ".xls". For example, a Form 26 for an applicator with ID number C1234567 is given the default name of PC123456700.xls. The Form Prefix (first letter of the name) represents the type of form and is defined as follows: P - Form 44-15-26E(Applicator/Technician Pesticide Annual Report) A - Form 44-15-26AE (List of Commercial Applicators and Technicians) S - Form 44-15-27E (Annual Report for Pesticide Sales to Certified Private Applicators) M - Form 44-15-25E (Annual Report for Restricted Pesticides Sales) The next 5 to 8 characters of the generated file name are ID numbers which represent either your Registered Business number, Certification Number or Commercial Permit Number. The next two digits are a unique sequence number between 00 an 99. The last part is the extension of ".xls". Some examples would be: PC555555501.xls, SR3333301.xls, MR3333302.xls While using these templates, the file names will be generated automatically for you. If you choose to change the file names and do not follow the above guidelines, your electronic documents may not be acceptable by NYSDEC and may be returned. If the user needs to change the file name, it is recommended that only the sequence number of 00 is changed. Using our example, PC123456700.xls would be changed to PC123456701.xls. NEVER change the Form Prefix. Retaining the ID portion of the name is also very important. If a file with the same name already exists, the user will be prompted with a warning message.