Import External Data

[Art MacNeil]

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.

 

[aaron.kempf]

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

 

[Art MacNeil]

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.

 

[Earl Kiosterud]

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.

 

[Nick Hodge]

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
(e-mail address removed)

 

[Art MacNeil]

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]

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.

 

[Art MacNeil]

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.

 

[David McRitchie]

Would VLOOKUP work, perhaps from a frozen copy of your main
spreadsheet.
http://www.mvps.org/dmcritchie/excel/vlookup.htm

 

[RagDyeR]

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 !
--------------------------------------------------------------------

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]

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.

 

[Art MacNeil]

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.