corporate property automated information system (cpais) macro walkthrough guide for excel version...

Corporate Property Automated Information

System (CPAIS) Macro Walkthrough Guide

for Excel Version 2003

Once you have logged into CPAIS select the “User Views” tab

CPAIS Macro Walkthrough

1

Select the module that you would like to run


2

Additional Modules can be found on the next page by selecting the “Next 5” Link

Option 2 – If you only have access to your own data (or want the data output for multiple agencies) than Select “Execute Query”

Option 1 - If you have access to more than one agency’s data or need to specify a year then you will need to specify the agency and year that you want. Continue to the next slide for instruction on this.


3

To specify your agency copy the example shown here

Agencies are listed as a number in CPAIS and not by name

To specify your agency and a year copy the example shown here

Use whichever data field pertains to your report (e.g. Annual Operating Cost Year instead of Facility Condition Year)


4

Right click on the file and select “Save Target As” and continue on to the next slide


5

Select the location that you would like the data downloaded to and click “Save”


6

Download the CPAIS Macro Workbook from

https://www.nfc.usda.gov/corporate/CPAIS.ht

m

It is recommended that you save the file on your computer’s desktop for easy access


7

Open the CPAIS Macro Workbook. Choose the file that corresponds to your version of Excel (2003 or 2007).


8

Upon first opening the Macro Workbook you will be required to change your Macro Security Settings and should see this pop-up box

Click “OK” then proceed to the next slide


9

Step 1 Select “Tools”

Step 2 Select “Macro” Step 3 Select

“Security” then continue to the next slide


10

Step 1 Change your security level to “Medium” then click “OK”

Step 2 Save your workbook

Step 3 Close the workbook

Step 4 Re-open your workbook and continue to the next slide


11

Select “Enable Macros” then continue to the next slide


12

Select the report you wish to run by clicking on the corresponding button

Continue to the next slide.


13

Make sure you have downloaded the correct corresponding file from CPAIS

Click “OK” and move on to the next slide


14

You will be asked to select the location of the previously downloaded CPAIS data file on the next screen

Select “OK” and continue on to the next slide


15


Step 1 Select the correct file

Step 2 Select “Open” and continue on to the next slide

16

Next you will be asked to choose where you would like your completed report to be saved. This will be a separate file from your original data sheet.

Select “OK” and continue on to the next slide


17

Step 1 Select the correct spot where you would like your completed file to be placed

Step 2 Select “Save” and continue on to the next slide


18

Your report has been completed and saved to your specified location

Click “OK” and move on to the next slide


19

Navigate to the folder on your computer where you chose to save your report. The completed file will be there waiting for you.

Open the file and continue on to the next slide


20

Notice that there are three different tabs in your report file. The first is the “Details” tab which contains the raw data. Some reports will color code certain data as seen here.

Click the “Legend” tab and move on to the next slide


21


Not all reports include a

“Legend” tab. This tab is where you will find the key for the color coding done on the “Details” tab.

Select the “Summary” tab and continue to the next slide

22

The “Summary” tab has the rolled up data shown in a Pivot Table

Data can be filtered using the drop down boxes seen here

Totals will be updated in real time with the application of any filters


23

corporate property automated information system (cpais) macro walkthrough guide for excel version...

Documents