I am looking for some simple Code that will open up all files in a certain
Directory, then copy a range within a certain sheet to one Workbook, then
close all Workbooks except for the Consolidated one, thus consolidating all
information

Basic information is as follows

All Files that I wish to open will have the same layout and woksheet names,
although each of these files will have a different workbook name
The range area I am looking to copy is A13:I13
This range will reside in a worksheet called "E-Import"
The Directory path where all these files will reside is C:\MIS\Labour
Module\Labour Import
My Consoldated File will be "Daily Labour Report w/e 18-12-05" - however
this name changes each week, as I create a new file for each week
The worksheet within my Consolidated file where all the file info wil be
copied to will be called "Consol Info"


Thanks

Ron DeBruin has some code examples that can do exactly this. I would
recommend the ADO method that does not need to open the workbooks.
http://www.rondebruin.nl/ado.htm#files

See example #3 for multiple workbooks.
Mike F
"John" wrote in message
...
I am looking for some simple Code that will open up all files in a certain
Directory, then copy a range within a certain sheet to one Workbook, then
close all Workbooks except for the Consolidated one, thus consolidating all
information

Basic information is as follows

All Files that I wish to open will have the same layout and woksheet
names, although each of these files will have a different workbook name
The range area I am looking to copy is A13:I13
This range will reside in a worksheet called "E-Import"
The Directory path where all these files will reside is C:\MIS\Labour
Module\Labour Import
My Consoldated File will be "Daily Labour Report w/e 18-12-05" - however
this name changes each week, as I create a new file for each week
The worksheet within my Consolidated file where all the file info wil be
copied to will be called "Consol Info"


Thanks

Thanks Mike

I used Ron's Example 3, I copied exactly as is except changing the source
directory to "C:\MIS\Labour Module\Labour Import" but it hits debug at below
saying "User-Defined type not defined". Not sure what this means

Public Sub GetData(SourceFile As Variant, SourceSheet As String, _
sourceRange As String, TargetRange As Range, HeaderRow As
Boolean)

Dim rsData As ADODB.Recordset



"Mike Fogleman" wrote in message
...
Ron DeBruin has some code examples that can do exactly this. I would
recommend the ADO method that does not need to open the workbooks.
http://www.rondebruin.nl/ado.htm#files

See example #3 for multiple workbooks.
Mike F
"John" wrote in message
...
I am looking for some simple Code that will open up all files in a certain
Directory, then copy a range within a certain sheet to one Workbook, then
close all Workbooks except for the Consolidated one, thus consolidating
all information

Basic information is as follows

All Files that I wish to open will have the same layout and woksheet
names, although each of these files will have a different workbook name
The range area I am looking to copy is A13:I13
This range will reside in a worksheet called "E-Import"
The Directory path where all these files will reside is C:\MIS\Labour
Module\Labour Import
My Consoldated File will be "Daily Labour Report w/e 18-12-05" - however
this name changes each week, as I create a new file for each week
The worksheet within my Consolidated file where all the file info wil be
copied to will be called "Consol Info"


Thanks

Hi John

You forgot to set the reference
You must set a reference to the Microsoft ActiveX Data Objects 2.5 library

Download first the example zip to test



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


"John" wrote in message ...
Thanks Mike

I used Ron's Example 3, I copied exactly as is except changing the source directory to "C:\MIS\Labour Module\Labour Import" but it
hits debug at below saying "User-Defined type not defined". Not sure what this means

Public Sub GetData(SourceFile As Variant, SourceSheet As String, _
sourceRange As String, TargetRange As Range, HeaderRow As Boolean)

Dim rsData As ADODB.Recordset



"Mike Fogleman" wrote in message ...
Ron DeBruin has some code examples that can do exactly this. I would recommend the ADO method that does not need to open the
workbooks.
http://www.rondebruin.nl/ado.htm#files

See example #3 for multiple workbooks.
Mike F
"John" wrote in message ...
I am looking for some simple Code that will open up all files in a certain Directory, then copy a range within a certain sheet to
one Workbook, then close all Workbooks except for the Consolidated one, thus consolidating all information

Basic information is as follows

All Files that I wish to open will have the same layout and woksheet names, although each of these files will have a different
workbook name
The range area I am looking to copy is A13:I13
This range will reside in a worksheet called "E-Import"
The Directory path where all these files will reside is C:\MIS\Labour Module\Labour Import
My Consoldated File will be "Daily Labour Report w/e 18-12-05" - however this name changes each week, as I create a new file for
each week
The worksheet within my Consolidated file where all the file info wil be copied to will be called "Consol Info"


Thanks

Thanks Mike / Ron

Superb, don't know much about References but it works

One small change which you might assist with, instead of adding a new
worksheet within the "consol" file how would I copy the source files into
the worksheet "Consol"?



"Ron de Bruin" wrote in message
...
Hi John

You forgot to set the reference
You must set a reference to the Microsoft ActiveX Data Objects 2.5 library

Download first the example zip to test



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


"John" wrote in message
...
Thanks Mike

I used Ron's Example 3, I copied exactly as is except changing the source
directory to "C:\MIS\Labour Module\Labour Import" but it hits debug at
below saying "User-Defined type not defined". Not sure what this means

Public Sub GetData(SourceFile As Variant, SourceSheet As String, _
sourceRange As String, TargetRange As Range, HeaderRow
As Boolean)

Dim rsData As ADODB.Recordset



"Mike Fogleman" wrote in message
...
Ron DeBruin has some code examples that can do exactly this. I would
recommend the ADO method that does not need to open the workbooks.
http://www.rondebruin.nl/ado.htm#files

See example #3 for multiple workbooks.
Mike F
"John" wrote in message
...
I am looking for some simple Code that will open up all files in a
certain Directory, then copy a range within a certain sheet to one
Workbook, then close all Workbooks except for the Consolidated one, thus
consolidating all information

Basic information is as follows

All Files that I wish to open will have the same layout and woksheet
names, although each of these files will have a different workbook name
The range area I am looking to copy is A13:I13
This range will reside in a worksheet called "E-Import"
The Directory path where all these files will reside is C:\MIS\Labour
Module\Labour Import
My Consoldated File will be "Daily Labour Report w/e 18-12-05" -
however this name changes each week, as I create a new file for each
week
The worksheet within my Consolidated file where all the file info wil
be copied to will be called "Consol Info"


Thanks

You can use this
Set sh = Worksheets("consol")


instead off

Set sh = ActiveWorkbook.Worksheets.Add
sh.Name = Format(Now, "dd-mm-yy h-mm-ss")



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


"John" wrote in message ...
Thanks Mike / Ron

Superb, don't know much about References but it works

One small change which you might assist with, instead of adding a new worksheet within the "consol" file how would I copy the
source files into the worksheet "Consol"?



"Ron de Bruin" wrote in message ...
Hi John

You forgot to set the reference
You must set a reference to the Microsoft ActiveX Data Objects 2.5 library

Download first the example zip to test



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


"John" wrote in message ...
Thanks Mike

I used Ron's Example 3, I copied exactly as is except changing the source directory to "C:\MIS\Labour Module\Labour Import" but
it hits debug at below saying "User-Defined type not defined". Not sure what this means

Public Sub GetData(SourceFile As Variant, SourceSheet As String, _
sourceRange As String, TargetRange As Range, HeaderRow As Boolean)

Dim rsData As ADODB.Recordset



"Mike Fogleman" wrote in message ...
Ron DeBruin has some code examples that can do exactly this. I would recommend the ADO method that does not need to open the
workbooks.
http://www.rondebruin.nl/ado.htm#files

See example #3 for multiple workbooks.
Mike F
"John" wrote in message ...
I am looking for some simple Code that will open up all files in a certain Directory, then copy a range within a certain sheet
to one Workbook, then close all Workbooks except for the Consolidated one, thus consolidating all information

Basic information is as follows

All Files that I wish to open will have the same layout and woksheet names, although each of these files will have a different
workbook name
The range area I am looking to copy is A13:I13
This range will reside in a worksheet called "E-Import"
The Directory path where all these files will reside is C:\MIS\Labour Module\Labour Import
My Consoldated File will be "Daily Labour Report w/e 18-12-05" - however this name changes each week, as I create a new file
for each week
The worksheet within my Consolidated file where all the file info wil be copied to will be called "Consol Info"


Thanks

Did you set a reference as was explained at the top of Ron's page?

You must set a reference to the Microsoft ActiveX Data Objects 2.5 library
inthe VBA editor to use the examples below.
And also copy the functions/subs at the bottom?

Mike F

"John" wrote in message
...
Thanks Mike

I used Ron's Example 3, I copied exactly as is except changing the source
directory to "C:\MIS\Labour Module\Labour Import" but it hits debug at
below saying "User-Defined type not defined". Not sure what this means

Public Sub GetData(SourceFile As Variant, SourceSheet As String, _
sourceRange As String, TargetRange As Range, HeaderRow
As Boolean)

Dim rsData As ADODB.Recordset



"Mike Fogleman" wrote in message
...
Ron DeBruin has some code examples that can do exactly this. I would
recommend the ADO method that does not need to open the workbooks.
http://www.rondebruin.nl/ado.htm#files

See example #3 for multiple workbooks.
Mike F
"John" wrote in message
...
I am looking for some simple Code that will open up all files in a
certain Directory, then copy a range within a certain sheet to one
Workbook, then close all Workbooks except for the Consolidated one, thus
consolidating all information

Basic information is as follows

All Files that I wish to open will have the same layout and woksheet
names, although each of these files will have a different workbook name
The range area I am looking to copy is A13:I13
This range will reside in a worksheet called "E-Import"
The Directory path where all these files will reside is C:\MIS\Labour
Module\Labour Import
My Consoldated File will be "Daily Labour Report w/e 18-12-05" - however
this name changes each week, as I create a new file for each week
The worksheet within my Consolidated file where all the file info wil be
copied to will be called "Consol Info"


Thanks