Import External Data

Hi all,

  I'm trying to use the "Import External Data" function in Excel 2003 to 
import data from one spreadsheet to another.  It works for some fields 
(specifically ,date and text fields), but for others no data is imported 
(specifically, whole numbers, percentage fields and dollar fields).  Is 
there some way to import the missing data?

Thank you,

Art. 


0
artmacneil (88)
7/6/2005 4:02:59 AM
excel 39879 articles. 2 followers. Follow

11 Replies
595 Views

Similar Articles

[PageSpeed] 7

importing external data is usually used to import data form a REAL data
store-- like an Access database or a SQL Server database.

Basically; Microsoft is too flaky to make these simple things work
correctly.  because they're too drunk on their own success.

Sorry; I wish that it weren't true-- but we have all been abused by a
monopoly that won't fix bugs in their products.

If it doesnt work-- give us a lot more information; we might be able to
help you.
But as a whole-- Excel DOESNT WORK RELIABLY ENOUGH to base your
business decisions off of this data.

I mean-- for starters; with Excel you have to have a different copy of
the formula for each record.. so when you change something; you have to
change it in 1,000 different places.

There is a better way.

Use DATABASES for your DATA and uninstall Excel

0
aaron.kempf (776)
7/6/2005 7:35:46 PM
<aaron.kempf@gmail.com> wrote in message 
news:1120678546.266183.235930@o13g2000cwo.googlegroups.com...
> importing external data is usually used to import data form a REAL data
> store-- like an Access database or a SQL Server database.
>
> Basically; Microsoft is too flaky to make these simple things work
> correctly.  because they're too drunk on their own success.
>
> Sorry; I wish that it weren't true-- but we have all been abused by a
> monopoly that won't fix bugs in their products.
>
> If it doesnt work-- give us a lot more information; we might be able to
> help you.
> But as a whole-- Excel DOESNT WORK RELIABLY ENOUGH to base your
> business decisions off of this data.
>
> I mean-- for starters; with Excel you have to have a different copy of
> the formula for each record.. so when you change something; you have to
> change it in 1,000 different places.
>
> There is a better way.
>
> Use DATABASES for your DATA and uninstall Excel
>


Drat.  I was hoping I could get this to work, thanks for your reply.



0
artmacneil (88)
7/7/2005 2:39:37 AM
Art,

Import generally means reading data that isn't native to the program, like a 
text file into an Excel sheet.  But your source data is a spreadsheet. 
Excel workbook?  Say more.  There's likely a way to get the data if you can 
describe it.  Describe the layout of the data in the sheet, with examples.

-- 
Earl Kiosterud
www.smokeylake.com/
-------------------------------------------

"Art MacNeil" <artmacneil@shaw.ca> wrote in message 
news:TJIye.1874188$6l.1636299@pd7tw2no...
> Hi all,
>
>  I'm trying to use the "Import External Data" function in Excel 2003 to 
> import data from one spreadsheet to another.  It works for some fields 
> (specifically ,date and text fields), but for others no data is imported 
> (specifically, whole numbers, percentage fields and dollar fields).  Is 
> there some way to import the missing data?
>
> Thank you,
>
> Art.
> 


0
nothanks4548 (968)
7/7/2005 3:37:28 AM
Art

I'm sure you can...unfortunately you have run into an Aaron 'rant'.  We're 
used to them but it can be disconcerting when encountered on first visit.

Are the 'numeric' rows mixed in amongst the other ones or are they on the 
extremities?  There will be a way of getting this to work

-- 
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


"Art MacNeil" <artmacneil@shaw.ca> wrote in message 
news:JB0ze.1878804$6l.522842@pd7tw2no...
>
> <aaron.kempf@gmail.com> wrote in message 
> news:1120678546.266183.235930@o13g2000cwo.googlegroups.com...
>> importing external data is usually used to import data form a REAL data
>> store-- like an Access database or a SQL Server database.
>>
>> Basically; Microsoft is too flaky to make these simple things work
>> correctly.  because they're too drunk on their own success.
>>
>> Sorry; I wish that it weren't true-- but we have all been abused by a
>> monopoly that won't fix bugs in their products.
>>
>> If it doesnt work-- give us a lot more information; we might be able to
>> help you.
>> But as a whole-- Excel DOESNT WORK RELIABLY ENOUGH to base your
>> business decisions off of this data.
>>
>> I mean-- for starters; with Excel you have to have a different copy of
>> the formula for each record.. so when you change something; you have to
>> change it in 1,000 different places.
>>
>> There is a better way.
>>
>> Use DATABASES for your DATA and uninstall Excel
>>
>
>
> Drat.  I was hoping I could get this to work, thanks for your reply.
>
>
> 


0
7/7/2005 6:40:49 AM
Well thank God for that!!

Here's how all the spreadsheets (almost 400 of them) are laid out.

All spreadsheets are about 400 lines long.

Column A - always text, though sometimes formulas are used (concatenate, 
etc) and some have conditional formatting.

Column B - is the primary column.  It always has calcs.   The results can be 
text, whole number (i.e. the year: 2005), dac (dollars and cents), date 
fields (dd/mm/yyyy) or percent fields.

There are other columns C through R, but they all lead back to Column B. 
(Columns C and D have calcs, the rest do not).

All spreadsheets are as I've described above.  I have about 400 of them. 
Each has a tab for reference data.  This "RD" tab is what I've described 
above.

So the ideal solution would be to maintain 1 RD Tab and have each of the 400 
or so spreadsheets import the external data from that 1 tab.  The RD tabs 
are all identical, so if this can be done it would save considerable time.

Thanks for your help,

Art.


"Earl Kiosterud" <nothanks@nospam.com> wrote in message 
news:OfSI1UqgFHA.2880@TK2MSFTNGP14.phx.gbl...
> Art,
>
> Import generally means reading data that isn't native to the program, like 
> a text file into an Excel sheet.  But your source data is a spreadsheet. 
> Excel workbook?  Say more.  There's likely a way to get the data if you 
> can describe it.  Describe the layout of the data in the sheet, with 
> examples.
>
> -- 
> Earl Kiosterud
> www.smokeylake.com/
> -------------------------------------------
>
> "Art MacNeil" <artmacneil@shaw.ca> wrote in message 
> news:TJIye.1874188$6l.1636299@pd7tw2no...
>> Hi all,
>>
>>  I'm trying to use the "Import External Data" function in Excel 2003 to 
>> import data from one spreadsheet to another.  It works for some fields 
>> (specifically ,date and text fields), but for others no data is imported 
>> (specifically, whole numbers, percentage fields and dollar fields).  Is 
>> there some way to import the missing data?
>>
>> Thank you,
>>
>> Art.
>>
>
> 


0
artmacneil (88)
7/8/2005 11:05:07 PM
Art,

If you're going to bring in 400 rows * 400 sheets, you'll end up with 
160,000 Rows, over twice what a worksheet will hold.

-- 
Earl Kiosterud
www.smokeylake.com/
-------------------------------------------

"Art MacNeil" <artmacneil@shaw.ca> wrote in message 
news:DEDze.1911987$6l.1704009@pd7tw2no...
> Well thank God for that!!
>
> Here's how all the spreadsheets (almost 400 of them) are laid out.
>
> All spreadsheets are about 400 lines long.
>
> Column A - always text, though sometimes formulas are used (concatenate, 
> etc) and some have conditional formatting.
>
> Column B - is the primary column.  It always has calcs.   The results can 
> be text, whole number (i.e. the year: 2005), dac (dollars and cents), date 
> fields (dd/mm/yyyy) or percent fields.
>
> There are other columns C through R, but they all lead back to Column B. 
> (Columns C and D have calcs, the rest do not).
>
> All spreadsheets are as I've described above.  I have about 400 of them. 
> Each has a tab for reference data.  This "RD" tab is what I've described 
> above.
>
> So the ideal solution would be to maintain 1 RD Tab and have each of the 
> 400 or so spreadsheets import the external data from that 1 tab.  The RD 
> tabs are all identical, so if this can be done it would save considerable 
> time.
>
> Thanks for your help,
>
> Art.
>
>
> "Earl Kiosterud" <nothanks@nospam.com> wrote in message 
> news:OfSI1UqgFHA.2880@TK2MSFTNGP14.phx.gbl...
>> Art,
>>
>> Import generally means reading data that isn't native to the program, 
>> like a text file into an Excel sheet.  But your source data is a 
>> spreadsheet. Excel workbook?  Say more.  There's likely a way to get the 
>> data if you can describe it.  Describe the layout of the data in the 
>> sheet, with examples.
>>
>> -- 
>> Earl Kiosterud
>> www.smokeylake.com/
>> -------------------------------------------
>>
>> "Art MacNeil" <artmacneil@shaw.ca> wrote in message 
>> news:TJIye.1874188$6l.1636299@pd7tw2no...
>>> Hi all,
>>>
>>>  I'm trying to use the "Import External Data" function in Excel 2003 to 
>>> import data from one spreadsheet to another.  It works for some fields 
>>> (specifically ,date and text fields), but for others no data is imported 
>>> (specifically, whole numbers, percentage fields and dollar fields).  Is 
>>> there some way to import the missing data?
>>>
>>> Thank you,
>>>
>>> Art.
>>>
>>
>>
>
> 


0
nothanks4548 (968)
7/9/2005 12:10:59 AM
Thanks Earl,

  Sorry, I'll try to be clear here:

I have 400 separate spreadsheets and one that I update as the year 
progresses.  Then in the spring or summer, the 400 separate spreadsheets get 
updated.  So at the moment, I copy the data on the main spreadsheet to each 
of the 400 spreadsheets in turn and I use "paste special" to paste the data 
in Columns E through R. I have Calcs in Columns B, C and D on each of the 
400 spreadsheets.

Hope this clears things up a bit.

Art.

"Earl Kiosterud" <nothanks@nospam.com> wrote in message 
news:eiWIyqBhFHA.2852@TK2MSFTNGP15.phx.gbl...
> Art,
>
> If you're going to bring in 400 rows * 400 sheets, you'll end up with 
> 160,000 Rows, over twice what a worksheet will hold.
>
> -- 
> Earl Kiosterud
> www.smokeylake.com/
> -------------------------------------------
>
> "Art MacNeil" <artmacneil@shaw.ca> wrote in message 
> news:DEDze.1911987$6l.1704009@pd7tw2no...
>> Well thank God for that!!
>>
>> Here's how all the spreadsheets (almost 400 of them) are laid out.
>>
>> All spreadsheets are about 400 lines long.
>>
>> Column A - always text, though sometimes formulas are used (concatenate, 
>> etc) and some have conditional formatting.
>>
>> Column B - is the primary column.  It always has calcs.   The results can 
>> be text, whole number (i.e. the year: 2005), dac (dollars and cents), 
>> date fields (dd/mm/yyyy) or percent fields.
>>
>> There are other columns C through R, but they all lead back to Column B. 
>> (Columns C and D have calcs, the rest do not).
>>
>> All spreadsheets are as I've described above.  I have about 400 of them. 
>> Each has a tab for reference data.  This "RD" tab is what I've described 
>> above.
>>
>> So the ideal solution would be to maintain 1 RD Tab and have each of the 
>> 400 or so spreadsheets import the external data from that 1 tab.  The RD 
>> tabs are all identical, so if this can be done it would save considerable 
>> time.
>>
>> Thanks for your help,
>>
>> Art.
>>
>>
>> "Earl Kiosterud" <nothanks@nospam.com> wrote in message 
>> news:OfSI1UqgFHA.2880@TK2MSFTNGP14.phx.gbl...
>>> Art,
>>>
>>> Import generally means reading data that isn't native to the program, 
>>> like a text file into an Excel sheet.  But your source data is a 
>>> spreadsheet. Excel workbook?  Say more.  There's likely a way to get the 
>>> data if you can describe it.  Describe the layout of the data in the 
>>> sheet, with examples.
>>>
>>> -- 
>>> Earl Kiosterud
>>> www.smokeylake.com/
>>> -------------------------------------------
>>>
>>> "Art MacNeil" <artmacneil@shaw.ca> wrote in message 
>>> news:TJIye.1874188$6l.1636299@pd7tw2no...
>>>> Hi all,
>>>>
>>>>  I'm trying to use the "Import External Data" function in Excel 2003 to 
>>>> import data from one spreadsheet to another.  It works for some fields 
>>>> (specifically ,date and text fields), but for others no data is 
>>>> imported (specifically, whole numbers, percentage fields and dollar 
>>>> fields).  Is there some way to import the missing data?
>>>>
>>>> Thank you,
>>>>
>>>> Art.
>>>>
>>>
>>>
>>
>>
>
> 


0
artmacneil (88)
7/9/2005 3:44:28 PM
Would VLOOKUP work,  perhaps from a frozen copy of your main
spreadsheet.
    http://www.mvps.org/dmcritchie/excel/vlookup.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"Art MacNeil" <artmacneil@shaw.ca> wrote in message news:whSze.210487$El.80080@pd7tw1no...
> Thanks Earl,
>
>   Sorry, I'll try to be clear here:
>
> I have 400 separate spreadsheets and one that I update as the year
> progresses.  Then in the spring or summer, the 400 separate spreadsheets get
> updated.  So at the moment, I copy the data on the main spreadsheet to each
> of the 400 spreadsheets in turn and I use "paste special" to paste the data
> in Columns E through R. I have Calcs in Columns B, C and D on each of the
> 400 spreadsheets.
>
> Hope this clears things up a bit.
>
> Art.
>
> "Earl Kiosterud" <nothanks@nospam.com> wrote in message
> news:eiWIyqBhFHA.2852@TK2MSFTNGP15.phx.gbl...
> > Art,
> >
> > If you're going to bring in 400 rows * 400 sheets, you'll end up with
> > 160,000 Rows, over twice what a worksheet will hold.
> >
> > -- 
> > Earl Kiosterud
> > www.smokeylake.com/
> > -------------------------------------------
> >
> > "Art MacNeil" <artmacneil@shaw.ca> wrote in message
> > news:DEDze.1911987$6l.1704009@pd7tw2no...
> >> Well thank God for that!!
> >>
> >> Here's how all the spreadsheets (almost 400 of them) are laid out.
> >>
> >> All spreadsheets are about 400 lines long.
> >>
> >> Column A - always text, though sometimes formulas are used (concatenate,
> >> etc) and some have conditional formatting.
> >>
> >> Column B - is the primary column.  It always has calcs.   The results can
> >> be text, whole number (i.e. the year: 2005), dac (dollars and cents),
> >> date fields (dd/mm/yyyy) or percent fields.
> >>
> >> There are other columns C through R, but they all lead back to Column B.
> >> (Columns C and D have calcs, the rest do not).
> >>
> >> All spreadsheets are as I've described above.  I have about 400 of them.
> >> Each has a tab for reference data.  This "RD" tab is what I've described
> >> above.
> >>
> >> So the ideal solution would be to maintain 1 RD Tab and have each of the
> >> 400 or so spreadsheets import the external data from that 1 tab.  The RD
> >> tabs are all identical, so if this can be done it would save considerable
> >> time.
> >>
> >> Thanks for your help,
> >>
> >> Art.
> >>
> >>
> >> "Earl Kiosterud" <nothanks@nospam.com> wrote in message
> >> news:OfSI1UqgFHA.2880@TK2MSFTNGP14.phx.gbl...
> >>> Art,
> >>>
> >>> Import generally means reading data that isn't native to the program,
> >>> like a text file into an Excel sheet.  But your source data is a
> >>> spreadsheet. Excel workbook?  Say more.  There's likely a way to get the
> >>> data if you can describe it.  Describe the layout of the data in the
> >>> sheet, with examples.
> >>>
> >>> -- 
> >>> Earl Kiosterud
> >>> www.smokeylake.com/
> >>> -------------------------------------------
> >>>
> >>> "Art MacNeil" <artmacneil@shaw.ca> wrote in message
> >>> news:TJIye.1874188$6l.1636299@pd7tw2no...
> >>>> Hi all,
> >>>>
> >>>>  I'm trying to use the "Import External Data" function in Excel 2003 to
> >>>> import data from one spreadsheet to another.  It works for some fields
> >>>> (specifically ,date and text fields), but for others no data is
> >>>> imported (specifically, whole numbers, percentage fields and dollar
> >>>> fields).  Is there some way to import the missing data?
> >>>>
> >>>> Thank you,
> >>>>
> >>>> Art.
> >>>>
> >>>
> >>>
> >>
> >>
> >
> >
>
>


0
dmcritchie (2586)
7/9/2005 3:57:16 PM
If I understand what you're saying, you want the same cell on *each* of your
400 sheets, to reference the *same, single* cell on the main sheet, and you
accomplish this now by completing a "once-a-year" copy and paste.

What would happen if you *linked* the cells on the 400 sheets to the main
sheet, and these 400 sheets were *constantly* being updated automatically,
as you revised the data in the main sheet?

Would that be acceptable?
-- 

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

"Art MacNeil" <artmacneil@shaw.ca> wrote in message
news:whSze.210487$El.80080@pd7tw1no...
Thanks Earl,

  Sorry, I'll try to be clear here:

I have 400 separate spreadsheets and one that I update as the year
progresses.  Then in the spring or summer, the 400 separate spreadsheets get
updated.  So at the moment, I copy the data on the main spreadsheet to each
of the 400 spreadsheets in turn and I use "paste special" to paste the data
in Columns E through R. I have Calcs in Columns B, C and D on each of the
400 spreadsheets.

Hope this clears things up a bit.

Art.

"Earl Kiosterud" <nothanks@nospam.com> wrote in message
news:eiWIyqBhFHA.2852@TK2MSFTNGP15.phx.gbl...
> Art,
>
> If you're going to bring in 400 rows * 400 sheets, you'll end up with
> 160,000 Rows, over twice what a worksheet will hold.
>
> -- 
> Earl Kiosterud
> www.smokeylake.com/
> -------------------------------------------
>
> "Art MacNeil" <artmacneil@shaw.ca> wrote in message
> news:DEDze.1911987$6l.1704009@pd7tw2no...
>> Well thank God for that!!
>>
>> Here's how all the spreadsheets (almost 400 of them) are laid out.
>>
>> All spreadsheets are about 400 lines long.
>>
>> Column A - always text, though sometimes formulas are used (concatenate,
>> etc) and some have conditional formatting.
>>
>> Column B - is the primary column.  It always has calcs.   The results can
>> be text, whole number (i.e. the year: 2005), dac (dollars and cents),
>> date fields (dd/mm/yyyy) or percent fields.
>>
>> There are other columns C through R, but they all lead back to Column B.
>> (Columns C and D have calcs, the rest do not).
>>
>> All spreadsheets are as I've described above.  I have about 400 of them.
>> Each has a tab for reference data.  This "RD" tab is what I've described
>> above.
>>
>> So the ideal solution would be to maintain 1 RD Tab and have each of the
>> 400 or so spreadsheets import the external data from that 1 tab.  The RD
>> tabs are all identical, so if this can be done it would save considerable
>> time.
>>
>> Thanks for your help,
>>
>> Art.
>>
>>
>> "Earl Kiosterud" <nothanks@nospam.com> wrote in message
>> news:OfSI1UqgFHA.2880@TK2MSFTNGP14.phx.gbl...
>>> Art,
>>>
>>> Import generally means reading data that isn't native to the program,
>>> like a text file into an Excel sheet.  But your source data is a
>>> spreadsheet. Excel workbook?  Say more.  There's likely a way to get the
>>> data if you can describe it.  Describe the layout of the data in the
>>> sheet, with examples.
>>>
>>> -- 
>>> Earl Kiosterud
>>> www.smokeylake.com/
>>> -------------------------------------------
>>>
>>> "Art MacNeil" <artmacneil@shaw.ca> wrote in message
>>> news:TJIye.1874188$6l.1636299@pd7tw2no...
>>>> Hi all,
>>>>
>>>>  I'm trying to use the "Import External Data" function in Excel 2003 to
>>>> import data from one spreadsheet to another.  It works for some fields
>>>> (specifically ,date and text fields), but for others no data is
>>>> imported (specifically, whole numbers, percentage fields and dollar
>>>> fields).  Is there some way to import the missing data?
>>>>
>>>> Thank you,
>>>>
>>>> Art.
>>>>
>>>
>>>
>>
>>
>
>



0
ragdyer1 (4060)
7/9/2005 4:17:29 PM
Art,

I missed that you're "broadcasting" the data from the master workbook to 400 
other workbooks.  At least I presume the 400 sheets you mention are in 
separate workbooks (since you're considering the Import command).

Any importing would have to be carried out from each workbook; there isn't 
an Excel command that will stuff other workbooks.  That'd leave you the 
nearly 400 workbooks in  which to set up an import.  Your original question 
dealt with some types of data not being imported, and that ought to work 
properly, but we'll hold off on that until this is cleared up a bit.

Or a very specific macro could broadcast the data from the master workbook 
to the 400 workbooks, much like you're doing manually with copy/paste.  But 
there are many things to consider for automating that approach.
-- 
Earl Kiosterud
www.smokeylake.com/
-------------------------------------------

"Art MacNeil" <artmacneil@shaw.ca> wrote in message 
news:whSze.210487$El.80080@pd7tw1no...
> Thanks Earl,
>
>  Sorry, I'll try to be clear here:
>
> I have 400 separate spreadsheets and one that I update as the year 
> progresses.  Then in the spring or summer, the 400 separate spreadsheets 
> get updated.  So at the moment, I copy the data on the main spreadsheet to 
> each of the 400 spreadsheets in turn and I use "paste special" to paste 
> the data in Columns E through R. I have Calcs in Columns B, C and D on 
> each of the 400 spreadsheets.
>
> Hope this clears things up a bit.
>
> Art.
>
> "Earl Kiosterud" <nothanks@nospam.com> wrote in message 
> news:eiWIyqBhFHA.2852@TK2MSFTNGP15.phx.gbl...
>> Art,
>>
>> If you're going to bring in 400 rows * 400 sheets, you'll end up with 
>> 160,000 Rows, over twice what a worksheet will hold.
>>
>> -- 
>> Earl Kiosterud
>> www.smokeylake.com/
>> -------------------------------------------
>>
>> "Art MacNeil" <artmacneil@shaw.ca> wrote in message 
>> news:DEDze.1911987$6l.1704009@pd7tw2no...
>>> Well thank God for that!!
>>>
>>> Here's how all the spreadsheets (almost 400 of them) are laid out.
>>>
>>> All spreadsheets are about 400 lines long.
>>>
>>> Column A - always text, though sometimes formulas are used (concatenate, 
>>> etc) and some have conditional formatting.
>>>
>>> Column B - is the primary column.  It always has calcs.   The results 
>>> can be text, whole number (i.e. the year: 2005), dac (dollars and 
>>> cents), date fields (dd/mm/yyyy) or percent fields.
>>>
>>> There are other columns C through R, but they all lead back to Column B. 
>>> (Columns C and D have calcs, the rest do not).
>>>
>>> All spreadsheets are as I've described above.  I have about 400 of them. 
>>> Each has a tab for reference data.  This "RD" tab is what I've described 
>>> above.
>>>
>>> So the ideal solution would be to maintain 1 RD Tab and have each of the 
>>> 400 or so spreadsheets import the external data from that 1 tab.  The RD 
>>> tabs are all identical, so if this can be done it would save 
>>> considerable time.
>>>
>>> Thanks for your help,
>>>
>>> Art.
>>>
>>>
>>> "Earl Kiosterud" <nothanks@nospam.com> wrote in message 
>>> news:OfSI1UqgFHA.2880@TK2MSFTNGP14.phx.gbl...
>>>> Art,
>>>>
>>>> Import generally means reading data that isn't native to the program, 
>>>> like a text file into an Excel sheet.  But your source data is a 
>>>> spreadsheet. Excel workbook?  Say more.  There's likely a way to get 
>>>> the data if you can describe it.  Describe the layout of the data in 
>>>> the sheet, with examples.
>>>>
>>>> -- 
>>>> Earl Kiosterud
>>>> www.smokeylake.com/
>>>> -------------------------------------------
>>>>
>>>> "Art MacNeil" <artmacneil@shaw.ca> wrote in message 
>>>> news:TJIye.1874188$6l.1636299@pd7tw2no...
>>>>> Hi all,
>>>>>
>>>>>  I'm trying to use the "Import External Data" function in Excel 2003 
>>>>> to import data from one spreadsheet to another.  It works for some 
>>>>> fields (specifically ,date and text fields), but for others no data is 
>>>>> imported (specifically, whole numbers, percentage fields and dollar 
>>>>> fields).  Is there some way to import the missing data?
>>>>>
>>>>> Thank you,
>>>>>
>>>>> Art.
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
> 


0
nothanks4548 (968)
7/9/2005 4:34:34 PM
Thanks all, for your questions,

  I tried vlookup and linking directly to the other worksheets a few years 
ago and ran into problems.  Now that all the RD tabs on all 400 spreadsheets 
are identical, they may now work but I'd prefer getting "Import External 
Data" to work. Yes, the 400 sheets I mentioned are in separate workbooks and 
I don't mind setting up 400 separate imports. As I understand it, I'd just 
have to do it once and any updates to the main spreadsheet would be imported 
to the 400 other spreadsheets once they were accessed.

Again, thanks for your help,

Art.

"Earl Kiosterud" <nothanks@nospam.com> wrote in message 
news:%23gOAaQKhFHA.3540@TK2MSFTNGP14.phx.gbl...
> Art,
>
> I missed that you're "broadcasting" the data from the master workbook to 
> 400 other workbooks.  At least I presume the 400 sheets you mention are in 
> separate workbooks (since you're considering the Import command).
>
> Any importing would have to be carried out from each workbook; there isn't 
> an Excel command that will stuff other workbooks.  That'd leave you the 
> nearly 400 workbooks in  which to set up an import.  Your original 
> question dealt with some types of data not being imported, and that ought 
> to work properly, but we'll hold off on that until this is cleared up a 
> bit.
>
> Or a very specific macro could broadcast the data from the master workbook 
> to the 400 workbooks, much like you're doing manually with copy/paste. 
> But there are many things to consider for automating that approach.
> -- 
> Earl Kiosterud
> www.smokeylake.com/
> -------------------------------------------
>
> "Art MacNeil" <artmacneil@shaw.ca> wrote in message 
> news:whSze.210487$El.80080@pd7tw1no...
>> Thanks Earl,
>>
>>  Sorry, I'll try to be clear here:
>>
>> I have 400 separate spreadsheets and one that I update as the year 
>> progresses.  Then in the spring or summer, the 400 separate spreadsheets 
>> get updated.  So at the moment, I copy the data on the main spreadsheet 
>> to each of the 400 spreadsheets in turn and I use "paste special" to 
>> paste the data in Columns E through R. I have Calcs in Columns B, C and D 
>> on each of the 400 spreadsheets.
>>
>> Hope this clears things up a bit.
>>
>> Art.
>>
>> "Earl Kiosterud" <nothanks@nospam.com> wrote in message 
>> news:eiWIyqBhFHA.2852@TK2MSFTNGP15.phx.gbl...
>>> Art,
>>>
>>> If you're going to bring in 400 rows * 400 sheets, you'll end up with 
>>> 160,000 Rows, over twice what a worksheet will hold.
>>>
>>> -- 
>>> Earl Kiosterud
>>> www.smokeylake.com/
>>> -------------------------------------------
>>>
>>> "Art MacNeil" <artmacneil@shaw.ca> wrote in message 
>>> news:DEDze.1911987$6l.1704009@pd7tw2no...
>>>> Well thank God for that!!
>>>>
>>>> Here's how all the spreadsheets (almost 400 of them) are laid out.
>>>>
>>>> All spreadsheets are about 400 lines long.
>>>>
>>>> Column A - always text, though sometimes formulas are used 
>>>> (concatenate, etc) and some have conditional formatting.
>>>>
>>>> Column B - is the primary column.  It always has calcs.   The results 
>>>> can be text, whole number (i.e. the year: 2005), dac (dollars and 
>>>> cents), date fields (dd/mm/yyyy) or percent fields.
>>>>
>>>> There are other columns C through R, but they all lead back to Column 
>>>> B. (Columns C and D have calcs, the rest do not).
>>>>
>>>> All spreadsheets are as I've described above.  I have about 400 of 
>>>> them. Each has a tab for reference data.  This "RD" tab is what I've 
>>>> described above.
>>>>
>>>> So the ideal solution would be to maintain 1 RD Tab and have each of 
>>>> the 400 or so spreadsheets import the external data from that 1 tab. 
>>>> The RD tabs are all identical, so if this can be done it would save 
>>>> considerable time.
>>>>
>>>> Thanks for your help,
>>>>
>>>> Art.
>>>>
>>>>
>>>> "Earl Kiosterud" <nothanks@nospam.com> wrote in message 
>>>> news:OfSI1UqgFHA.2880@TK2MSFTNGP14.phx.gbl...
>>>>> Art,
>>>>>
>>>>> Import generally means reading data that isn't native to the program, 
>>>>> like a text file into an Excel sheet.  But your source data is a 
>>>>> spreadsheet. Excel workbook?  Say more.  There's likely a way to get 
>>>>> the data if you can describe it.  Describe the layout of the data in 
>>>>> the sheet, with examples.
>>>>>
>>>>> -- 
>>>>> Earl Kiosterud
>>>>> www.smokeylake.com/
>>>>> -------------------------------------------
>>>>>
>>>>> "Art MacNeil" <artmacneil@shaw.ca> wrote in message 
>>>>> news:TJIye.1874188$6l.1636299@pd7tw2no...
>>>>>> Hi all,
>>>>>>
>>>>>>  I'm trying to use the "Import External Data" function in Excel 2003 
>>>>>> to import data from one spreadsheet to another.  It works for some 
>>>>>> fields (specifically ,date and text fields), but for others no data 
>>>>>> is imported (specifically, whole numbers, percentage fields and 
>>>>>> dollar fields).  Is there some way to import the missing data?
>>>>>>
>>>>>> Thank you,
>>>>>>
>>>>>> Art.
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
> 


0
artmacneil (88)
7/9/2005 5:31:42 PM
Reply:

Similar Artilces:

Pivot Table Source Data
I have an existing Pivot Table that usually updates daily, albeit this relies someone in the office refreshing the data. Anyway we seem to have missed one days data and thought we would be clever and just include the days missing data back into the source data. In effect we are putting the 8th September data between the 7th and the 9th. However no matter what we do the data from the 8th ends up as the last column in the pivot table. Are we missing something really simple here or has my brain turned to mush? thanks for the help in advance. Bryan P.S I also tried creating a dynamic range but ...

EXCEL tabular data extract
Hi all! Hi have a excel file with the next kind of data: T�TULO MINORISTA NOMBRE XXXXXXXXX CENT./SUC. RRRRRRRR DIRECCI�N TTTTTTTT LOCALIDAD VVVVVVVVVVV PROVINCIA SSSSSSSSSSS C.P. 11111 TEL�FONO 4323543543654364 FAX 435435454543543543 T�TULO MINORISTA NOMBRE XXXXXXXXX CENT./SUC. RRRRRRRR DIRECCI�N TTTTTTTT LOCALIDAD VVVVVVVVVVV PROVINCIA SSSSSSSSSSS C.P. 11111 TEL�FONO 4323543543654364 FAX 435435454543543543 I want to extract all the faxes from that table, bu the problem is that all...

how do i create an office data connection file
i want to call a parameterized data connection. i want to eventually put this in office sharepoint server 2007's data connection library. every time i open the connection file i have successfully created that connects to the db, it want me to pick a table. i can cancel out of that and change it to sql and say to use a procedure, but i can't save this file, and the "Edit Query..." and "Parameters..." buttons are disabled so i can't set parameters. why is it so hard to work with this thing? thanks ...

Importing a .cdr file in Publisher 2007
Hi there, someone has sent me a .cdr file, is there a way to import it into Publisher? Thanks, Ulli You can import a .cdr file in 2007. Not sure about the version however. I cannot import CorelDraw 12 images into Publisher. Your sender can convert the .cdr in Corel to a .wmf or .emf or any other format. Try importing, Insert, picture, from file. If your sender cannot convert the file, send it to me and I will convert it for you. mary-sauer at columbus.rr.com -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Ulli" <Ul...

Stripping Blank Characters form Imported Data
I am trying to verify 2 worksheets full of data. One has been imported from Visual Basic and the other from Business Object. The Business object sheet, brings over the entire field not just the charaters needed. Example: For the city field there are 50 characters available. If you use Nashville, the Business Object brings over 50 characters where the Visual Basic brings over 9. When trying to find descrepancies, this brings back a false positive if the city is the same. Is there a way to manipulate that data when it is sent to Excel? The easiest way to deal with this would be to find...

Retrieving (re-importing) .pst archive files in Outlook 2000
Can anyone tell me how to import .pst archive files in Outlook 2000? I have tried Import from another program or file/Personal Folder File (.pst)/File to import/Browse => C:Documents and Settings/User(My name)/Application data/Microsoft/Outlook ... and can't see any .pst files. In Windows Explorer, a search in the same location (i.e C:Documents and Settings/User(My name)/Application data/Microsoft/Outlook) reveals .FAV, .LOG, .DAT and other files, but none of my .pst archive files. I have ensured that "Show hidden files and folders" is switched on in Tools/Folder...

Data Tables
In Excel 2000, must data tables (Data\Tables...) always be located on the same worksheet tab as the source data? I want to put summary tables into a separate worksheet (tab), but Excel won't let me do that. Steve Looks that way, at least in Excel97 (my ver) But we could always mirror the data tables (or parts thereof) elsewhere (eg: another sheet) via simple link formulas -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Steve D" <sdrenker_(you_know_what_here)_media.sj.nec.com> wrote in message news:ufhMsZfyFHA.3804@TK2MSFTNGP10....

Data Validation problem #5
hi I think that is a different solution entirely - in that example yo gave me it is a simple case of choosing one list and that then sets th range for list 2, however the second set of lists are all individual my requirement is that the validation picks the required items out o just ONE list, whereas in the example you needed to maintain individua lists each of which was dependant on the first selection -- moosife ----------------------------------------------------------------------- moosifer's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1590 View this threa...

How do I limit labels in chart legend to only a portion of data?
I am hoping to get a chart that includes four lines of data, but with only two of the line labels for the data showing up in the chart legend. The two lines that I want excluded are just straight bars that will include a text box within the chart as their label, but I have run into problems with simply slapping a line on the chart as it does not move around when we scale up or down the two axes. Is there a way to get the legend to only show the label identifiers with only the two sets of data that I want shown? Thank you Hi, The legend displays an entry for each series in the chart....

delete data #2
Hello, we have CRM v3 installed in a test evironment. we have added test data for customers, products. we have created oppurtunities to invoices. we would like to delete all of this data. is there a way to do it without uninsalling and reinstalling the software? cheers, Michael. Try to delete via the UI from Advanced Find. For some of the object (Order, Invoices, Quote, etc.), depending on status some of these records cannot be deleted. Frank Lee, Microsoft CRM MVP http://microsoft-crm.spaces.live.com http://www.workopia.com/Links.htmTry "Michael P. Lindekugel" wrote:...

Merge data
..Net 2003 C# I get xml from a sql database pull. I put the data into a XMLDomDocument and then the user changes it. I now need to update the database correctly. I need to be able to get the data back correctly including add, change and delete to the original data. When I pull the data from the db, I store a copy in a Dataset. So I end up with a Dataset (original data) and an XML DOM Document with the changed data. I would appreciate knowing how to do this correctly. I understand that the DataSet.Merge does not work for some of this. Is there an example? thank you If you keep the dat...

Formatting number in Data Table
I am using Excel 2007. I have a spreadsheet that the figures can be negative or positve. I have formatted the cells with this custom format $#,##0;[Red]$-#,##0. On the spreadsheet the format works fine. I created a chart from the data. I have a data table at the bottom of the chart. The problem is that the format from the spreadsheet is not reflected in the data table. When I click on Format Data Table, there is no option for number formatting. There is a number formatting option available when i click Format Axis, but this doesn't change it, because the actual axis is Text. Is th...

Copying Data From SQL Into Excel
When I copy a range of data from SQL and paste it into Excel, the data doesn't appear to be available for formulas - in this case a VLOOKUP formula. However, once I click in the formula bar (as if to edit the data) then hit "return" the data is "magically" available for the VLOOKUP formula. It seems to me to be a format problem, but changing the format of the data doesn't help. Could this be related to similar issues when copying data from Access into Excel? Are there any workarounds? It sounds similar to the Access problem. Instead of pasting, you can ...

master list for auto complete data?
Is it possible to have a master list, in my case it's of names, in a workbook that excel will use for auto complete data for that workbook? Thanks Hi Tangy Go to "Tools>Auto Correct Options", select the "Auto Correct" tab and enter the abbreviated items and full names for those items you want Excel to replace. Ensure the relevant boxes are ticked. -- XL2002 Regards William willwest22@yahoo.com "Tangy" <t a n g y@rogers.com> wrote in message news:_OqdnbKPa7IShELcRVn-jQ@rogers.com... | Is it possible to have a master list, in my case it'...

Data from non-adjacent cells as input for Linest
Hi, I would like to use data as input for Linest that is in the following cells: known y: A1 to A3, A10 to A12 known x: B1 to B3, B10 to B12 I have the feeling that this is almost answered in the following posts, but I do not understand it: http://groups.google.com/group/microsoft.public.excel/browse_thread/thread/fe4463f591797f44/4061a15313417929?lnk=gst&q=linest+#4061a15313417929 Regards, David ...

copy and match data from one worksheet into another via script?
Hi all I have two worksheets 2 columns each The first sheet has ID and Name Column for example: Code ------------------- ID[/B] NAM 193948 Michael 684588 John 535279 Luke 098734 Matthe ------------------- my 2nd sheet Code ------------------- ID [B]NAM Michael John Luke Matthe ------------------- How do i use the first sheet to match up the names on the 2nd sheet and ultimately copy the ID numbers onto the 2nd sheet? This is just a simplified example. There are thousands of entries. Thank you if you have ...

LNK2001: unresolved external
I've hit a brick wall with this one. I have a project that compiles and runs fine in debug mode. I switch to release build the linker fails claiming it can't find one of my class' functions. Which is plainly there. It is always the destructor it can't find i.e if I bin my class and create an entirely new one with just a constructor and destructor that too fails to link, claiming it cant find the constructor. I've never seen this behavour before, what can I have done wrong ??? If I delete the destructors implementation from the relevent CPP file and put it directly ...

Import from Mozilla
What is the simplest way to import Mozilla local folders into Outlook/Exchange. This needs to be done on a number of machines, Many Thanks, Marcus Robinson ...

Automatically import/export workflow
Hello. I'm working on a redeploiment tool for CRM 3.0 pro. I mean a tool to automatically import all customizations (customizations, callouts, workflow, data...) of a CRM to another one. Is there a way to automatically (from a program, not manually) import/export workflow rules ? I know there exist web services for callouts import/export, is there the same for workflow rules. Regards, I don't think so. Workflow import/export is handled by a separate application and cannot be accessed through any of the CRM web services -- David Jennaway - Microsoft Dynamics CRM MVP Web: http://...

how to deal with duplicate data rows
I need to interface with a program that generates a .csv file with row of data. It generates 2 or more rows for each instance uniquely identified by the docket/page combination Example: last first bank trustee docket page smith john ny bank (blank field) 12235 8907 (blank field) (blank field) cal western 12235 8907 smith janet ny bank (blank field) 12235 8907 I need to consolidate all three lines into one row by concating field that contain different data (first name column) and columns with blan spaces to look like the ...

Merge & Sort Dynamic Lists w/ Data Validation
First off, let me say thanks to the Excel Usenet community that has helped me over and over through these newsgroups. You are phenomenal. Question 1: I need to create a BOTH box that will dynamically pull names from both the "Chicago Office" & "Seattle Office" columns in my sheet. It is extremely preferable to do this without VB Script if possible to avoid the security warnings on opening the sheet. (Our IT department will not budge on this..) Detail: I have two lists of employees. Column A lists the Chicago Office employees. Column B lists the Seattle Office employee...

Importing data from an excel file
I have an excel file with contact data for customers. Now I want to import it into CRM 3.0. I use the guide but I am stuck at the manual import i.e. populate CDF at step 5 in the data migration framework application. Do I have to go to the SQL server and select the database and import into the contacts table? I am really sorry for the newbie question.. /Jonas Hi Jonas, You will indeed need to populate the CDF tables. You can do this via several ways of which using SSIS is my personal favorite. Hope this helps, -- Ronald Lemmen - MSCRM MVP Avanade Netherlands http://ronaldlemmen.blog...

Outlook Express Imported Email
I've recently imported mail from Outlook express to Outlook 2007. The "From" fied of all the old imported email does not display the email address. It just shows the sends full name. I therefore cannot reply to old email or click on the From field and add the email address to my contacts. Anyone come across this problem before? thanks, "Pryan" <Pryan@discussions.microsoft.com> wrote in message news:8B86DF03-5BC4-40B3-ADE4-F73674833190@microsoft.com... > I've recently imported mail from Outlook express to Outlook 2007. It's be...

Import Messages from Outlook Express 6
Hi, I would like some advice on importing messages that are currently on a Win98se machine running OE 6 into an XP Pro machine running Outlook 2003, Thanks, any help greatly appriciated, Ed <anonymous@discussions.microsoft.com> wrote in message news:5c9c01c3e5bb$834ea400$a001280a@phx.gbl... > Hi, > I would like some advice on importing messages that are > currently on a Win98se machine running OE 6 into an XP > Pro machine running Outlook 2003, > Thanks, any help greatly appriciated, > Ed Go here to backup your old OE: http://insideoe.tomsterdam.com/backup/index.htm...

Importing text files into Excel
Does anyone know of a quick and easy way to bring large text files into Excel. The data in the text files is separated by spaces. The spaces that separate the data vary in number. I have tried to replace the spaces with tabs while in Notepad but it takes too long. Any help would be appreciated! Thanks Chris Open Excel and create a blank workbook. Then: Data | Get External Data | Import Data (this command might be something like Import Text File, if you are using an older version of Excel). Browse to find your file and click to Import it. Should open up the Text Import Wizard. Make...