Importing? Question....

  • Thread starter Thread starter ShYbOiNtX
  • Start date Start date
S

ShYbOiNtX

I have a question that I hope someone can help me with. I think it is a fairly
easy task, but for the life of me, I cannot seem to get it.

Here is what I want to do...

I work for a hotel, and my night auditor saves about 10 reports to disk, in
excel format. Same reports, every night. Then, the next day, I am taking
those 10 saved files, opening them, copying the contents from each, and pasting
them in a different saved file....with a tab for each file. I hope that makes
sense. Basically, taking the 10 files, and combining them into one file to be
emailed.

Now, is there a way to just open each tab at the bottom of my "emailed" file,
and import the contents from each saved file, rather than "open, copy, paste,
close...open, copy, paste, close...".??

Forgive me if I rambled on or didn't make sense, it is late and I really should
be in bed rather than thinking about work! lol thanks in advance!

James
 
ShYbOiNtX wrote...
...
. . . my night auditor saves about 10 reports to disk, in excel
format. Same reports, every night. Then, the next day, I am
taking those 10 saved files, opening them, copying the contents
from each, and pasting them in a different saved file....with a
tab for each file. I hope that makes sense. Basically, taking the
10 files, and combining them into one file to be emailed.

Now, is there a way to just open each tab at the bottom of my
"emailed" file, and import the contents from each saved file,
rather than "open, copy, paste, close...open, copy, paste,
close..."?
Click to expand...
...

Yes. If each of the 10 nightly report files consists of a single
worksheet, and the formats never vary but the filenames do, then you
could write your summary workbook using dummy or template nightly
report filenames in your formulas, then use Edit > Replace (or Edit >
Links > Change Source) in each worksheet in your summary workbook to
change the file references to those of the previous night's report
workbooks.

Do you want to use VBA to automate this so that all you need to do is
select one nightly report file per summary workbook worksheet?
 
Subject: Re: Importing? Question....
From: hgrove <[email protected] >
Date: 8/19/2004 3:41 PM Central Daylight Time
Message-id: <[email protected]>

ShYbOiNtX wrote...
..
..

Yes. If each of the 10 nightly report files consists of a single
worksheet, and the formats never vary but the filenames do, then you
could write your summary workbook using dummy or template nightly
report filenames in your formulas, then use Edit > Replace (or Edit >
Links > Change Source) in each worksheet in your summary workbook to
change the file references to those of the previous night's report
workbooks.

Do you want to use VBA to automate this so that all you need to do is
select one nightly report file per summary workbook worksheet?
Click to expand...

I dont think I am following you...or maybe you misunderstood me...

Let me try again:
Employee saves 10 reports every night. One report for credit cards, one report
for cash, one report for taxes, etc, etc...
So I have 10 files saved, without any formulas, with names like "credit.xls",
etc.

I open the file, copy the contents, and then paste into another workbook with
10 pages, each page tab labeled for credit cards, cash, etc.

Is there a way instead of opening each of the 10 files to just go to my
"summary" worksheet and then to each page and use a funtion like "insert file"
and it paste the contents of that file into the worksheet?

I don't think I am making sense here...but hopefully you get the idea. If not,
no big deal...just trying to make my job easier! lol thanks!!
 
ShYbOiNtX said:
Is there a way instead of opening each of the 10 files to just go to my
"summary" worksheet and then to each page and use a funtion like "insert
file" and it paste the contents of that file into the worksheet?
Click to expand...

No.

The only ways to bring data from one Excel file into another are to open
both and copy and paste between them or to use formulas in one to access
values in the other. That's all. If you don't like opening all files and
copying and pasting, the only alternative is using formulas.
 
Harlan Grove said:
The only ways to bring data from one Excel file into another are to open
both and copy and paste between them or to use formulas in one to access
values in the other. That's all.
Click to expand...

Sounds unlikely. I know of at least one other way: use e.g. ADO and a
Jet driver to execute the following sql:

SELECT *
INTO [Excel 8.0;Database=C:\Book2.xls;].Sheet1
FROM [Excel 8.0;Database=C:\Book1.xls;].[Sheet1$]
;

Jamie.

--
 
Back
Top