spreadsheets application

/in /by

Module 13 Objectives:

  • Plan a data exchange
  • Embed a workbook in a Word document
  • Import a text file
  • Import a database table
  • Insert a graphic file in a worksheet
  • Link a workbook to a Word document
  • Link an Excel chart to a PowerPoint slide
  • Import a table into Access

Note: Due to new functionality added to Office 365 during 2017, the illustration on page 301 Figure 3-13 should be replaced with this picture:

Import Window

Instead of steps 3-5 on page 300, just click Load to import the text file.

NOTE: On page 302, step 2, instead of clicking the From Access button on the Data tab, you now need to choose the Get File button, then choose From Database, then click From Microsoft Access Database.

In this chapter will learn how to exchange Excel data with other programs by using and contrasting features such as Importing vs. Exporting, Embedding vs. Linking and Inserting vs. Pasting. Other tips from Marcus:

  1. Pay special attention to the terminology used in this chapter, especially the terms introduced on pg 298. When I use the term OLE, I’m not referring to a side item at Taco Johns!
  2. Read and learn about the different file formats supported by Excel 2016 by referring to the table M-1.
  3. When to use Linking vs. Embedding? For example, if a Word report is needed that contains the total sales data for the month, data could be either embedded or linked. If there is a report for each day, embedding should be used so that each daily report shows total sales through that date. If the data is linked to a single Word report, each time that report is opened it would update to give the current total sales.
  4. A comma is also a popular delimiter character. Many files can be saved in a .CSV format – which means it is a Comma Separated File.
  5. Importing Text vs. Copy/Paste? If you just copy and paste a paragraph of text (from a Notepad file, for example) instead of using the Text Import Wizard, the entire paragraph will be crammed into one cell.
  6. To display a different icon to represent the file, click the Change Icon button in the Object dialog box, scroll down the icon list in the Change Icon dialog box, and select any icon.
  7. WARNING! Embedding can lead to larger file sizes, because one document is placed inside the other. This may necessitate learning how to compress your workbook using a Zip program, such as WinZip to shrink the file size.
  8. When you open a document containing linked data, you are asked if you want to update the linked data. You can manage the updating of links by clicking the File tab, and clicking Edit Links to Files in the right pane. The Links dialog box opens, allowing you to change a link’s update from the default setting of automatic to manual. The Links dialog box also allows you to change the link source, permanently break a link, open the source file, and manually update a link. If you send your linked files to another user, the links will be broken because the linked file path references the local machine where you inserted the links. Because the file path will not be valid on the recipient user’s machine, the links will no longer be updated when the user opens the destination document. To correct this, recipients who have both the destination and source documents can use the Links dialog box to change the link’s source in the destination document to their own machines. Then the links will be automatically updated when they open the destination document in the future.
  9. The default setting for updating links in PowerPoint file is Manual. To update links in an open PowerPoint file, click the File tab, click Edit Links to Files in the right pane, click the link in the Links list, click Update Now, then click Close.
  10. Remember that a primary key cannot be duplicated. That is, if more than one row/record in Excel has the same value for a column/field, that column/field cannot be used as the primary key.

Module 13 Homework: (e-mail me with questions)

  • Independent Challenge #1 – (two uploads) -7pts
  • Independent Challenge #2 (two uploads). In step b, delete the extra column that is imported due to an extra, unnecessary tab in the starting data file. – 5pts BONUS

Module 14 Objectives:

  • Share Excel Files
  • Set up a shared workbook for multiple users
  • Track revisions in a shared workbook
  • Apply and modify passwords
  • Work with XML schemas
  • Import and export XML data
  • Run Web queries to retrieve external data
  • Import and export HTML data

The emphasis in this unit is to learn how incorporate Web information with Excel, apply/modify passwords, and learn how to use XML schemas. Focus less of your time on the Shared Workbook/Revision tracking features. Other tips from Marcus:

NOTE: Due to the removal of the Changes group on the Review tab, the Share Workbooks features on pages 324-325, Track Changes on pages 326-327, and Share Web Links features on pages 334-335 have been replaced by Microsoft in Office 365 in mid-2017. These features now utilize the co-authoring feature to share data. Neither of these features are involved in the assigned homework or on the upcoming exam, so I recommend just skipping these steps. You can choose to add the Track Changes feature back to the ribbon by following these instructions . However, this feature will not be utilized in either assignment or exam.

  1. HTML format is designed to display data and XML format is designed to hold/store data. Excel supports importing from and saving to both formats! Nonetheless, if your desire is to publish Excel info to a web page, consider using the single file web page option (.mht). This feature is much cleaner, and easier to distribute to others.
  2. Instead of putting the shared workbook on a server to be shared simultaneously, you might want to distribute copies to your reviewers via e-mail. Once everyone has entered their changes and returned their workbook copies to you, you can merge the changed copies into one master workbook that contains everyone’s changes. Each copy you distribute must be designated as shared, and the Change History feature on the Advanced tab of the Share Workbook dialog box must be activated. Occasionally a conflict occurs when two users are trying to edit the same cells in a shared workbook. In this case, the second person to save the file will see a Resolve Conflicts dialog box and need to choose Accept Mine or Accept Other. To merge workbooks, you need to add the Compare and Merge Workbooks command to the Quick Access Toolbar by clicking the File Tab, clicking Options, and clicking Quick Access toolbar. Click All Commands in the Choose commands from list, click Compare and Merge Workbooks, click Add, then click OK. Once you get the changed copies back, open the master copy of the workbook, then click the Compare and Merge Workbook button on the Quick Access toolbar. The Select Files to Merge Into Current Workbook dialog box opens. Select the workbooks you want to merge (you can use the [Ctrl] key to select more than one workbook), then click OK.
  3. You can also use a password to encrypt the contents of a workbook by clicking the File tab, clicking Protect Workbook in the middle pane, clicking Encrypt with Password, and entering a password.
  4. If the Developer tab does not appear click the File tab, click Options, click Customize Ribbon, and select the Developer check box.
  5. XML is a technology that is designed for managing and sharing structured data in a human-readable text file. XML follows industry-standard guidelines and can be processed by a variety of databases and applications. Using XML, application designers can create their own customized tags, data structures, and schemas. In short, XML greatly eases the definition, transmission, validation, and interpretation of data between databases, applications, and organizations.
  6. More info on using XML and Excel: http://www.addictivetips.com/microsoft-office/excel-2010-working-with-xml-format/.
  7. Excel works primarily with two types of XML files:
    • XML data files (.xml), which contain the custom tags and structured data.
    • Schema files (.xsd), which contain schema tags that enforce rules, such as data type and validation.
  8. When you open a Microsoft Excel 2013 workbook in the browser, the workbook opens in a Web-based viewer. This viewer supports most features of an Excel workbook, but not all. To open a workbook that contains features unsupported by the Web-based viewer or editor, open the workbook in Excel.
  9. As of Excel 2010, you can use Web queries to import data directly from various Web pages that contain financial and other types of statistical data that you need to work with in a worksheet . http://www.dummies.com/how-to/content/how-to-import-online-data-into-excel-2010-with-a-w.html
  10. The following diagram shows how the different files and operations work together when you use XML with Excel. Essentially, there are five phases to the process:

    XML Process

  1. Adding an XML schema file (.xsd) to a workbook.
  2. Mapping XML schema elements to individual cells or XML tables.
  3. Importing an XML data file (.xml) and binding the XML elements to mapped cells.
  4. Entering data, moving mapped cells, and leveraging Excel functionality, while preserving XML structure and definitions.
  5. Exporting revised data from mapped cells to an XML data file.

Module 14 Homework:

Independent Challenge #2 – 14pts

    • Step h should refer to EX 14-January Sales instead of EX 14-Rates workbook.
    • Save and Upload after step k.
    • In step l, save the file as a single file web page (not .htm) and upload again.
    • Skip steps m & n.
  • Independent Challenge #3 – 16 pts
    • Upload a screenshot after step e.
    • In step K SAVE before closing.
    • Upload the Customers.xlsx and 14-Phone List.xml files.
 
Do you need a similar assignment done for you from scratch? We have qualified writers to help you. We assure you an A+ quality paper that is free from plagiarism. Order now for an Amazing Discount!
Use Discount Code "Newclient" for a 15% Discount!

NB: We do not resell papers. Upon ordering, we do an original paper exclusively for you.