Copying ranges of cells from closed workbooks?

I've browsed/searched here and have found similar topics, but I'm not following the implementation of them so well...

I have 5 or 6 workbooks that I'd like to be combining the contents of into one master workbook. They're all exactly the same, and each workbook is made up of 9 worksheets. 5 of the sheets in each book are time sheets where employees hours are listed, and 1 of the sheets is a page where employees data is entered. The info entered into that page propogates the other 5 hour pages in columns on the left side so that on the time sheets all that can be entered is hour totals for the week.

To get it all into a master workbook, I just need to copy the same data ranges from each workbook per worksheet. I'm certain it's possible, and I can customize it if I have a decent template; I'm just having trouble getting started.

More info: The employee data range is up to almost 200 rows, but data within those may only be, say, the first five rows. Is there a way to return/copy/paste only the rows that have data in them, allowing maybe two spaces (empty rows) between each workbook copied into the master workbook?

It seems like a tall order, but I'm sure it can be done and I'm hoping to do it!

Thanks

You will have to open the Excel workbooks... but can iopen them in hidden
mode so they don't actually appear on the screen.

Do a search for programatically accessing Excel. It should give you some
links to some code snippets that show you how to open another workbook
without displaying it.

This site might also help. http://www.vba-programmer.com/

Here is a block of code I have used to open a "database" excel file and read
data from it...

<><>
WB_OpenFile = Application.GetOpenFilename()

If WB_OpenFile = False Then Exit Sub ' Exit process if Cancel is
selected
On Error GoTo bad_file_open
Workbooks.Open Filename:=WB_OpenFile
bad_file_name:
<><>


The other option would be to write a Visual Basic program that opens all of
the Excel workbooks and reads and writes the data without ever viewing the
workbooks. This might be a more flexible implementation.

Brian




"JHongTurney" <[email protected]>
wrote in message
news:[email protected]...
>
> I've browsed/searched here and have found similar topics, but I'm not
> following the implementation of them so well...
>
> I have 5 or 6 workbooks that I'd like to be combining the contents of
> into one master workbook. They're all exactly the same, and each
> workbook is made up of 9 worksheets. 5 of the sheets in each book are
> time sheets where employees hours are listed, and 1 of the sheets is a
> page where employees data is entered. The info entered into that page
> propogates the other 5 hour pages in columns on the left side so that
> on the time sheets all that can be entered is hour totals for the
> week.
>
> To get it all into a master workbook, I just need to copy the same data
> ranges from each workbook per worksheet. I'm certain it's possible, and
> I can customize it if I have a decent template; I'm just having trouble
> getting started.
>
> More info: The employee data range is up to almost 200 rows, but data
> within those may only be, say, the first five rows. Is there a way to
> return/copy/paste only the rows that have data in them, allowing maybe
> two spaces (empty rows) between each workbook copied into the master
> workbook?
>
> It seems like a tall order, but I'm sure it can be done and I'm hoping
> to do it!
>
> Thanks
>
>
> --
> JHongTurney
> ------------------------------------------------------------------------
> JHongTurney's Profile:
http://www.excelforum.com/member.php...o&userid=29369
> View this thread: http://www.excelforum.com/showthread...hreadid=490814
>