Transcript Using Microsoft Office and Office Links Utilities

Office Links - Sharing Data
in Microsoft Office
A Mixed Bag of Treasures
Chester N. Barkan
Registrar
Long Island University, C.W.Post Campus
Microsoft Office


Microsoft Word
 Mail Merge Feature
 Hyperlinks
 Organizational Charts
Microsoft Access
 Merge & Publishing in MS Word – Office Links
 Analyzing with MS Excel – Office Links
 Importing Legacy Data into an Access Database
1. OfficeLinks Merge It with MS
Word (Tools menu)


Starts the Microsoft Word Mail Merge Wizard,
which merges Microsoft Access data with a
Word document so you can create, for
example, form letters or mailing labels.
Demonstration of mail merge features
Microsoft Word – Mail Merge
Feature




Mail Merge Feature – is one example of how
we share data between resources
Primary File is the document file which is used
with the merge data
The data files can be developed using Excel or
Access. We will use Access in this presentation
Review the handout on working with the Mail
Merge Feature
2. Hyperlinks in Word



Hyperlinks aren't just blue, underlined words that
magically take you to another place. Behind them, in
the HTML, is a URL that tells the browser where to
go when you click the link.
Take a look at the example on the left. "Microsoft
Web site" is an example of a hyperlink. Under it is
what the HTML looks like for the link.
Don't get too caught up in what the HTML means.
Just pay attention to the part that's enclosed in
quotation marks. That's a URL, and that's where the
link takes you when you click it.
Create a hyperlink


Microsoft Word creates a hyperlink for you
when you type the address of an existing Web
page, such as www.microsoft.com, if the
automatic formatting of hyperlinks has not
been turned off.
To create more customized hyperlinks, do one
of the following, depending on what you want
to link to.
3. Organizational Charts




On the Drawing toolbar, click Diagram
Toolbar: A bar with buttons and options that you use to carry out commands. To display a
toolbar, use the Customize dialog box (point to Toolbars on the View menu and click
Customize). To see more buttons, click Toolbar Options at the end of the toolbar.
Click the Organization Chart diagram, and then click OK.
Do one or more of the following:


Text cannot be added to lines or connectors in organization charts.




If you want to add a shape, select the shape you want to add the new shape under or next to, click the
arrow on the Insert Shape button on the Organization Chart toolbar, and then click one or more of
the following:
Coworker— to place the shape next to the selected shape and connect it to the same superior
shape.
Subordinate — to place the new shape below and connect it to the selected shape.
Assistant— to place the new shape below the selected shape with an elbow connector.


If you want to add text to a shape, right-click the shape, click Edit Text, and type the text.
If you want to add a preset design scheme, click AutoFormat on the Organization Chart toolbar,
and select a style from the Organization Chart Style Gallery.
Click outside the drawing when you are finished.
4. OfficeLinks Publish It with MS
Word (Tools menu)

Creates a rich-text format (.rtf) file of the
selected object (such as a table, report, or
portion of a datasheet) and loads it into
Microsoft Word. By default, the file name is
the name of the object, the file extension is .rtf,
and the document is stored in the Microsoft
Access folder.
MS Access: Export data or database
objects



To an Access or Excel file
Export database objects to an existing
Microsoft Access database or Microsoft
Access project
In the Database window click the name of the
object you want to export and then on the File
menu, click Export.
To an Access or Excel file



Click the arrow to the right of the Save in box, and
select the drive or folder where the database you want
to export to is located.
Double-click the icon for the database that you want
to export to.
In the Export dialog box, enter a name for the new
object (or accept the current name). If you are
exporting a table, in Export dialog box, select
whether you want to export both the table's definition
and data or just the table's definition.
Importing Data to Spreadsheet








To a Microsoft Excel or other spreadsheet
Save an object's output as a Microsoft Excel file
In the Database window , click the name of the object you
want to save. To save part of a datasheet, open the datasheet
and select a portion of the datasheet before continuing.
On the File menu, click Export.
In the Save as type box, click Microsoft Excel 5-7 (*.xls) or
Microsoft Excel 97-2002 (.xls).
Click the arrow to the right of the Save in box, and select the
drive or folder to save to.
In the File name box, enter a name for the file (or use the
suggested name).
Select the Save formatted check box.
Importing Data to Spreadsheet




Do one of the following:
 If you are saving a datasheet, click Export All to save the
entire datasheet or Save Selection if you selected a portion
of the datasheet in step 1.
 For all other database objects, click Export.
Load the output of a table, query, form, or report into
Microsoft Excel
In the Database window ,click the name of the datasheet, form,
or report you want to save and load into Microsoft Excel. To
save a selection of a datasheet, open the datasheet, and then
select the portion of the datasheet before continuing.
On the Tools menu, point to Office Links, and then click
Analyze It with Microsoft Excel.
Importing Data to Spreadsheet



Export a spreadsheet on a data access page to a
Microsoft Excel workbook
In the design window, make sure the spreadsheet is
activated by double-clicking on the spreadsheet. This
will open the application that created the object,
which in this case is Microsoft Excel. For
additional information on activating a spreadsheet,
see Excel Help.
On the toolbar in the spreadsheet, click Export to
Microsoft Excel to run or switch to Excel and
display the data in a workbook.
To Microsoft Word or other text
format








Export a datasheet to a delimited or fixed-width text file
In the Database window click the name of the table, query, view, or stored
procedure you want to export , and then on the File menu, click Export.
In the Save as type box, click Text Files (*.txt; *.csv; *.tab; *asc).
Click the arrow to the right of the Save in box, and select the drive or
folder to export to.
In the File Name box, enter a name for the file (or use the suggested
name), and then click Export.
Microsoft Access starts the Export Text Wizard.
Follow the directions in the dialog boxes. Click Advanced to create or use
an import/export specification Save an object's output as a Rich Text
Format file
In the Database window ,click the name of the object you want to save. To
save a selection of a datasheet, open the datasheet and select the portion of
the datasheet before continuing.
To Microsoft Word or other text format







On the File menu, click Export.
In the Save as type box, click Rich Text Format (*.rtf).
Click the arrow to the right of the Save in box, and select the drive or
folder to save to.
In the File name box, enter a name for the file (or use the suggested name).
Do one of the following:
 If you are saving a datasheet, click Export All to save the entire
datasheet or click the arrow to the right of the Export All box, and
select Save Selection if you selected a portion of the datasheet in step
1.
 For all other database objects, click Export.
Load the output of a table, query, form, or report into Microsoft Word
In the Database window ,click the name of the datasheet, form, or report
you want to save and load into Microsoft Word. To save a selection of a
datasheet, open the datasheet and select the portion of the datasheet before
continuing.
To Microsoft Word or other text format








On the Tools menu, point to Office Links, and click Publish It with
Microsoft Word.
Save a datasheet to an encoded format
On the File menu, click Export.
In the Save as type box, click Text Files (*.txt; *.csv; *.tab; *asc).
Click the arrow to the right of the Save in box, and select the drive or
folder to export to.
In the File name box, enter a name for the file (or use the suggested name).
Select the Save formatted check box.
In the Encode <objectname> As dialog box, select the method of encoding
as Windows (default), MS-DOS, Unicode, or Unicode (UTF-8).
Export to HTML format




To a Web page
Depending on whether you need static or dynamic data, you
can export, import, or link to different types of HTML data in
Microsoft Access.
Export
Export a datasheet to dynamic HTML format
 In the Database window click the name of the table, query,
or form you want to export, and then on the File menu,
click Export.
 In the Save As Type box, click Microsoft IIS 1-2 (*.htx;
*.idc) or Microsoft Active Server Pages (*.asp).
 Click the arrow to the right of the Save In box and select
the drive or folder to export to.
 In the File Name box, enter the file name.
 Click Export.
Export to HTML format
In the HTML Template box, enter the
location of an HTML template.
 In the Data Source Name box, enter
the name of the ODBC data source that
you will connect to when the servergenerated HTML files are processed on
the Web server.
Do one of the following:


Export to HTML format

Export a datasheet to static HTML format
 In the Database window click the name of the table, query,
or form, you want to export, and then on the File menu,
click Export.
 In the Save As Type box, click HTML Documents
(*.html;*.htm).
 Click the arrow to the right of the Save In box and select
the drive or folder to export to.
 In the File Name box, enter the file name.
 Select the Save Formatted check box to:
 Save the table, query, or form in a format similar to its
appearance in Datasheet view
 Enable the AutoStart check box
Import or link

Import or link (read-only) HTML tables and lists







Open a database, or switch to the Database window for the open
database.
To import HTML tables or lists, on the File menu, point to Get
External Data, and then click Import.
To link HTML tables or lists, on the File menu, point to Get External
Data, and then click Link Tables.
In the Import or Link dialog box, in the Files of type box, click
HTML Documents (*.html; *.htm).
Click the arrow to the right of the Look in box.
Select the drive and folder where the HTML file you want to import or
link is located, and then double-click the file name.
Follow the instructions for the Import HTML Wizard or the Link
HTML Wizard. Click the Advanced button if you want to edit an
import/export specification (import/export specification: A specification
that stores the information Access needs to import or export a fixedwidth or delimited text file.) or specify different file and field formats.
OfficeLinks Analyze It with MS
Excel (Tools menu)
Creates a Microsoft Excel (.xls) file of the
selected object (such as a table, report, or
portion of a datasheet) and loads it into
Microsoft Excel. By default, the file name is
the name of the object, the file extension is
.xls, and the document is stored in the
Microsoft Access folder.
Importing Legacy Data into an
Access Database


Requirements are an open Access database and
the text file- 2 Options
Get External Data Import (File menu)

Copies data from a text file, HTML file,
spreadsheet, Microsoft Exchange folder or address
book, database object, or database table into a
Microsoft Access table. Also, copies objects from
another database into the current database.
Get External Data Link Tables (File
menu)

Links data from a table in another database to
a Microsoft Access table in the current
database.
Legacy or Mainframe Data


Referring to ASCII text (delimited or fixed
width) data that is generated by most legacy
mainframe systems
Import data wizard
Reasons to import data into a table

If you know that you will use your data only in
Microsoft Access, you should import it.
Microsoft Access generally works faster with
its own tables, and, if you need to, you can
modify the imported table to meet your needs
just like any other table that is created in
Microsoft Access.
Reasons to link data from an
external table

You might want to link to tables from other Microsoft
Access databases if, for example, you want to use a
table from another Microsoft Access database that is
shared on a network. This is particularly useful if you
want to store all of your tables in one database on a
network server, while keeping forms, reports, and other
objects in a separate database that is copied among
users of the shared database. You can easily split an
existing database into two databases, called front-end
and a back-end databases.
Demonstration of Importing Data
from Text files




Import Wizard
Review the different stops.
Setting the field sizes – be familiar with the
data you are working with. If something is 3
characters in length, you should allow fore that
when creating the table
Data Types – Be Alert – What constitutes text
data vs. numeric that will be used in
calculations
Demonstration & Conclusion




Complete the identification of the fields
Importing into an existing table or creating a
new table
Review data once it is imported
Any questions can be referred to me c/o
[email protected]