Simple macro to import ALL xls files in dir into 1 workbook?

Re: Simple macro to import ALL xls files in dir into 1 workb

Lacking genius , let me recommend this as an alternative:

shell to DOS,
Go to the directory (aka folder) you want
Type: dir *.xls > Excel.txt

that's: dir [space] *.xls [space] > [space] Excel.txt
Now, go to your Excel sheet and import the text file.

Re: Simple macro to import ALL xls files in dir into 1 workb

Well that is ok, but not precisely what I was looking for. thanks...

Re: Simple macro to import ALL xls files in dir into 1 workb

I wish I could say I made this but I didn't (thanks to whoever wrote it) but I have used it in the past and it will do what you are looking for and if you need it I have a code that will take all the worksheets on the new Book and combine them in to one summary sheet just let me know

Edit for typo


Sub puttogether()

Dim fs, numfiles, Direct, dirlen, TheOriginalFile, file_count, filename, tabname
numfiles = 0

TheOriginalFile = ActiveWorkbook.Name

Direct = ActiveWorkbook.Path 'CurDir()
Set fs = Application.FileSearch
With fs
.LookIn = Direct
.FileType = msoFileTypeAllFiles
If .Execute > 0 Then
numfiles = .FoundFiles.Count
Else
MsgBox "There were no files found."
End If
End With

file_count = 1

Do While file_count <= numfiles
If Mid(fs.FoundFiles(file_count), Len(Direct) + 2) = TheOriginalFile Then
'Skips Consolidating File
Else
Workbooks.Open filename:=fs.FoundFiles(file_count)
filename = ActiveWorkbook.Name
tabname = Mid(ActiveWorkbook.Name, 1, Len(ActiveWorkbook.Name) - 4)
Windows(filename).Activate
Sheets(1).Move After:=Workbooks(TheOriginalFile).Sheets(1)
Windows(TheOriginalFile).Activate
End If

file_count = file_count + 1
Loop

End Sub

Re: Simple macro to import ALL xls files in dir into 1 workb

BTW forgot to add this will not look for the files but it needs to be in the Same directory as the files you want to import.

you might want to check out ASAP Utilities too it has an import feature that will let you select the directory see the recommended downloads on this baord good luck and I hope this helps

Re: Simple macro to import ALL xls files in dir into 1 workb

MDuff,

Nice code. However, I suggest two changes:

Change this line:
Sheets(1).Move After:=Workbooks(TheOriginalFile).Sheets(1)

to:
Sheets(1).Copy After:=Workbooks(TheOriginalFile).Sheets(1)

and add this line just after the above line:
Windows(filename).Close

In the first change, the macro will simply copy the details of sheet1 from all subsidiary files, as compared to physically moving Sheet1 from all subsidiary files to the master file.

The second change will close each subsidiary file after its details are copied to the master file. If you don’t add this line, all subsidiary files remain open (in the OP’s case, this means he will have 100 files opened at once).

Regards,

Mike

You guys are AWESOME!!!! I have been looking for something like this for MONTHS!!!!!! THANK YOU!

Re: Simple macro to import ALL xls files in dir into 1 workb

Hi all about this code I am using on some sheets that have links to external workbooks any one know some code I can add to avoid iod the message every time it opens a new book asking tom update the links.

on these sheets I do not need to update information when opening

Also not to abuse too much but these workbooks are very big with a lot of formating and formulas any one know a way to use this code to just copy values?

thanks in advance