VBA data from Closed workbook Question
 

Hi all, i have approx 35 xls files with only one worksheet in per
workbook. What i need to do is to create a summary workbook containing
the data from all the worksheets. I would normally have done this by
opening the file and selecting all from A2 down and then copied it onto
a new sheet and then just added the data from each sheet underneath the
data already there. Can one do this with the workbooks closed ? If so
how would one do that ? any help with code would be appreciated.



Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

VBA data from Closed workbook Question
 

Hi all,

Hi there Les,

i have approx 35 xls files with only one worksheet in per
workbook. What i need to do is to create a summary workbook containing
the data from all the worksheets.

OK

I would normally have done this by
opening the file and selecting all from A2 down and then copied it onto
a new sheet and then just added the data from each sheet underneath the
data already there.

Well, you've got several options really:
- Copy the raw data, i.e., the actual digits
- Copy the data, but by adding links from the sub workbooks to the master
(c.f., Edit Paste Special... menu item, and click the Paste Link button)

A link can look like this, for instance (this text is placed in your master
workbook, in one of the cells, and it retreives a value from cell A1 in
Sheet1 of the Excel spreadsheet Sub1.xls, found in the directory C:\Stuff\ ):

='C:\Stuff\[Sub1.xls]Sheet1'!A1

However, links can be broken -- they point to files using absolute
filepaths, and if the linked files are ever moved or renamed you're out of
luck. You can check your links via the Edit Links... dialogue.

There's no reason why you wouldn't be able to paste such links
programmatically into cells, provided, of course, that you know the names of
all the sub workbooks, and the extent of the data that are within those
books. If the number of observations contained within each sub workbook
change then it may become a little bit tricky.

Can one do this with the workbooks closed ? If so
how would one do that ? any help with code would be appreciated.

Well, from your (ie, the user's) perspective the sub workbooks are not open.
However, they are of course still accessed by Excel. If a workbook contains
external links, that is, links to other workbooks or to add-ins, you will be
asked whether you want to update the data in your master book whenever you
open it; if you decide not to update, you will only have cached data
available.

Hope this helps...
/MP

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

VBA data from Closed workbook Question
 

Hi Les

Maybe you can use ADO
http://www.rondebruin.nl/ado.htm

But I like to open them and do what I want
http://www.rondebruin.nl/copy3.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Les Stout" wrote in message ...
Hi all, i have approx 35 xls files with only one worksheet in per
workbook. What i need to do is to create a summary workbook containing
the data from all the worksheets. I would normally have done this by
opening the file and selecting all from A2 down and then copied it onto
a new sheet and then just added the data from each sheet underneath the
data already there. Can one do this with the workbooks closed ? If so
how would one do that ? any help with code would be appreciated.



Les Stout

*** Sent via Developersdex http://www.developersdex.com ***