Microsoft Office Excel Part (B)

Export Data To Microsoft Office Excel

MapPoint North America 2004 SDK

1. Open the map from which you want to export data.
2. Do one of the following:

Draw a closed shape (such as a drivetime zone or freeform, rectangle, oval, or radius circle shape) around the data you want to export, and then select the shape by clicking its border.

On the Navigation toolbar, click Select  , and then drag from the upper-left corner to the lower-right corner of the area that includes the data you want to export.

Select the data set name in the legend.

3. On the Data menu, click Export to Excel.

- or -

Click Export to Excel .

Microsoft Excel starts, and the entire data set or any data mapped entirely within the shape you drew is exported and presented in an Excel workbook (see the notes below for more information). The workbook includes a header sheet with the dimensions of the area you selected, the name of each data set that was included, and the total number of records exported from each set.

Each data set is included on a separate tab of the Excel workbook with the first tab as the header sheet.

Notes

You can only export data from one closed shape at a time; that is, any shapes on the map other than the selected closed shape will be ignored.

Selecting an area or shape at a high altitude could result in unreliable export data. It is possible that the two-dimensional shape will extend beyond the three-dimensional curvature of the Earth that is rendered on the map. To avoid this problem, try the following:

Select the shape, and on the View menu, point to Zoom, and then click To Selection.

Use the Select tool to zoom to the lowest possible altitude that shows the data you want to export, and then select the area that includes that data.

To export the entire set, select the data set name in the legend.

Data that does not appear entirely within the selected area or shape may or may not be exported. Every mapped data point has a centroid — a point representing the center of the geographic area to which the data is mapped — that is affected by the map projection and elevation. If the centroid for a particular data point is within the selected area or shape, the data is exported, and if not, the data is not exported. For some data, you may have better results creating a more precise shape, such as a freeform shape, surrounding the data subset that you want to export.

If your map includes territories or data mapped to territories, territory definitions are exported to one sheet and any data mapped to territories are exported to a separate sheet.

About this course

This course includes:

Two self-paced lessons and two practice sessions for hands-on experience.

A short test at the end of each lesson; tests are not scored.

A Quick Reference Card you can take away from the course.   Goals

After completing this course, you will be able to:

Publish a list from Excel to a SharePoint site.

Synchronize the lists when updates are made.

Resolve conflicts when different changes are made to the same data.

In this course you'll learn how to publish an Excel list to a SharePoint site. Then you'll learn how to synchronize the Excel list so that when you make changes to it the SharePoint list gets updated at the same time.

You'll also learn how to resolve conflicts when different changes are made to the same data in Excel and in the SharePoint list.

To learn more about this course, read the overview information or the table of contents in the left column. When you're done reading this page, click Next to start the first lesson.

Publishing an Excel list to a SharePoint site is an easy way to share data with others and to let others interact with the data. You use a simple two-step wizard to publish the list to a site based on Windows SharePoint Services where others can see the Excel data without even having to open Excel.

People who have permissions may then read the SharePoint list, while others may even revise the list or enter additional data.

Suppose that you have an Excel list of sales made by your salespeople. In this lesson, you'll learn how to publish it to a SharePoint site so that you can share it with your managers.

To continue reading this lesson on your own, click Next.

 

To publish the list you created with the new List command in Excel 2003, begin by clicking the list (it must be active on the worksheet), point to List on the Data menu, and then click Publish List.

The Publish List to SharePoint Site dialog box appears, and you would:

Type the URL of the server (your SharePoint site) in the Address box.

Select the Link to the new SharePoint list check box so that you can update the list with changes. If you don't choose to link the lists now, you won't be able to do it later on, and you won't be able to update.

Type a name for your list in the Name box.

Type a description in the Description box, which is optional, but useful, and then click the Next button.

Then, Excel checks the data in each column to ensure that the data belongs to one of the data types supported by Windows SharePoint Services: text, currency, dates, numbers, or hyperlinks. If it doesn't, Excel will usually apply the text data type to each column. If you do not want to accept the formatting applied by Excel, you can go back to the list to check formatting in the specific cells and make any necessary changes.

Excel also checks whether each column contains only one type of data. For example, a column in a published list cannot contain both numbers and text. If a list column did mix the two types, Excel would apply the text data type for every cell in the column. Given a choice between numbers and text, Excel would choose text.

In the practice session at the end of the lesson we'll talk a bit more about what goes on in the Publish List to SharePoint Site dialog box.

Note     Formulas are not exported to the SharePoint site. They are converted to values. If you have the list total row turned on, the total row will not be exported.

After you click Finish in the dialog box, you could click the link in the message box (Figure 1) to see the list on your SharePoint site. Once you've taken a look, you can return to your list in Excel by clicking the Close button at the top of the browser window, clicking the Excel button on the taskbar, or pressing ALT+TAB. Then click OK to close the message box with the link.

You can see a published list on the SharePoint site any time by selecting the list on the worksheet, clicking List on the List toolbar, and then clicking View List on Server, as shown in Figure 2.

Others can see the list by clicking on it directly from the SharePoint site, under Lists. Those who have the necessary permissions can also make changes to the list data. In the next lesson, you'll learn how to synchronize changes made to the list either in Excel or from the SharePoint site.

Note     To see the list, others must have a working network or Internet connection to the Microsoft Windows SharePoint Services site, and they must have permissions to view the list on the server.

In this practice session, you'll publish a list from Excel to a server that is running Microsoft Windows SharePoint Services.

To do the practice session, you must have access to a server that is running SharePoint Services, and you must have permissions to create a list on the server.

If you don't have a server running Windows SharePoint Services but you do have Internet access, you can sign up for the Windows SharePoint Services Trial which will allow you do the practices.

About the practice session

When you click Practice in Excel, a practice worksheet will download to your computer and open in Excel, and a separate window with practice instructions will appear alongside (see picture).

Note     You need to have Excel 2003 installed on your computer.

Tips

If the practice instructions aren't visible, or if they disappear when you click in Excel, click the Excel Help taskbar button and then click the Auto Tile button in the upper-left corner of the instructions.

If the practice instructions cover up Excel, click the Auto Tile button in the upper-left corner of the instructions.

Before you begin

Make sure to close Excel if it is already running.

Start the practice

Click the Practice in Excel button now.

Now you've published your list on the SharePoint site. Since publication, the salespeople have made more sales, so you need to revise the Excel list and update it on the site.

In this lesson you'll learn how to synchronize the list so that the changes you make in Excel are visible on the Share Point site, and changes on the Share Point list appear in the Excel list. You'll also learn what to do when changes made on the Share Point site conflict with changes made in Excel, and vice versa.

Click Next to continue reading this lesson on your own.

Because your list was linked to the new SharePoint list when you published it (remember the Link to the new SharePoint list check box), future changes to the Excel list will be updated on the SharePoint list, and any changes made to the SharePoint list will be brought down to the Excel list.

When you make changes in the Excel list

Let's say that you add another row to the list in Excel to account for another sale made by Peacock. You'd probably add lots of rows at a time in real life, but you'll just add one row in this example. After you add the row, the data is no longer the same on both the lists.

To update the data on the SharePoint site to match the data in Excel, you would click Synchronize List  on the List toolbar in Excel. Now the data would be exactly the same in the list on the SharePoint site as it is in the list in Excel.

Note     Synchronization is not automatic. If you make changes without synchronizing and then save the list, a dialog box appears to remind you that your workbook contains changes to a SharePoint list that have not yet been synchronized with the SharePoint site. You can synchronize then by clicking the Synchronize List button , or synchronize the next time you open the Excel list (more on that later).

When others make changes in the SharePoint list

Perhaps Suyama adds a sale directly to the list that he forgot to send in to you last week. He has permission to add data to the site, but now the SharePoint list is out of sync with the Excel list. If you know that others have permissions to make changes to your published lists, it's a good idea to click Synchronize List in Excel regularly and often to ensure that both lists are in sync.

 

Click Discard My Changes to accept the changes on the SharePoint site.

 Click Retry My Changes to overwrite the changes on the SharePoint site.

With a list accessible both in Excel and on the SharePoint site, it's possible to have different changes made to the same piece of data. For example, a sales figure could be updated to one amount by you, but to a different amount by someone else.

You'll be informed about the conflict between the Excel list and the one on the SharePoint site the next time you synchronize the list in Excel, when the Resolve Conflicts and Errors dialog box will open.

The dialog box displays changes you made in the Excel list and changes made in the SharePoint list. You can accept or discard your changes: Accepting your changes will overwrite any changes made on the SharePoint site; discarding your changes will save to the Excel list the changes made to the SharePoint list.

Conflicts can also be resolved by someone viewing the SharePoint list. A message appears at the bottom of the list indicating that there is a data conflict with another user, along with a Resolve link that can be clicked to open the Resolve Conflicts dialog box on the SharePoint site.

You'll get to practice synchronizing lists and resolving conflicts at the end of the lesson.

Note    Besides data conflicts, Excel will report errors such as not having permission to use the server.

There might be times when you add some new data in Excel but are not ready to synchronize when you save the list. For example, you might be working with a laptop that is not connected to your office and the SharePoint site.

Just save and close the file without synchronizing. When you're back in the office and you reopen the Excel workbook containing that list, you'll get a dialog box telling you that the workbook has a list that has not yet been synchronized on the SharePoint site. The dialog box gives you the option to click either Yes or No.

Click Yes to keep your changes, and then click Synchronize List  to update the SharePoint site with the changes.

Click No if you do not want to keep your previous changes, and then click Discard Changes and Refresh  on the List toolbar. This choice will overwrite the data in your Excel list with the data on the SharePoint site.

Tip     You can also use Discard Changes and Refresh to restore your Excel list to the latest published version on the SharePoint site if you want to discard the changes that you've recently made to the Excel list.

In this practice session, you'll make changes to the list in Excel that you created earlier and then synchronize the list. You'll also resolve conflicts to the list. Finally, you'll delete the practice list from your SharePoint site.

To do the practice session, you must have access to a server that is running SharePoint Services, and you must have permissions to create a list on the server.

If you don't have a server running Windows SharePoint Services but you do have Internet access, you can sign up for the Windows SharePoint Services Trial which will allow you do the practices.

About the practice session

When you click Practice in Excel, a blank worksheet will download to your computer and open in Excel, and a separate window with practice instructions will appear alongside (see picture).

Note     You need to have Excel 2003 installed on your computer.

Tips

If the practice instructions aren't visible, or if they disappear when you click in Excel, click the Excel Help taskbar button and then click the Auto Tile button in the upper-left corner of the instructions.

If the practice instructions cover up Excel, click the Auto Tile button in the upper-left corner of the instructions.

Have you completed all the lessons that you're interested in? A checkmark appears in the table of contents next to each completed lesson's title.

When you've finished all the lessons, please rate the course and give us your feedback. Ratings and feedback help us to create useful courses and to make existing ones better.

                                               -###-