Importing & Exporting

Office Data Connection Files


Text Files

Import Wizard, Text Files ?
You can open single-table database files (.dbf).
You can use Microsoft Query to access many other database file formats.
You can run Microsoft Query directly by selecting (Data > Get External Data > New Database Query).
You can open all types of text files. Just open as usual and you will be prompted with the Text Import Wizard.
You can import data from other file type. Just browse to the file (Import data wizard ?).
Often when you import data, numeric values may appear as numbers but they are actually treated as text.. A quick way to ensure they are all numerical values is to multiply them all by the number "1". Type the value "1" into a cell. Press (Edit > Copy). Highlight the cells containing the data and select (Edit > Paste Special). Click the "multiply" option button and press OK.

You can export the current worksheet although all formatting will be lost except for number formats. To export an Excel file as a text file, select (File > SaveAs) and select from one of the following eight formats:

*.prnFormatted Text (space delimited)
*.txtText (tab delimited)
*.txtUnicode Text
*.txtText (Macintosh)
*.txtText (MS-DOS)
*.csvComma Delimited
*.csvCSV (Macintosh)
*.csvCSV (MS-DOS)

Moving and Sizing

To make an object stay with a cell when the cell moves but keep the size independent of changes in cell height/width select "Move but don't size with cells".
To keep the object's position and size independent of the changes to the cell position height/width select "don't move or size with cells"
To make a control stay with a cell when the cell moves, select "move and size with cells"

Move and size with cells - Control resizes and is repositioned relative to the cell resize. If a control takes up half the cell, it will contnue to do so when the cell is resized.
Move but do not size with cells - This is the default. Control remains with the cell it was dropped on and is not resized. This means that the cell is linked to the cell it was dropped on.
Do not move or size with cells - This provides exact positioning regardless of whether the cell it was dropped onto is moved or resized.
The default behaviour cannot be changed from the Options dialog box.
You can change for individual objects by right clicking and selecting Format Object
SS - office 2007 size and properties dialog

Copying and Pasting

When you copy from one Office application to another the default Copy-Paste embeds the object of the source application into the target application.

Copying an Excel object into Word or PowerPoint

When you copy a whole workbook and paste it into a different application the following happens:
Copying Excel charts into PowerPoint will copy the entire workbook.
If you paste one chart from a workbook that is 3MB in size then your presentation will increase by 3M.
If this presentation is then sent to someone else the entire workbook is also sent with it - scary !!
Whenever you want to copy a chart into PowerPoint or Word you should copy the chart as picture.
Whenever you want to copy a table into PowerPoint or Word you should paste the data as a table/formatted text - not as a picture
Copying charts as pictures though does prevent you from being able to reformat them.
If possible always try and format them accordingly in Excel first.
If you want to paste in the actual chart object - then make sure you copy the chart and its data to a new workbook before copying in the chart. At least this way the file size is kept to a minimum.

To copy a chart as a picture hold down Shift and select (Edit > Copy Picture)
You will notice that the normal Copy changes into Copy Picture..
When an embedded chart is selected you get this screen shot

When a range or chart sheet is selected you get a slightly different screen shot

Copying the Picture format allows you to ungroup and adjust the individual elements.
selecting "as shown on screen" has no restriction on the size of image that can be copied.
selecting "as shown when printed" does have a limitation though - If the chart or range is larger than the margins of the printed page then you will see the following message
SS - The picture is too large and will be truncated.

When you copy a whole chart sheet you have no control over the size
For this reason you should always use embedded charts.
Hold down Alt to help with positioning and aligning on a worksheet.
Always copy charts as picture to help reduce the file size.

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopNext