***PLease help. Need to Merge directory of files into one file.***

I have a bunch of excel files stored in c:/temp. I want to merge all of
these files into one excel spreadsheet.

Sub consolidatebooks()
Dim sh as Worksheet
Dim sPath as String, sName as String
Dim bk as workbook
set sh = Activesheet
sh.cells.clearcontents
sPath = "C:\Myfolder\"
sName = dir(sPath & "*.xls")
do while sName <> ""
set bk = workbooks.Open(sPath & sname)
' copy sheets?
'bk.worksheets.copy After:= _
thisworkbook.Worksheets(thisworbook.worksheets.count)
' copy some data
' bk.worksheets(1).Range("A1").currentRegion.copy _
sh.cells(rows.count,1).End(xlup)(2)
bk.close SaveChanges:=False
sname = dir()
Loop
End Sub

--
Regards,
Tom Ogilvy

"Shani" wrote:

> I have a bunch of excel files stored in c:/temp. I want to merge all of
> these files into one excel spreadsheet.
>
>

This code gets all matching files from a directory - change my message
box to whatever you need it to do (insert file by the sound of it!)


Sub lookin()



Set fs = Application.FileSearch
Dim newbook, curbook, MyCount, myrange
With fs
.lookin = "c:\temp\usb"
'LookIn should be changed to the location you want to use
..SearchSubFolders = True 'assuming you DO want to search subfolders

.Filename = "*.xls"
'if you are looking for other than DOC files, alter the line above
If .Execute(SortBy:=msoSortByFileName,
SortOrder:=msoSortOrderAscending) > 0 Then
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)

Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub

doesn't do anything

this doesnt do anything

there is a syntax error.


If .Execute(SortBy:=msoSortByFileName

Tom left the definition of Merge to you.

do while sName <> ""
set bk = workbooks.Open(sPath & sname)
' copy sheets?
'bk.worksheets.copy After:= _
thisworkbook.Worksheets(thisworbook.worksheets.count)
' copy some data
' bk.worksheets(1).Range("A1").currentRegion.copy _
sh.cells(rows.count,1).End(xlup)(2)
bk.close SaveChanges:=False
sname = dir()
Loop

The top part of this portion of the code copies all the worksheets in each
workbook to this workbook.

The bottom part of this portion of the code copies all the data in each sheet to
a single sheet within this new workbook.

Since you didn't give any hint about what you meant by merge, Tom left it up to
you to decide.

You'll have to uncomment the lines that do the type of work you want.

' copy sheets?
' copy some data

Are instructions to you--don't uncomment them.

And since you didn't give any rules, Tom's code uses Column A to determine the
next available cell and assumes that all your data in each sheet is contiguous.

======
Ps.

I think you meant:

Tom, Thank you for your response, but this doesn't seem to do anything when I
try it. What can I do to make it work the way I want?

And then you should give a little more info about how you want it to work.






Shani wrote:
>
> this doesnt do anything

--

Dave Peterson

It doesn't open and close the workbooks in the directory?

I gave you some choices, possibly you are frozen by indecision.

Work with Aiden.

--
Regards,
Tom Ogilvy


"Shani" wrote:

> this doesnt do anything
>
>

> there is a syntax error.
>
>
> If .Execute(SortBy:=msoSortByFileName

given this post, how could you post your first post to Aiden.

Perhaps this isn't a serious request and you are just toying with use.
Enjoy. <g>

by the way, you just have copied the code improperly and haven't corrected
for wordwrap.

--
Regards,
Tom Ogilvy


"Shani" wrote:

> there is a syntax error.
>
>
> If .Execute(SortBy:=msoSortByFileName
>
>