Hi,
I had used this code in the past to import all files in a directory to a one excel workbook but it seems that [VBA]Application.FileSearch [/VBA]no longer works in office 2010 I have tried to search the internet but could not find any fixes
any ideas on how I can get this code to work in XL2010?
thanks
[VBA]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).Copy After:=Workbooks(TheOriginalFile).Sheets(1)
Windows(filename).Close
Windows(TheOriginalFile).Activate
End If
file_count = file_count + 1
Loop
End Sub
[/VBA]