I have an external link to an excel file that contains a table. I am able to
import the data but am not able to keep the source formatting. Is there a
way to do this?

I tried using "format painter" and just copying from the source file to the
new file. It works initially, however, when i refresh the data the
formatting gets all thrown off.

Any suggestions and/or other options to try would be appreciated.

Thanks,

hi Mike,

Two ways, depending on whether you want a picture of the data in the source workbook, or data you can work with in the target
workbook. Both require that you have both your source & target workbooks open:

1. For a linked picture of the data in the source workbook -
.. activate the source workbook
.. copy the source range
.. click on the target workbook
.. hold down the shift key and select Edit|Paste Picture Link

2. For linked data you can work with in the target workbook -
.. activate the target workbook
.. enter '=' into the upper left destination cell in the target workbook
.. click on the source workbook, then the upper left source cell in that workbook
.. press <Enter. You should now have a formula in the form of '=[Book1]Sheet1!$A$1' in the target workbook
.. delete the '$' symbols from the formula
.. copy the formula across & down as far as needed
.. click on the source workbook, then select all the source cells in that workbook
.. copy the selected cells
.. click on the target workbook, then select all the destination cells in that workbook
.. click on Edit|Paste Special|Format
Note that, if the source range includes any empty cells, you'll get 0s in the target workbook. Here are three possible ways of
dealing with that in the target workbook:
.. delete the formulae returning the unwanted 0s
.. use Tools|Options|View to suppress 0 values - not good if you've got needed 0s
.. change the formula from '=[Book1]Sheet1!A1' to =IF([Book1]Sheet1!A1="","",[Book1]Sheet1!A1) - not good if you've got headings etc
spanning multiple cells
Alternatively, you could insert dummy spaces or tick marks (') in the source workbook - again, not good if you've got headings etc
spanning multiple cells.

Cheers

--
macropod
[MVP - Microsoft Word]


"MikeD1224" wrote in message ...
| I have an external link to an excel file that contains a table. I am able to
| import the data but am not able to keep the source formatting. Is there a
| way to do this?
|
| I tried using "format painter" and just copying from the source file to the
| new file. It works initially, however, when i refresh the data the
| formatting gets all thrown off.
|
| Any suggestions and/or other options to try would be appreciated.
|
| Thanks,