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

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" <(E-Mail Removed)> wrote in message
newsDCF1A90-C22D-4A88-B6CF-(E-Mail Removed)...
> 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
>

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" <(E-Mail Removed)> wrote in message
> newsDCF1A90-C22D-4A88-B6CF-(E-Mail Removed)...
> > 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
> >
>
>
>

This code ONLY works on a single folder.

--
HTH

Bob Phillips

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

"Varun Nair" <(E-Mail Removed)> wrote in message
news:999AB088-59B3-4118-89E7-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> > newsDCF1A90-C22D-4A88-B6CF-(E-Mail Removed)...
> > > 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
> > >
> >
> >
> >