|
Information
you cut or copy to another program is pasted, if possible,
in a format that the program can edit. For example,
cells from Microsoft Excel worksheets and records
from Microsoft Access are pasted into Word as Word
tables, complete with column widths and font formatting.
Text separated by tab characters from Word is pasted
into Microsoft Excel in rows and columns of cells,
and records from Microsoft Access are pasted into
a Microsoft Excel worksheet as rows with each field
in a separate column.
If a program can’t edit the information, it inserts
it as an embedded object, which you can edit in the
source program ( program that has created the object
) by double-clicking the object. If the information
can’t be inserted as an embedded object — for example,
you’re copying the information from a program that
does not support OLE ( Object Linking and Embedding),
the information is pasted as a static picture that
cannot be edited.
To control the format of information pasted from the
Clipboard, use the Paste Special command (Edit menu)
instead of Paste.
Importing Quattro Pro worksheets
Microsoft Excel may not convert some of the information
in your Quattro Pro files when you open the file.
Microsoft Excel cannot open Quattro Pro for Windows
version 6.0 files.
· On the File menu, click Open
· To open Quattro Pro for Windows files, click Quattro
Pro 1.0/5.0, Quattro Pro 6.0, or Quattro Pro 7.0 in
the Files of type box. If these selections are missing,
you need to install the Quattro Pro converter, a component
of Excel.
· In the Look in box, click the drive or folder that
contains the workbook. In the folder list, double-click
folders until you open the folder that contains the
workbook you want. Double-click the workbook you want
to open.
Once you have opened a Quattro Pro file in Microsoft
Excel, you must save the file as a Microsoft Excel
workbook to complete the conversion. As you save the
file, make sure to click Microsoft Excel Workbook
(or another Microsoft Excel format) in the Save as
type box.
Bring data from a Web page into Microsoft Excel
There are several ways you can bring data from a Web
page into Excel. Depending on how you get the data
into Excel and how the data was put on the Web originally,
some of your data might look different in Excel than
on the Web page. Entire workbooks that were saved
as Web pages by using Excel will always appear correctly,
but other data might appear differently, especially
interactive data. When you copy data from a Web page
to Excel, you might lose some content. For example,
scripts, .gif image files, and lists of data in a
single table cell do not appear properly in Excel.
Copying Web page data to Excel works best if you select
a table only.
Import an entire text file.
Use this procedure if you want to refresh or update
the data in Excel whenever the original text file
changes. If you want to copy the data into Excel without
maintaining a connection to the original file, you
can open the text file in Excel. let’s see how to
import text file into Excel.
· Click the cell where you want to put the data from
the text file. To ensure that the external data does
not replace existing data, make sure that the worksheet
has no data below or to the right of the cell you
click.
· On the Data menu, point to Get External Data, and
then click Import Text File.
(Figure 5.9)
·
In the Look in list, locate and double-click the text
file you want to import as an external data range
or source.
· To specify how you want to divide the text into
columns, choose Delimited in the original data type
option choosing this option will enable you to specify
your own field delimiter such as tab,space,comma or
any other character. Choose Fixed Length if you want
to place data into column with a space between them
i.e space acts as field delimiter. In this case we
are choosing Delimited option.
(Figure 5.10)
·
In step2 of the Import text wizard ( as shown in the
following figure ) you can specify the width of each
column.click on the black arrow that seperates the
two column and hold down the mouse key while you move
this line. If you want to delete this line, double-click
on it.
(Figure 5.11)
·
In the step 3 of Text Import Wizard you can spacify
the column you want to include in the import , in
case you want to delete a cloumn select the column
by clicking the column and then select Do not import
column (skip) option from the Column data format.
If you want to format numbers such as decimal seperation
etc then click the Advanced button.
(Figure 5.12)
·
Click Finish, and then click Properties in the Import
Data dialog box to set formatting and layout options
for how the external data range is brought into Microsoft
Excel.
( Figure 5.13 )
·
In the Import Data dialog box, do one of the following:
o To return the data to the location you selected
in step 1, click Existing worksheet, and then click
OK.
o To return the data to a new worksheet, click New
worksheet, and then click OK. Microsoft Excel adds
a new worksheet to your workbook and automatically
puts the Data from the popup menu
Import Access data
To bring external data into Microsoft Excel, you must:
Have access to the data If the external data source
you want to access is not on your local computer,
you might need to see the administrator of the database
for a password, user permission, or other connection
information.
Install Microsoft Query Microsoft Query is an optional
feature for Excel and includes the Query Wizard. To
set up a data source or create a query in Query, you
must install Query separately.
Install ODBC drivers An ODBC driver is required to
retrieve data in relational databases, text files,
or Excel. When you installed Query, you automatically
installed a set of ODBC drivers. See a list of drivers
installed with Query. If you use a driver other than
one installed with Query, you must install the driver
separately.
Install data source drivers A data source driver is
required to retrieve OLAP source data. Query supports
connecting to databases that were created by using
Microsoft SQL Server OLAP Services; when you installed
Query, you automatically installed support for this
type of OLAP database. To connect to other OLAP databases,
you need to install a data source driver and client
software
Using Query Wizard :
Query is an optional feature for Microsoft Excel that
is, by default, automatically installed the first
time you use a command or feature that retrieves external
data. You can use Query Wizard to import data from
a data source. A data source is a stored set of information
that identifies the database a user wants to gain
access to and includes the information needed to connect
to that database. A data source has an identifying
name and can include the name, and location of the
database server, the name of the driver program used
to connect to the database,and information the database
needs when you log on. Data sources can connect to
relational databases such as Microsoft Access and
Microsoft SQL server , On-Line Analytical Processing
(OLAP) databases such as Microsoft SQL Server OLAP
Services, text file database, and lists on Microsoft
Excel worksheet. Let’s see how to use query wizard.
· Click the cell where you want to place the data
retrived from the external data source.
· On the Data menu, point to Get External Data, and
then click New Database Query.
(Figure 5.14)
·
On the Databases tab, double-click the database
you want to retrieve the data from. If you need
to set up a new data source, double-click New Data
Source, and then specify the data source. Select
the Use the Query Wizard to create/edit queries
check box.
(Figure 5.15)
·
Select the <New Data Source > and click OK .
(Figure 5.16)
·
Give a name to this new data source (in this case
name it as my datasource). Choose the DataBase driver
you want to use for data source( in this case choose
Micrsoft Access Driver (*.mdb)). Now click the button
Connect. In the ODBC Microsoft Access setup window
click the Select button, now select FPNWIND.MDB from
C:\Program files\ Microsoft Office\Office\1033 and
click OK.
(Figure 5.17 )
·
Now select a tabel to be used in the query (in this
case choose Customers ) and click OK.
(Figure 5.18)
·
Datasource window will appear again, select the
datasource you have just created (i.e my datasource)
if not allready selected and click OK. Query Wizard
will start, asking you to select (Figure 5.18)
· Datasource window will appear again, select the
datasource you have just created (i.e my datasource)
if not allready selected and click OK. Query Wizard
will start, asking you to select columns you want
to include in the query. Now select the column you
want to include and click the button.
If you want to exclude a column then select the
column and click the the If
you want to deselect all the column then just click
the 
. If you wnat to include other tables or views click
the option button and
use preview button to browse through the values
of the column.
(Figure 5.19)
·
After you have selected the column you want to include
in the query, click the Next button.Query wizard will
now ask you for any filter or criteria you want to
use to filter the records, just click next.
(Figure 5.20)
·
Specify the column you want to use for sorting .
Choose CustomerID and click Next.
(Figure 5.21)
·
You can save the query by click the Save Query button,
now click the Finish button.
(Figure 5.22)
·
Return External Data to Microsoft Excel window will
appear.
o To return the data directly to Microsoft Excel,
click Return data to Microsoft Excel, click Finish,
and then select where you want to put the external
data.
o To save the query in a .dqy query file that you
can open and run in Excel or open and edit in Microsoft
Query, click Save Query. This optional step is recommended
if you plan to create an OLAP cube from the query.
o To run the OLAP Cube Wizard, where you can create
a cube from the data in your query, click Create an
OLAP cube from this query, click Finish, and then
follow the directions in the OLAP Cube Wizard. This
option is recommended only for experienced users.
Learn about creating OLAP cubes.
o To refine the query further, click View data or
edit query in Microsoft Query, and then click Finish.
The result set is displayed in Query, where you can
make further changes and then save a query file, create
an OLAP cube, or return the result set to Excel.
(Figure 5.23)
Data
will be placed the worksheet as shown in the following
figure.
(Figure 5.24)
Like
this you can import data from any of the following
databases.
o Microsoft SQL Server OLAP Services (OLAP provider)
o Microsoft Access 2000
o dBASE
o Microsoft SQL Server OLAP Services (OLAP provider)
o Microsoft Access 2000
o dBASE
o Microsoft FoxPro
o Microsoft Excel
o Oracle
o Paradox
o SQL Server
o Text file databases
· To check the status of a query that’s running in
the background and taking a long time to return data,
click Refresh Status·
· On the Excel External Data toolbar. To cancel the
query, click Stop Refresh.
Exporting worksheet on a Web page
Use this procedure to put a worksheet on a Web page
so that others can interact with the data. You can
also put a worksheet on a Web page so that others
can only view the data.
Before you save or publish data on a Web page, save
your workbook as an .xls or .htm file so that you
have a version you can modify if you want to later
change your Web page.
· Click the worksheet you want to put on a Web page.
· On the File menu, click Save as Web Page.
(Figure 5.25)
(Figure 5.26)
· Click Publish button.
· Make sure that Items on sheetname is selected
in the Choose box and Sheet All contents of sheetname
is selected in the list.
· Under Viewing options, select the Add interactivity
with check box, and click the type of functionality
you want for your worksheet.
· To add a title to the published worksheet, click
Change, type the title you want, and click OK.
· In the File name box, click Browse, and locate
the drive, folder, Web folder, Web server, or FTP
location where you want to save or publish your
Web page.
· If you want to view the Web page in your browser
after you publish it, select the Open published
Web page in browser check box.
· Click Publish.
If you select a worksheet that contains a chart
and publish it with interactive functionality, the
chart will not be included on the Web page. To put
an interactive chart on a Web page, you must save
it separately. Some formatting and features of your
worksheet are not retained when you save it as a
Web page. After you save your worksheet as a Web
page, you can’t open and modify the .htm file in
Microsoft Excel without losing formatting and functionality.
Use Microsoft FrontPage 2000 or data access page
Design view in Microsoft Access 2000 to modify the
Web page.
Convert a Microsoft Excel list to a Microsoft
Access database
After you convert a list to Microsoft Access,
you maintain the data only in Access. Changes you
make to the Access database after the conversion
do not affect the Microsoft Excel list.
To convert an Excel list, you must have Access installed.
If the Convert to MS Access command does not appear
on the Data menu in Excel, you need to install and
load the AccessLinks add-in program. Go to Tools
and then choose Add Ins. In the Add Ins window select
the AccessLinks as shown in the following figure
(Figure 5.27)
Click
OK. It will ask you “ You want to install this feature”
click YES. In this way you can install any Add Ins
you want. Now let’s see a example of convert excel
sheet in to access database.
· Open a new worksheet.
· Now type the text in the worksheet so that it
look like the following worksheet.
(Figure 5.28)
·
Place the cursor in the A1 cell , choose Convert to
MS Access option from the Data menu.Convert to MS
Access window will appear as shown in the following
figure.
(Figure 5.29)
·
To create a new Access database choose New database
option To add the list to an existing Access database,
click Existing database, and then type the path to
the database in the box under Existing database. To
look for the database on your system or network, click
Browse. In this case choose New Database and click
OK. The Access Import Spreadsheet Wizard will start,
which will guide you to permanently convert your Excel
list to an Access database.
(Figure 5.30)
·
Choose First Row conatins Column Headings option and
click Next button.
(Figure 5.31)
·
Choose the option In a new Table and click Next.
(Figure 5.32)
·
In the option Indexed choose Yes( No Duplicates )
and click Next
(Figure 5.33)
·
Choose the option No primary Key and click Next.
(Figure 5.34)
·
Give a name for your table in the Import to Tabel
option and click Finish. Confermatibox will appear
as shown in the following figure
(Figure 5.35)
·
After you click OK button, you will see the MS-Access
window with your database and tabel just created.
(Figure 5.36)
·
To see the contents of the table you have just created.
select the table then click the open button located
on the toolbar or right click and choose open option
from the pop-up menu.
(Figure 5.37)
·
After the conversion, AccessLinks places a text box
to the right of the original list stating that the
list has been converted to Access data. The original
data, however, does not change.
·
(Figure 5.38 )
·
After you have convertd the Excel list into Access
table anychanges made in the Excel list will not be
reflected in the Access table.
|