Importing unopened worksheet into an open Workbook

If an unopened Excel Workbook only contains a single worksheet, is it
possible to import the data from that worksheet into another Workbook that is
open, without having to open the other file?

You can write formulas in the open workbook that refer to the cells in the 2nd
workbook, regardless of the number of worksheets it contains. This creates a
"link" from workbook #1 to #2.

If #2 isn't open when you open #1, you'll be asked whether to update the
information from #2. This will happen without you seeing #2 appear on the
screen.


On Sat, 5 Mar 2005 15:33:03 -0800, "GrayesGhost"
<[email protected]> wrote:

>If an unopened Excel Workbook only contains a single worksheet, is it
>possible to import the data from that worksheet into another Workbook that is
>open, without having to open the other file?

Myrna,

I appreciate the reply, but I don't want to link the two workbooks. I want
to physically insert the data from the unopend worksheet into a worksheet in
another Workbook that is already open (similar to importing a text file -
except I want to import an Excel Worksheet). Is it possible to do this?

Hi GrayestGhost-

Direct answer to your question is No. In order to move or copy sheets from
one file to another, both files must be open. Even if you move or copy to a
New workbook, tha wkbk will open as it is created.

HTH |:>)

"GrayesGhost" wrote:

> If an unopened Excel Workbook only contains a single worksheet, is it
> possible to import the data from that worksheet into another Workbook that is
> open, without having to open the other file?

Well, if you are talking about Data/Import External Data, that might be
possible.

If not, you would need to use a VBA macro do do this, but even then, you must
open the 2nd workbook. However, the macro can turn off screen updating so the
user doesn't see the 2nd book being opened and closed.

BTW, unless you are talking about Data/Import External Data, you can't import
a text file (#2) into an open workbook (#1) without a macro, either. If you
just open the text file (#2), it goes into its own workbook, so at that point
you have 2 workbooks open. To get it into book #1, you have to move or copy
the worksheet from #2 to #1.


On Sat, 5 Mar 2005 16:35:04 -0800, "GrayesGhost"
<[email protected]> wrote:

>Myrna,
>
>I appreciate the reply, but I don't want to link the two workbooks. I want
>to physically insert the data from the unopend worksheet into a worksheet in
>another Workbook that is already open (similar to importing a text file -
>except I want to import an Excel Worksheet). Is it possible to do this?
>

Taz,

That wasn't what I wanted to hear, but that does answer my question.

I am a long time Quattro Pro user who is, unfortunately, having to now learn
Excel.

There are a lot of similarities between the two spread sheets but the
differences are also as far apart as night and day. In Quattro Pro, it is
possible to "insert" an unopened file into an open worksheet without having
to first open the second file and then do a copy/paste. The project I am
currrently working on involves combining data from several different, single
sheet workbooks and that feature would be most helpful about now.

Thanks for the reply - GrayesGhost



"CyberTaz" wrote:

> Hi GrayestGhost-
>
> Direct answer to your question is No. In order to move or copy sheets from
> one file to another, both files must be open. Even if you move or copy to a
> New workbook, tha wkbk will open as it is created.
>
> HTH |:>)
>
> "GrayesGhost" wrote:
>
> > If an unopened Excel Workbook only contains a single worksheet, is it
> > possible to import the data from that worksheet into another Workbook that is
> > open, without having to open the other file?

The macro to do this would not be very complicated.

On Sat, 5 Mar 2005 19:25:02 -0800, "GrayesGhost"
<[email protected]> wrote:

>Taz,
>
>That wasn't what I wanted to hear, but that does answer my question.
>
>I am a long time Quattro Pro user who is, unfortunately, having to now learn
>Excel.
>
>There are a lot of similarities between the two spread sheets but the
>differences are also as far apart as night and day. In Quattro Pro, it is
>possible to "insert" an unopened file into an open worksheet without having
>to first open the second file and then do a copy/paste. The project I am
>currrently working on involves combining data from several different, single
>sheet workbooks and that feature would be most helpful about now.
>
>Thanks for the reply - GrayesGhost
>
>
>
>"CyberTaz" wrote:
>
>> Hi GrayestGhost-
>>
>> Direct answer to your question is No. In order to move or copy sheets from
>> one file to another, both files must be open. Even if you move or copy to a
>> New workbook, tha wkbk will open as it is created.
>>
>> HTH |:>)
>>
>> "GrayesGhost" wrote:
>>
>> > If an unopened Excel Workbook only contains a single worksheet, is it
>> > possible to import the data from that worksheet into another Workbook
that is
>> > open, without having to open the other file?

You haven't told us enough. If the other workbook contains a table, use
Data - Get external data. The process varies a bit with your release of
Excel. You never have to open the workbook, and can refresh the import to
reread the data at any time.

You can also create a link without ever having opened the workbook (though
it's easier if you do).
=[workbookname.xls]'Sheet 1'!A1

Now copy this down and across, and you have the data. Once done, you never
have to reopen the other workbook. A macro could parse the data, if
necessary, put it where you want it, and do any other tasks.

Without knowing more, that's the best I can suggest.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"GrayesGhost" <[email protected]> wrote in message
news:[email protected]...
> If an unopened Excel Workbook only contains a single worksheet, is it
> possible to import the data from that worksheet into another Workbook that
> is
> open, without having to open the other file?

I suggested formulas, too, but he rejected that with the statement that he
doesn't want to link the workbooks. I guess he's missing a feature from
Quattro (or Lotus?) that he used frequently.

It would be fairly simple to program what he wants.

On Sat, 5 Mar 2005 23:19:00 -0500, "Earl Kiosterud" <[email protected]>
wrote:

>You haven't told us enough. If the other workbook contains a table, use
>Data - Get external data. The process varies a bit with your release of
>Excel. You never have to open the workbook, and can refresh the import to
>reread the data at any time.
>
>You can also create a link without ever having opened the workbook (though
>it's easier if you do).
>=[workbookname.xls]'Sheet 1'!A1
>
>Now copy this down and across, and you have the data. Once done, you never
>have to reopen the other workbook. A macro could parse the data, if
>necessary, put it where you want it, and do any other tasks.
>
>Without knowing more, that's the best I can suggest.

Earl,

Maybe I don't know enough about Excell yet to word the question in a form
that is understandable. What I am trying to do is to copy some statistics
from several Workbooks that only contain a single work sheet. I want to
combine the worksheets from multiple Excell workbooks into seperate
worksheets in a single Workbook, and then get rid of all the original files.

I know I can open each workbook seperately, copy the data from that workbook
and paste it into seperate sheets in the file that I am compiling (that is
what I have been doing) but I know there has to be an easier way - such as
simply inserting the unopened file into a blank work sheet as I was able to
do in Quattro Pro.

(Menu: Insert/File/<filename>) = Inserted the desired file into one or more
worksheets depending on whether or not the inserted file was a text file or
an existing Quattro Pro workbook.

I am now using MS Office 2000 Pro.

I don't want to go to the trouble of writing formulas, or linking the
current workbook to files that I am going to destroy once I have transfered
the data - But I don't know of any other way to word the question - I think I
pretty much have the answer anyway though - apparently Excell doesn't have
the feature that I am looking for.

Thanks again - GG


"Earl Kiosterud" wrote:

> You haven't told us enough. If the other workbook contains a table, use
> Data - Get external data. The process varies a bit with your release of
> Excel. You never have to open the workbook, and can refresh the import to
> reread the data at any time.
>
> You can also create a link without ever having opened the workbook (though
> it's easier if you do).
> =[workbookname.xls]'Sheet 1'!A1
>
> Now copy this down and across, and you have the data. Once done, you never
> have to reopen the other workbook. A macro could parse the data, if
> necessary, put it where you want it, and do any other tasks.
>
> Without knowing more, that's the best I can suggest.
> --
> Earl Kiosterud
> mvpearl omitthisword at verizon period net
> -------------------------------------------
>
> "GrayesGhost" <[email protected]> wrote in message
> news:[email protected]...
> > If an unopened Excel Workbook only contains a single worksheet, is it
> > possible to import the data from that worksheet into another Workbook that
> > is
> > open, without having to open the other file?
>
>
>

GG,

We still don't know much about the data, and how it's to be consolidated.
There are macro possibilities, linking possibilities (you don't have to do
it each time if you rename your workbooks to that of the links), import
possibilities.

Is this a one-time job, or does data come in regularly, which needs to be
consolidated again and again?

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"GrayesGhost" <[email protected]> wrote in message
news:[email protected]...
> Earl,
>
> Maybe I don't know enough about Excell yet to word the question in a form
> that is understandable. What I am trying to do is to copy some statistics
> from several Workbooks that only contain a single work sheet. I want to
> combine the worksheets from multiple Excell workbooks into seperate
> worksheets in a single Workbook, and then get rid of all the original
> files.
>
> I know I can open each workbook seperately, copy the data from that
> workbook
> and paste it into seperate sheets in the file that I am compiling (that is
> what I have been doing) but I know there has to be an easier way - such as
> simply inserting the unopened file into a blank work sheet as I was able
> to
> do in Quattro Pro.
>
> (Menu: Insert/File/<filename>) = Inserted the desired file into one or
> more
> worksheets depending on whether or not the inserted file was a text file
> or
> an existing Quattro Pro workbook.
>
> I am now using MS Office 2000 Pro.
>
> I don't want to go to the trouble of writing formulas, or linking the
> current workbook to files that I am going to destroy once I have
> transfered
> the data - But I don't know of any other way to word the question - I
> think I
> pretty much have the answer anyway though - apparently Excell doesn't have
> the feature that I am looking for.
>
> Thanks again - GG
>
>
> "Earl Kiosterud" wrote:
>
>> You haven't told us enough. If the other workbook contains a table, use
>> Data - Get external data. The process varies a bit with your release of
>> Excel. You never have to open the workbook, and can refresh the import
>> to
>> reread the data at any time.
>>
>> You can also create a link without ever having opened the workbook
>> (though
>> it's easier if you do).
>> =[workbookname.xls]'Sheet 1'!A1
>>
>> Now copy this down and across, and you have the data. Once done, you
>> never
>> have to reopen the other workbook. A macro could parse the data, if
>> necessary, put it where you want it, and do any other tasks.
>>
>> Without knowing more, that's the best I can suggest.
>> --
>> Earl Kiosterud
>> mvpearl omitthisword at verizon period net
>> -------------------------------------------
>>
>> "GrayesGhost" <[email protected]> wrote in message
>> news:[email protected]...
>> > If an unopened Excel Workbook only contains a single worksheet, is it
>> > possible to import the data from that worksheet into another Workbook
>> > that
>> > is
>> > open, without having to open the other file?
>>
>>
>>

Hi,

I posted a similar question a few weeks ago on the programming part of this
forum.

I got this answer from Jamie Collins:

> can I copy data from one workbook to another (or for that matter
> from within a workbook) WITHOUT OPENING EACH WORKBOOK?

If your data is arranged as a database (i.e. rows of columns,
preferable with column headers) then yes.

A simple example to copy the entire contents of one table (worksheet)
to another workbook where the table does not already exist:

SELECT
MyKeyCol, MyDataCol
INTO
[Excel 8.0;HDR=YES;Database=C:\My
Folder\MyTargetWorkbook.xls;].[Sheet8$]
FROM
[Excel 8.0;HDR=YES;Database=C:\My
Folder\MySourceWorkbook.xls;].[Sheet8$]
;

A more complex example where the table does already exist in the
target workbook and you only want to append non-duplicated rows:

INSERT INTO
[Excel 8.0;HDR=YES;Database=C:\My
Folder\MyTargetWorkbook.xls;].[Sheet8$]
(MyKeyCol, MyDataCol)
SELECT
T1.MyKeyCol, T1.MyDataCol
FROM
[Excel 8.0;HDR=YES;Database=C:\My
Folder\MySourceWorkbook.xls;].[Sheet8$] T1
LEFT JOIN
[Excel 8.0;HDR=YES;Database=C:\My
Folder\MyTargetWorkbook.xls;].[Sheet8$] T2
ON T1.MyKeyCol=T2.MyKeyCol
WHERE
T2.MyKeyCol IS NULL
;


I have to admit this code is too complicated for me, but perhaps you can
find some use for it. If not: perhaps you can ask Jamie to explain!

JvLin

PS The question I posed also pertained to getting a collection of worksheet
names from unopened workbooks.

Earl,

I don't know how else to put this .......... the data in question is simply
an Excell worksheet that contains rows and columns of numbers, with an
occasional column containing equations ......... nothing special .........
just a routine spread sheet page.

I simply want to import an entire page from an unopend Excell Workbook,
(sorta like doing a copy/paste where I physically open the other file; copy
the original worksheet and paste it into a blank worksheet in the Workbook
that I am currently working in) without first having to open the other file;
or without having to write a formula, or create a link to a source file that
is going to be deleted once the data has been recorded.

I am not merging any worksheets, or combining data in any way. I am simply
making a duplicate copy of the worksheet in question and then deleting the
source file. Instead of having several Excell Workbooks that only contain a
single page of data, I end up with a single Excell Workbook that has several
pages of data.

I guess a bad example might be when you write a check. All the information
that you need to know is recorded on the check but instead of sorting through
a huge box of seperate checks,(= Multiple Excell Workbooks that only contain
one worksheet), it is much simpler to store all the data from each check in a
single register (= One Excell Workbook with multiple pages) where you have
immediate access to all the data in a single place.

As far as the project that I am currently working on, this is a one time
deal, however I do similar projects quite often.

As I say, I have been doing this in Quattro Pro for a very long time but I
am now trying to convert to Excell. Simply put, I don't like the program.
Quattro Pro is so much more straight forward and a good bit more user
friendly, but sometimes we don't always have the final say in how things are
done. I am trying to make the transition as painless as possible and, since
I have found that both programs do have some similar features (they just go
about doing it differently), I was hoping that someone might be able to tell
me what I am missing here.

Thanks again - GG






"Earl Kiosterud" wrote:

> GG,
>
> We still don't know much about the data, and how it's to be consolidated.
> There are macro possibilities, linking possibilities (you don't have to do
> it each time if you rename your workbooks to that of the links), import
> possibilities.
>
> Is this a one-time job, or does data come in regularly, which needs to be
> consolidated again and again?
>
> --
> Earl Kiosterud
> mvpearl omitthisword at verizon period net
> -------------------------------------------
>
> "GrayesGhost" <[email protected]> wrote in message
> news:[email protected]...
> > Earl,
> >
> > Maybe I don't know enough about Excell yet to word the question in a form
> > that is understandable. What I am trying to do is to copy some statistics
> > from several Workbooks that only contain a single work sheet. I want to
> > combine the worksheets from multiple Excell workbooks into seperate
> > worksheets in a single Workbook, and then get rid of all the original
> > files.
> >
> > I know I can open each workbook seperately, copy the data from that
> > workbook
> > and paste it into seperate sheets in the file that I am compiling (that is
> > what I have been doing) but I know there has to be an easier way - such as
> > simply inserting the unopened file into a blank work sheet as I was able
> > to
> > do in Quattro Pro.
> >
> > (Menu: Insert/File/<filename>) = Inserted the desired file into one or
> > more
> > worksheets depending on whether or not the inserted file was a text file
> > or
> > an existing Quattro Pro workbook.
> >
> > I am now using MS Office 2000 Pro.
> >
> > I don't want to go to the trouble of writing formulas, or linking the
> > current workbook to files that I am going to destroy once I have
> > transfered
> > the data - But I don't know of any other way to word the question - I
> > think I
> > pretty much have the answer anyway though - apparently Excell doesn't have
> > the feature that I am looking for.
> >
> > Thanks again - GG
> >
> >
> > "Earl Kiosterud" wrote:
> >
> >> You haven't told us enough. If the other workbook contains a table, use
> >> Data - Get external data. The process varies a bit with your release of
> >> Excel. You never have to open the workbook, and can refresh the import
> >> to
> >> reread the data at any time.
> >>
> >> You can also create a link without ever having opened the workbook
> >> (though
> >> it's easier if you do).
> >> =[workbookname.xls]'Sheet 1'!A1
> >>
> >> Now copy this down and across, and you have the data. Once done, you
> >> never
> >> have to reopen the other workbook. A macro could parse the data, if
> >> necessary, put it where you want it, and do any other tasks.
> >>
> >> Without knowing more, that's the best I can suggest.
> >> --
> >> Earl Kiosterud
> >> mvpearl omitthisword at verizon period net
> >> -------------------------------------------
> >>
> >> "GrayesGhost" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > If an unopened Excel Workbook only contains a single worksheet, is it
> >> > possible to import the data from that worksheet into another Workbook
> >> > that
> >> > is
> >> > open, without having to open the other file?
> >>
> >>
> >>
>
>
>

JV,

Thanks for the info, but that is a little too complicated for me. I'm doing
a very simple operation and it would take me longer to learn and implement
those formulas than it would for me to simply open the old workbook, do a
copy/paste, then close and delete the source file.

Take Care - GG

"JVLin" wrote:

> Hi,
>
> I posted a similar question a few weeks ago on the programming part of this
> forum.
>
> I got this answer from Jamie Collins:
>
> > can I copy data from one workbook to another (or for that matter
> > from within a workbook) WITHOUT OPENING EACH WORKBOOK?
>
> If your data is arranged as a database (i.e. rows of columns,
> preferable with column headers) then yes.
>
> A simple example to copy the entire contents of one table (worksheet)
> to another workbook where the table does not already exist:
>
> SELECT
> MyKeyCol, MyDataCol
> INTO
> [Excel 8.0;HDR=YES;Database=C:\My
> Folder\MyTargetWorkbook.xls;].[Sheet8$]
> FROM
> [Excel 8.0;HDR=YES;Database=C:\My
> Folder\MySourceWorkbook.xls;].[Sheet8$]
> ;
>
> A more complex example where the table does already exist in the
> target workbook and you only want to append non-duplicated rows:
>
> INSERT INTO
> [Excel 8.0;HDR=YES;Database=C:\My
> Folder\MyTargetWorkbook.xls;].[Sheet8$]
> (MyKeyCol, MyDataCol)
> SELECT
> T1.MyKeyCol, T1.MyDataCol
> FROM
> [Excel 8.0;HDR=YES;Database=C:\My
> Folder\MySourceWorkbook.xls;].[Sheet8$] T1
> LEFT JOIN
> [Excel 8.0;HDR=YES;Database=C:\My
> Folder\MyTargetWorkbook.xls;].[Sheet8$] T2
> ON T1.MyKeyCol=T2.MyKeyCol
> WHERE
> T2.MyKeyCol IS NULL
> ;
>
>
> I have to admit this code is too complicated for me, but perhaps you can
> find some use for it. If not: perhaps you can ask Jamie to explain!
>
> JvLin
>
> PS The question I posed also pertained to getting a collection of worksheet
> names from unopened workbooks.

GG,

There are at least three approaches that come to mind -- data Import, or a
mirror sheet for each workbook full of links, or a macro. The choice
depends partly on what is known about the workbooks. Are they always the
same names? If not, can they be renamed or copied to match fixed names in
any solution we come up with? Or does the solution need to accomodate any
workbook name, to be provided at run time? Are the sheet names known in
advance? Is there only a total of one sheet in each workbook? Are these
strict tables (headings in the first row, then rows of records, no totals at
the bottom), or free-form worksheets, with stuff here and there?

I'm not sure if any is worth the effort -- simply opening the workbooks, and
doing a sheet copy might still be less work. especially if you don't do this
often. Are you willing to pursue a macro solution?
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"GrayesGhost" <[email protected]> wrote in message
news:[email protected]...
> Earl,
>
> I don't know how else to put this .......... the data in question is
> simply
> an Excell worksheet that contains rows and columns of numbers, with an
> occasional column containing equations ......... nothing special .........
> just a routine spread sheet page.
>
> I simply want to import an entire page from an unopend Excell Workbook,
> (sorta like doing a copy/paste where I physically open the other file;
> copy
> the original worksheet and paste it into a blank worksheet in the Workbook
> that I am currently working in) without first having to open the other
> file;
> or without having to write a formula, or create a link to a source file
> that
> is going to be deleted once the data has been recorded.
>
> I am not merging any worksheets, or combining data in any way. I am
> simply
> making a duplicate copy of the worksheet in question and then deleting the
> source file. Instead of having several Excell Workbooks that only contain
> a
> single page of data, I end up with a single Excell Workbook that has
> several
> pages of data.
>
> I guess a bad example might be when you write a check. All the
> information
> that you need to know is recorded on the check but instead of sorting
> through
> a huge box of seperate checks,(= Multiple Excell Workbooks that only
> contain
> one worksheet), it is much simpler to store all the data from each check
> in a
> single register (= One Excell Workbook with multiple pages) where you have
> immediate access to all the data in a single place.
>
> As far as the project that I am currently working on, this is a one time
> deal, however I do similar projects quite often.
>
> As I say, I have been doing this in Quattro Pro for a very long time but I
> am now trying to convert to Excell. Simply put, I don't like the program.
> Quattro Pro is so much more straight forward and a good bit more user
> friendly, but sometimes we don't always have the final say in how things
> are
> done. I am trying to make the transition as painless as possible and,
> since
> I have found that both programs do have some similar features (they just
> go
> about doing it differently), I was hoping that someone might be able to
> tell
> me what I am missing here.
>
> Thanks again - GG
>
>
>
>
>
>
> "Earl Kiosterud" wrote:
>
>> GG,
>>
>> We still don't know much about the data, and how it's to be consolidated.
>> There are macro possibilities, linking possibilities (you don't have to
>> do
>> it each time if you rename your workbooks to that of the links), import
>> possibilities.
>>
>> Is this a one-time job, or does data come in regularly, which needs to be
>> consolidated again and again?
>>
>> --
>> Earl Kiosterud
>> mvpearl omitthisword at verizon period net
>> -------------------------------------------
>>
>> "GrayesGhost" <[email protected]> wrote in message
>> news:[email protected]...
>> > Earl,
>> >
>> > Maybe I don't know enough about Excell yet to word the question in a
>> > form
>> > that is understandable. What I am trying to do is to copy some
>> > statistics
>> > from several Workbooks that only contain a single work sheet. I want
>> > to
>> > combine the worksheets from multiple Excell workbooks into seperate
>> > worksheets in a single Workbook, and then get rid of all the original
>> > files.
>> >
>> > I know I can open each workbook seperately, copy the data from that
>> > workbook
>> > and paste it into seperate sheets in the file that I am compiling (that
>> > is
>> > what I have been doing) but I know there has to be an easier way - such
>> > as
>> > simply inserting the unopened file into a blank work sheet as I was
>> > able
>> > to
>> > do in Quattro Pro.
>> >
>> > (Menu: Insert/File/<filename>) = Inserted the desired file into one or
>> > more
>> > worksheets depending on whether or not the inserted file was a text
>> > file
>> > or
>> > an existing Quattro Pro workbook.
>> >
>> > I am now using MS Office 2000 Pro.
>> >
>> > I don't want to go to the trouble of writing formulas, or linking the
>> > current workbook to files that I am going to destroy once I have
>> > transfered
>> > the data - But I don't know of any other way to word the question - I
>> > think I
>> > pretty much have the answer anyway though - apparently Excell doesn't
>> > have
>> > the feature that I am looking for.
>> >
>> > Thanks again - GG
>> >
>> >
>> > "Earl Kiosterud" wrote:
>> >
>> >> You haven't told us enough. If the other workbook contains a table,
>> >> use
>> >> Data - Get external data. The process varies a bit with your release
>> >> of
>> >> Excel. You never have to open the workbook, and can refresh the
>> >> import
>> >> to
>> >> reread the data at any time.
>> >>
>> >> You can also create a link without ever having opened the workbook
>> >> (though
>> >> it's easier if you do).
>> >> =[workbookname.xls]'Sheet 1'!A1
>> >>
>> >> Now copy this down and across, and you have the data. Once done, you
>> >> never
>> >> have to reopen the other workbook. A macro could parse the data, if
>> >> necessary, put it where you want it, and do any other tasks.
>> >>
>> >> Without knowing more, that's the best I can suggest.
>> >> --
>> >> Earl Kiosterud
>> >> mvpearl omitthisword at verizon period net
>> >> -------------------------------------------
>> >>
>> >> "GrayesGhost" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > If an unopened Excel Workbook only contains a single worksheet, is
>> >> > it
>> >> > possible to import the data from that worksheet into another
>> >> > Workbook
>> >> > that
>> >> > is
>> >> > open, without having to open the other file?
>> >>
>> >>
>> >>
>>
>>
>>

GG,

On considering this a little more, I think the macro solution is the one
most like what you describe. The other two won't give you formulas -- just
fixed numbers or text.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Earl Kiosterud" <[email protected]> wrote in message
news:%[email protected]...
> GG,
>
> There are at least three approaches that come to mind -- data Import, or
> a mirror sheet for each workbook full of links, or a macro. The choice
> depends partly on what is known about the workbooks. Are they always the
> same names? If not, can they be renamed or copied to match fixed names in
> any solution we come up with? Or does the solution need to accomodate any
> workbook name, to be provided at run time? Are the sheet names known in
> advance? Is there only a total of one sheet in each workbook? Are these
> strict tables (headings in the first row, then rows of records, no totals
> at the bottom), or free-form worksheets, with stuff here and there?
>
> I'm not sure if any is worth the effort -- simply opening the workbooks,
> and doing a sheet copy might still be less work. especially if you don't
> do this often. Are you willing to pursue a macro solution?
> --
> Earl Kiosterud
> mvpearl omitthisword at verizon period net
> -------------------------------------------
>
> "GrayesGhost" <[email protected]> wrote in message
> news:[email protected]...
>> Earl,
>>
>> I don't know how else to put this .......... the data in question is
>> simply
>> an Excell worksheet that contains rows and columns of numbers, with an
>> occasional column containing equations ......... nothing special
>> .........
>> just a routine spread sheet page.
>>
>> I simply want to import an entire page from an unopend Excell Workbook,
>> (sorta like doing a copy/paste where I physically open the other file;
>> copy
>> the original worksheet and paste it into a blank worksheet in the
>> Workbook
>> that I am currently working in) without first having to open the other
>> file;
>> or without having to write a formula, or create a link to a source file
>> that
>> is going to be deleted once the data has been recorded.
>>
>> I am not merging any worksheets, or combining data in any way. I am
>> simply
>> making a duplicate copy of the worksheet in question and then deleting
>> the
>> source file. Instead of having several Excell Workbooks that only
>> contain a
>> single page of data, I end up with a single Excell Workbook that has
>> several
>> pages of data.
>>
>> I guess a bad example might be when you write a check. All the
>> information
>> that you need to know is recorded on the check but instead of sorting
>> through
>> a huge box of seperate checks,(= Multiple Excell Workbooks that only
>> contain
>> one worksheet), it is much simpler to store all the data from each check
>> in a
>> single register (= One Excell Workbook with multiple pages) where you
>> have
>> immediate access to all the data in a single place.
>>
>> As far as the project that I am currently working on, this is a one time
>> deal, however I do similar projects quite often.
>>
>> As I say, I have been doing this in Quattro Pro for a very long time but
>> I
>> am now trying to convert to Excell. Simply put, I don't like the
>> program.
>> Quattro Pro is so much more straight forward and a good bit more user
>> friendly, but sometimes we don't always have the final say in how things
>> are
>> done. I am trying to make the transition as painless as possible and,
>> since
>> I have found that both programs do have some similar features (they just
>> go
>> about doing it differently), I was hoping that someone might be able to
>> tell
>> me what I am missing here.
>>
>> Thanks again - GG
>>
>>
>>
>>
>>
>>
>> "Earl Kiosterud" wrote:
>>
>>> GG,
>>>
>>> We still don't know much about the data, and how it's to be
>>> consolidated.
>>> There are macro possibilities, linking possibilities (you don't have to
>>> do
>>> it each time if you rename your workbooks to that of the links), import
>>> possibilities.
>>>
>>> Is this a one-time job, or does data come in regularly, which needs to
>>> be
>>> consolidated again and again?
>>>
>>> --
>>> Earl Kiosterud
>>> mvpearl omitthisword at verizon period net
>>> -------------------------------------------
>>>
>>> "GrayesGhost" <[email protected]> wrote in message
>>> news:[email protected]...
>>> > Earl,
>>> >
>>> > Maybe I don't know enough about Excell yet to word the question in a
>>> > form
>>> > that is understandable. What I am trying to do is to copy some
>>> > statistics
>>> > from several Workbooks that only contain a single work sheet. I want
>>> > to
>>> > combine the worksheets from multiple Excell workbooks into seperate
>>> > worksheets in a single Workbook, and then get rid of all the original
>>> > files.
>>> >
>>> > I know I can open each workbook seperately, copy the data from that
>>> > workbook
>>> > and paste it into seperate sheets in the file that I am compiling
>>> > (that is
>>> > what I have been doing) but I know there has to be an easier way -
>>> > such as
>>> > simply inserting the unopened file into a blank work sheet as I was
>>> > able
>>> > to
>>> > do in Quattro Pro.
>>> >
>>> > (Menu: Insert/File/<filename>) = Inserted the desired file into one or
>>> > more
>>> > worksheets depending on whether or not the inserted file was a text
>>> > file
>>> > or
>>> > an existing Quattro Pro workbook.
>>> >
>>> > I am now using MS Office 2000 Pro.
>>> >
>>> > I don't want to go to the trouble of writing formulas, or linking the
>>> > current workbook to files that I am going to destroy once I have
>>> > transfered
>>> > the data - But I don't know of any other way to word the question - I
>>> > think I
>>> > pretty much have the answer anyway though - apparently Excell doesn't
>>> > have
>>> > the feature that I am looking for.
>>> >
>>> > Thanks again - GG
>>> >
>>> >
>>> > "Earl Kiosterud" wrote:
>>> >
>>> >> You haven't told us enough. If the other workbook contains a table,
>>> >> use
>>> >> Data - Get external data. The process varies a bit with your release
>>> >> of
>>> >> Excel. You never have to open the workbook, and can refresh the
>>> >> import
>>> >> to
>>> >> reread the data at any time.
>>> >>
>>> >> You can also create a link without ever having opened the workbook
>>> >> (though
>>> >> it's easier if you do).
>>> >> =[workbookname.xls]'Sheet 1'!A1
>>> >>
>>> >> Now copy this down and across, and you have the data. Once done,
>>> >> you
>>> >> never
>>> >> have to reopen the other workbook. A macro could parse the data, if
>>> >> necessary, put it where you want it, and do any other tasks.
>>> >>
>>> >> Without knowing more, that's the best I can suggest.
>>> >> --
>>> >> Earl Kiosterud
>>> >> mvpearl omitthisword at verizon period net
>>> >> -------------------------------------------
>>> >>
>>> >> "GrayesGhost" <[email protected]> wrote in
>>> >> message
>>> >> news:[email protected]...
>>> >> > If an unopened Excel Workbook only contains a single worksheet, is
>>> >> > it
>>> >> > possible to import the data from that worksheet into another
>>> >> > Workbook
>>> >> > that
>>> >> > is
>>> >> > open, without having to open the other file?
>>> >>
>>> >>
>>> >>
>>>
>>>
>>>
>
>

Hi GrayesGhost

I hope this will help you.

Sub Retrieve_Info
P = "C:\MyDocumnets"
f = "Test.xls"
s = "Sheet1"

Application.ScreenUpdating = False
For r = 1 To 100
For c = 1 To 4
a = Cells(r, c).Address
Cells(r, c) = GetValue(P, f, s, a)
Next c
Next r
Application.ScreenUpdating = True
End Sub


Private Function GetValue(path, file, sheet, ref)
' Retrieves a value from a closed workbook
Dim arg As String

' Make sure the file exists
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
Exit Function
End If

' Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)

' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function

Thanks
Bob

"GrayesGhost" wrote:

> If an unopened Excel Workbook only contains a single worksheet, is it
> possible to import the data from that worksheet into another Workbook that is
> open, without having to open the other file?

Hi GrayesGhost

I hope this will help you.

Sub Retrieve_Info
P = "C:\MyDocumnets"
f = "Test.xls"
s = "Sheet1"

Application.ScreenUpdating = False
For r = 1 To 100
For c = 1 To 4
a = Cells(r, c).Address
Cells(r, c) = GetValue(P, f, s, a)
Next c
Next r
Application.ScreenUpdating = True
End Sub


Private Function GetValue(path, file, sheet, ref)
' Retrieves a value from a closed workbook
Dim arg As String

' Make sure the file exists
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
Exit Function
End If

' Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)

' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function

Thanks
Bob

"GrayesGhost" wrote:

> If an unopened Excel Workbook only contains a single worksheet, is it
> possible to import the data from that worksheet into another Workbook that is
> open, without having to open the other file?

Bob,

I was able to use your code. I've been finding an answer to the exact problem as GrayesGhost.

I would jus like to ask how to tell what sheet should the copied data should be pasted. currently, it will paste it in the first sheet. what if I want to paste it in another sheet?

me again. :D

I found this site to address our problem:
Pull in data from a closed Workbook without having to open it

I have modified the script found there to suit my need and i've come up with this:

Sub CopyAndPaste(WholePath, sSheetname)

Dim xlobj As Object
Dim wsobj As Object
Dim rngobj As Object
Set xlobj = GetObject(WholePath)
Set wsobj = xlobj.Worksheets(sSheetname)
Set rngobj = wsobj.UsedRange
sUsedRange = rngobj.Address

With Sheet6.Range(sUsedRange)
'If the cell in Sheet1 of the closed workbook is not _
empty the pull in it's content, else put in an Error.
.FormulaR1C1 = "=IF('D:\UP\v1\[Data-2005.xls]Internal'!RC="""", NA()," & _
"'D:\UP\v1\[Data-2005.xls]Internal'!RC)"
'Delete all Error cells
On Error Resume Next
.SpecialCells(xlCellTypeFormulas, xlErrors).Clear
On Error GoTo 0
'Change all formulas to Values only
.Value = .Value
End With
End Sub

my concern is that I would like to reuse this function and would like to parameterize the targetsheet where the data will be dumped. (Sheet6 in this case)

I'll also parameterize the source file and make it flexible so that users can choose the file to be used.

For now, I will be content if somebody would tell me how to make 'Sheet6' a variable. I tried sSheetname.Range(sUsedRange) but it throws and object needed error.

Help anyone.

Help anyone.

I still can't resolve my Sheet6.Range problem above.

Try this:
In a blank workbook or in an active workbook
Data
Import External Data
Import Data
Path/Filename
Select Table
OK
Select Existing Workbook
OK
Repeat as Needed

Hope this is what you are looking for.

Open each individual workbook. Designate the workbook that you want all of
the worksheets combined in. In the other opened workbooks, right click on
the sheet tab at the bottom. Click Move or Copy, then select the master
workbook under 'To Book' you want to move to.

"GrayesGhost" wrote:

> Earl,
>
> Maybe I don't know enough about Excell yet to word the question in a form
> that is understandable. What I am trying to do is to copy some statistics
> from several Workbooks that only contain a single work sheet. I want to
> combine the worksheets from multiple Excell workbooks into seperate
> worksheets in a single Workbook, and then get rid of all the original files.
>
> I know I can open each workbook seperately, copy the data from that workbook
> and paste it into seperate sheets in the file that I am compiling (that is
> what I have been doing) but I know there has to be an easier way - such as
> simply inserting the unopened file into a blank work sheet as I was able to
> do in Quattro Pro.
>
> (Menu: Insert/File/<filename>) = Inserted the desired file into one or more
> worksheets depending on whether or not the inserted file was a text file or
> an existing Quattro Pro workbook.
>
> I am now using MS Office 2000 Pro.
>
> I don't want to go to the trouble of writing formulas, or linking the
> current workbook to files that I am going to destroy once I have transfered
> the data - But I don't know of any other way to word the question - I think I
> pretty much have the answer anyway though - apparently Excell doesn't have
> the feature that I am looking for.
>
> Thanks again - GG
>
>
> "Earl Kiosterud" wrote:
>
> > You haven't told us enough. If the other workbook contains a table, use
> > Data - Get external data. The process varies a bit with your release of
> > Excel. You never have to open the workbook, and can refresh the import to
> > reread the data at any time.
> >
> > You can also create a link without ever having opened the workbook (though
> > it's easier if you do).
> > =[workbookname.xls]'Sheet 1'!A1
> >
> > Now copy this down and across, and you have the data. Once done, you never
> > have to reopen the other workbook. A macro could parse the data, if
> > necessary, put it where you want it, and do any other tasks.
> >
> > Without knowing more, that's the best I can suggest.
> > --
> > Earl Kiosterud
> > mvpearl omitthisword at verizon period net
> > -------------------------------------------
> >
> > "GrayesGhost" <[email protected]> wrote in message
> > news:[email protected]...
> > > If an unopened Excel Workbook only contains a single worksheet, is it
> > > possible to import the data from that worksheet into another Workbook that
> > > is
> > > open, without having to open the other file?
> >
> >
> >

Try this.
This will copy a named sheet from a closed WB and paste into your active WB.



Sub test()
Dim Wb1 As Workbook
Dim Wb2 As Workbook
Application.ScreenUpdating = False
Set Wb1 = ActiveWorkbook
Set Wb2 = Workbooks.Open("C:\filename.xls")
Wb2.Sheets("sheet1").Copy _
after:=Wb1.Sheets(Wb1.Sheets.Count)
Wb2.Close False
Application.ScreenUpdating = True
End Sub


Syed

you could just open your new workbook and minmise it

then open each of the other workbooks inturn ,

select the tab with the data in and right click

select move or copy

then move the page into the new book




it is easier that it sounds and keeps the original formating ext ,,, and is
less hassel that copying and pasting accross both books



"GrayesGhost" wrote:

> If an unopened Excel Workbook only contains a single worksheet, is it
> possible to import the data from that worksheet into another Workbook that is
> open, without having to open the other file?