You manage a small animal shelter in Bowling Green, Ohio. Your assistant created an XML document…
You manage a small animal shelter in Bowling Green, Ohio. Your assistant created an XML document that lists some of the recent small animals that your shelter took in. In particular, the XML document lists the animal type (such as cat); the age, sex, name, color, date the animal was brought in; and the date the animal was adopted. You want to manage the data in an Excel worksheet, so you will create a link to the original data source in case it changes.
a. Use Windows to copy the e09m3animals.xml file, and then rename the copied file e09m3animals_LastnameFirstname.xml.
b. Start a new workbook and save it as e09m3animals_LastnameFirstname.
c. Import the XML document e09m3animals_LastnameFirstname.xml into Sheet1 of the workbook. Rename Sheet1 Animals.
d. Create a PivotTable from the imported data, placing the animal type and sex as row labels and counting names as values. Use the adoption date as a report filter and set a filter to show those animals that have not been adopted. Rename the PivotTable worksheet PivotTable. Delete the blank worksheets.
e. Open e09m3animals_LastnameFirstname.xml in Notepad. Edit the XML document by adding 3/25/2012 for the adoption date for Paws the cat. Edit Fido’s data by entering his age: 6 months. Edit Twerpy’s color as Orange. Edit Misty’s adoption date of 3/31/2012. Save the XML file, and then close Notepad.
f. Display the Animals worksheet in Excel, and then refresh the connection. Display the PivotTable worksheet, and then refresh the PivotTable.
g. Create a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side of each worksheet.
h. Save and close the workbook, and submit based on your instructor’s directions.