Open all files in a folder, run a macro

[MOB]

I have 2,000 spreadsheets in a folder - I would like some code that;

- opens each spreadsheet one at a time
- in sheet "Summary", copy/paste special whole sheet
- in sheet "Summary", columns C&D - I only want the first letter of any text to remain (converting names to initials)
- delete all sheets except "Summary"
- save and close the file, but keep Excel open

Is this possible? I'm comfortable with VBA for most of the above except for opening each file in folder.

TIA

 

[mumps]

Try:

Sub MOB()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim wkbSource As Workbook, ws As Worksheet, rng As Range, temp As String, LastRow As Long, Val As Variant
    Const strPath As String = "C:\Test\" 'change folder path to suit your needs
    ChDir strPath
    strExtension = Dir(strPath & "*.xlsx")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension)
        For Each ws In Sheets
            If ws.Name <> "Summary" Then
                Application.DisplayAlerts = False
                ws.Delete
                Application.DisplayAlerts = True
            End If
        Next ws
        With Sheets("Summary")
            LastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .UsedRange.Cells.Value = .UsedRange.Cells.Value
            For Each rng In .Range("C2:D" & LastRow)
                Val = Split(rng, " ")
                For i = LBound(Val) To UBound(Val)
                    If temp = "" Then temp = Left(Val(i), 1) Else temp = temp & Left(Val(i), 1)
                Next i
                rng = temp
                temp = ""
            Next rng
        End With
        Application.DisplayAlerts = False
        wkbSource.Close savechanges:=False
        Application.DisplayAlerts = True
        strExtension = Dir
    Loop
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

 

[MOB]

First file opens, but before it starts to change text to just the first letter, it fails at;

rng = temp

"Run time error 1004, Application-defined or object-defined error"

 

[mumps]

I think that it would be easier to help and test possible solutions if I could work with your actual files which includes any macros you are currently using. Perhaps you could upload a copy of your macro file and a copy of the first files it opens to a free site such as www.box.com or www.dropbox.com. Once you do that, mark each file for 'Sharing' and you will be given a link to each file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[MOB]

Will do, thanks for your help!