Excel VBA Merge Workbook tables Central table
 

I have a file directory called "DirectoryOne" which contains many MS
Excel
workbooks. In each of these MS Excel workbooks, there is a table which
may
contains zero to many records.

Issues:
The quantity MS workbook and MS workbook names may vary.
In each of the workbooks, the table column and rows may vary.
Some times you may get a missing name.


For MS Excel workbook example:

Name Age Sex Location Color Etc....
joe 12 M NY Red
robert 23 F LDN blue
susan 15 CAN pink
mike 78 N GER orange
joe 2 M UK green
34 F UK black
Jane 15 CAN pink

etc...

Is this possible in MS Excel using VBA?

From a Master spreadsheet, is it possible to create a VBA macro to open
all
MS Excel workbooks in the "DirectoryOne" directory and then copy these
row records details into a the Master spreadsheet workbook worksheet
called
"Raw data".

Issues: Sometimes a user may have workbook open in the "DirectoryOne"
directory, whilst the macro is running and don't want macro to
crash.....


Raw datasheet example in the Master spreadsheet, which contains
additional
records from the other workbooks in the "DirectoryOne" directory.

Name Age Sex Location Color zipcode Etc....
joe 12 M NY Red
robert 23 F LDN blue
susan 15 CAN pink
mike 78 N GER orange
joe 2 M UK green
34 F UK black
Jane 15 CAN pink
lloyd 23 F LDN blue 12
vickie 15 CAN pink 11
john 78 N RUS Green 3
adam 21 F UK green 09
rob 36 F LDN 11
sam 25 D FIN pink 1
mandy 8 N GER orange 2
asstair 12 M UK green 1
etc...

Appreciate any help given of this.

Brenda

Excel VBA Merge Workbook tables Central table
 

Ron de Bruin has lots of sample code at his site:

This looks like it could get you close:
http://www.rondebruin.nl/copy4.htm

wrote:

I have a file directory called "DirectoryOne" which contains many MS
Excel
workbooks. In each of these MS Excel workbooks, there is a table which
may
contains zero to many records.

Issues:
The quantity MS workbook and MS workbook names may vary.
In each of the workbooks, the table column and rows may vary.
Some times you may get a missing name.

For MS Excel workbook example:

Name Age Sex Location Color Etc....
joe 12 M NY Red
robert 23 F LDN blue
susan 15 CAN pink
mike 78 N GER orange
joe 2 M UK green
34 F UK black
Jane 15 CAN pink

etc...

Is this possible in MS Excel using VBA?

From a Master spreadsheet, is it possible to create a VBA macro to open
all
MS Excel workbooks in the "DirectoryOne" directory and then copy these
row records details into a the Master spreadsheet workbook worksheet
called
"Raw data".

Issues: Sometimes a user may have workbook open in the "DirectoryOne"
directory, whilst the macro is running and don't want macro to
crash.....

Raw datasheet example in the Master spreadsheet, which contains
additional
records from the other workbooks in the "DirectoryOne" directory.

Name Age Sex Location Color zipcode Etc....
joe 12 M NY Red
robert 23 F LDN blue
susan 15 CAN pink
mike 78 N GER orange
joe 2 M UK green
34 F UK black
Jane 15 CAN pink
lloyd 23 F LDN blue 12
vickie 15 CAN pink 11
john 78 N RUS Green 3
adam 21 F UK green 09
rob 36 F LDN 11
sam 25 D FIN pink 1
mandy 8 N GER orange 2
asstair 12 M UK green 1
etc...

Appreciate any help given of this.

Brenda

--

Dave Peterson