many workbooks in one.

[Cesar Zapata]

Hi,

I got this code posted on the groups.......

Sub Consolidate()
With Application.FileSearch
.NewSearch
'Change this to your directory
.LookIn = "C:\Excel\delete these"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
OpenIt = MsgBox("File: " & .FoundFiles(i) & Chr(13) & _
"Do you want to include this?", vbYesNo)
If OpenIt = vbYes Then
Workbooks.Open .FoundFiles(i)
CloseIt = vbNo
If ActiveWorkbook.Worksheets.Count > 1 Then
CloseIt = vbYes
Set myBookToClose = ActiveWorkbook
End If
ActiveSheet.Name = Left(ActiveWorkbook.Name, _
Len(ActiveWorkbook.Name) - 4)
ActiveSheet.Move After:=ThisWorkbook.Sheets(1)
If CloseIt = vbYes Then myBookToClose.Close False
End If
Next i
Else: MsgBox "There were no files found."
End If

End With
The problem is that directory is in a intranet using
http://companyname/storesI changed the path and it did not work.. Any
ideas?Thanks

 

[Bernie Deitrick]

Cesar,

That is a copy of my code.

If your are using an intranet, don't use the http: but use the \\
drive designation of the drive/folder, along the lines of -
\\Volume\folder1\folder2\

HTH,
Bernie
MS Excel MVP