Data Compilation Macro
 

Hi All,

thank you very much for your previous helps.

I have one problem here. I have a folder where i get data in .xls format. I
get nearly hundred files a day. I need to manually sit and cut copy paste all
the data so that i could have all the data on a single excel sheet.

Request you to design a macro for the same.

Many thanks
--
Varun Nair

Data Compilation Macro
 

You might need to change the code to get the last cell depending upon your
data structure

Option Explicit


Dim oFSO

Sub LoopFolders()

Set oFSO = CreateObject("Scripting.FileSystemObject")

selectFiles "c:\MyTest"

Set oFSO = Nothing

End Sub


'---------------------------------------------------------------------------
Sub selectFiles(sPath)
'---------------------------------------------------------------------------
Dim Folder As Object
Dim Files As Object
Dim file As Object
Dim fldr

Set Folder = oFSO.GetFolder(sPath)

For Each fldr In Folder.Subfolders
selectFiles fldr.Path
Next fldr

For Each file In Folder.Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
ActiveWorkbook.Worksheets(1).UsedRange.Copy _

ThisWorkbook.Worksheets(1).Range("A1").End(xlDown) .Offset(1,0)
End If
Next file

End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Varun Nair" wrote in message
...
Hi All,

thank you very much for your previous helps.

I have one problem here. I have a folder where i get data in .xls format.
I
get nearly hundred files a day. I need to manually sit and cut copy paste
all
the data so that i could have all the data on a single excel sheet.

Request you to design a macro for the same.

Many thanks
--
Varun Nair

Data Compilation Macro
 

Hi Bob,

All the files to be compiled are in a single folder. would this macro work
for even such files
--
Varun Nair



"Bob Phillips" wrote:

You might need to change the code to get the last cell depending upon your
data structure

Option Explicit


Dim oFSO

Sub LoopFolders()

Set oFSO = CreateObject("Scripting.FileSystemObject")

selectFiles "c:\MyTest"

Set oFSO = Nothing

End Sub


'---------------------------------------------------------------------------
Sub selectFiles(sPath)
'---------------------------------------------------------------------------
Dim Folder As Object
Dim Files As Object
Dim file As Object
Dim fldr

Set Folder = oFSO.GetFolder(sPath)

For Each fldr In Folder.Subfolders
selectFiles fldr.Path
Next fldr

For Each file In Folder.Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
ActiveWorkbook.Worksheets(1).UsedRange.Copy _

ThisWorkbook.Worksheets(1).Range("A1").End(xlDown) .Offset(1,0)
End If
Next file

End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Varun Nair" wrote in message
...
Hi All,

thank you very much for your previous helps.

I have one problem here. I have a folder where i get data in .xls format.
I
get nearly hundred files a day. I need to manually sit and cut copy paste
all
the data so that i could have all the data on a single excel sheet.

Request you to design a macro for the same.

Many thanks
--
Varun Nair

Data Compilation Macro
 

This code ONLY works on a single folder.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Varun Nair" wrote in message
...
Hi Bob,

All the files to be compiled are in a single folder. would this macro work
for even such files
--
Varun Nair



"Bob Phillips" wrote:

You might need to change the code to get the last cell depending upon
your
data structure

Option Explicit


Dim oFSO

Sub LoopFolders()

Set oFSO = CreateObject("Scripting.FileSystemObject")

selectFiles "c:\MyTest"

Set oFSO = Nothing

End Sub



'---------------------------------------------------------------------------
Sub selectFiles(sPath)

'---------------------------------------------------------------------------
Dim Folder As Object
Dim Files As Object
Dim file As Object
Dim fldr

Set Folder = oFSO.GetFolder(sPath)

For Each fldr In Folder.Subfolders
selectFiles fldr.Path
Next fldr

For Each file In Folder.Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
ActiveWorkbook.Worksheets(1).UsedRange.Copy _

ThisWorkbook.Worksheets(1).Range("A1").End(xlDown) .Offset(1,0)
End If
Next file

End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Varun Nair" wrote in message
...
Hi All,

thank you very much for your previous helps.

I have one problem here. I have a folder where i get data in .xls
format.
I
get nearly hundred files a day. I need to manually sit and cut copy
paste
all
the data so that i could have all the data on a single excel sheet.

Request you to design a macro for the same.

Many thanks
--
Varun Nair