Combine Workbooks from VBA & Macros For Excel Publicatio

[itsme2]

I have many many spreadsheets to search through today for the "boss" so I came across this code in the Mr.Excel's book. THe only problem is that all this is doing is throwing up a blank sheet and only if I rem out the DestWB.Sheets(1).Delete statement otherwise, an error displays stating that I must have at least one active page in a workbook.

I though this was to combine all workbooks into a single workbook. I even copied all my files to a directory called C:\Data thinking maybe that the string to look at the desktop folder just was a bit to much...

Any ideas as to why this won't work?

Sub CombineWorkbooks()
    Dim CurFile As String
    Dim DestWB As Workbook
    Dim ws As Object
    
    Const DirLoc As String = "C:\Data"
    
    Application.ScreenUpdating = False
    
    Set DestWB = Workbooks.Add(xlWorksheet)
    
    CurFile = Dir(DirLoc & "*.xls")
    
    Do While CurFile <> vbNullString
        Dim OrigWB As Workbook
        
        Set OrigWB = Workbooks.Open(Filename:=DirLoc & CurFile, ReadOnly:=True)
        
        'Limit to valid sheet names and remove.xls
        
        CurFile = Left(Left(CurFile, Len(CurFile) - 4), 29)
        
        For Each ws In OrigWB.Sheets
            ws.Copy After:=DestWB.Sheets(DestWB.Sheets.Count)
            
            If OrigWB.Sheets.Count > 1 Then
                DestWB.Sheets(DestWB.Sheets.Count).Name = CurFile & ws.Index
            Else
                DestWB.Sheets(DestWB.Sheets.Count).Name = CurFile
            End If
        Next
        
        OrigWB.Close SaveChanges:=False
        
        CurFile = Dir
    Loop
    
    Application.DisplayAlerts = False
        DestWB.Sheets(1).Delete
    Application.DisplayAlerts = True
    
    Application.ScreenUpdating = True
    Set DestWB = Nothing
End Sub

 

[itsme2]

okay my bad --- it has to be C:\Data\ for the proper directory and apparently for any directory that has subdirectories to it you must ad the underscore -- it appears to be working great and has saved my butt today for I have literally hundrerds of spreadsheet to review and to extrapalate data from


Sorry

)