Importing unopened worksheet into an open Workbook

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


--
saziz
------------------------------------------------------------------------
saziz's Profile: http://www.excelforum.com/member.php...fo&userid=6350
View this thread: http://www.excelforum.com/showthread...hreadid=351528

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?

Hey Bob-

The code below works great but since I don't know how much data i have
because people update the worksheet daily, can you modify the code to where
it pulls data from the last used row instead of only 100 rows?

"Bob" wrote:

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 Dave or Anyone out the I was wondering if you could help me.
I have 3 sets of workbooks in 47 different languages.
Workbook A has the following:
A header row that displays the following
ColA = Country
ColB = Language
ColC = Section
ColD = Phrase
ColE = Num
ColF = New
ColG = Country Specific
Then several rows following the heading row

Workbook B has the following:
2 header rows that will not be coming over
ColA = TextCode
ColB = Original Text
ColC = Translation
and rows of data following

Workbook C has the following:
one header row that will not be coming over
ColA = Text Code
ColB = Original Text
ColC = Translation

What I need:
1 Master workbook for each language (meaning, I need a master workbook/sheet
for a language to start with appending workbook a and its headings and append
workbook b and then append workbood c.

with workbook b and c, Col A would append to ColC of Master and Col c would
append to ColD of Master.

I have 47 languages with three spreadsheets each to do from now until Monday.
Is this doable. Of course I will be working on it all weekend.

Can you help me?


"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?

Hi Dave or Anyone out the I was wondering if you could help me.
I have 3 sets of workbooks in 47 different languages.
Workbook A has the following:
A header row that displays the following
ColA = Country
ColB = Language
ColC = Section
ColD = Phrase
ColE = Num
ColF = New
ColG = Country Specific
Then several rows following the heading row

Workbook B has the following:
2 header rows that will not be coming over
ColA = TextCode
ColB = Original Text
ColC = Translation
and rows of data following

Workbook C has the following:
one header row that will not be coming over
ColA = Text Code
ColB = Original Text
ColC = Translation

What I need:
1 Master workbook for each language (meaning, I need a master workbook/sheet
for a language to start with appending workbook a and its headings and append
workbook b and then append workbood c.

with workbook b and c, Col A would append to ColC of Master and Col c would
append to ColD of Master.

I have 47 languages with three spreadsheets each to do from now until Monday.
Is this doable. Of course I will be working on it all weekend.

Can you help me?


"Earl Kiosterud" wrote:

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in
message
...
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?