Combine Workbooks from VBA & Macros For Excel Publicatio

itsme2

Board Regular
Joined
Jul 29, 2006
Messages
66
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?


Code:
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

itsme2

Board Regular
Joined
Jul 29, 2006
Messages
66
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

:eek:)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,745
Messages
5,638,114
Members
417,009
Latest member
Emily00028

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top