#1   Report Post  
Old February 16th 07, 07:46 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 34
Default External Data Import

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,

  #2   Report Post  
Old February 16th 07, 10:22 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 329
Default External Data Import

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,




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Import External Data RFJ Excel Discussion (Misc queries) 1 August 28th 06 12:16 AM
import external data ana garcia Excel Discussion (Misc queries) 2 April 29th 06 04:05 AM
Import External Data Susie Excel Worksheet Functions 0 July 29th 05 09:12 PM
import external data Dave Excel Discussion (Misc queries) 0 May 24th 05 11:19 PM
Import External Data Martin Excel Worksheet Functions 1 February 9th 05 06:14 AM


All times are GMT +1. The time now is 06:34 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017