Macro to go thru directory of Excel files

Looking for someway to go thru a directory (with or without sub folders) and for every Excel file it finds, open the file and Copy - Paste Special Values each worksheet in the workbook. I wouldn't know how many sheets any given workbook may have until it opened. Can this be done? Copy and paste part is easy enough but hoping not to have to have someone open each one and then run a copy paste macro for each sheet and workbook.

Hi,

Try this:

Sub LoopFiles()

Dim strDir As String, strFileName As String
Dim wbOpenBook As Workbook, wsEachSheet As Worksheet

strDir = "C:\Test\"
strFileName = Dir(strDir & "*.xls")

Do While strFileName <> ""
    Set wbOpenBook = Workbooks.Open(strDir & strFileName)
    For Each wsEachSheet In wbOpenBook.Worksheets
        With wsEachSheet.UsedRange
            .Copy
            .Range("A1").PasteSpecial xlPasteValues
        End With
    Next wsEachSheet
    wbOpenBook.Close True
    strFileName = Dir
Loop

End Sub

Change the directory to search as required.

Dom

Hi,

You can use this, this doesn't take into account subfolder, so you may have to loop threw directories, but adjust the code arcordingly.

Sub Steffen()
     
    Dim objFSO As Object
    Dim objFile As Object
    Const strDIR As String = "Yoy directory to look in"
     
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    MsgBox "There are " & objFSO.GetFolder(strDIR).Files.Count & " files in the folder."
    For Each objFile In objFSO.GetFolder(strDIR).Files
        If InStr(1, objFile, ".xls") > 0 Then
            Dim xlApp As Excel.Application
            Application.DisplayAlerts = False
            Workbooks.Open (objFile)
            ws = ThisWorkbook.Sheets.Count
                For i = 1 To ws
                ' insert procedure here
                ' Format Sheets(1).Range("your range to copy").copy
                ' Workbook.Sheet.Range.Insert (Where to insert the copied range)
                Next i
            Workbooks(objFile.Name).Close
        End If
    Next objFile
     
    Set objFile = Nothing
    Set objFSO = Nothing
    Application.DisplayAlerts = True
     
End Sub

Be aware if you have many files it will be a heavy procedure.

Steffen Thomsen

snb posted examples of how to loop through directories here which includes code to take account of subfolders:

http://www.excelforum.com/tips-and-t...ernatives.html

Dom

Hi stvgarner

I was able to adapt Leith Ross' code from this link to do something similar to what you describe
http://www.excelforum.com/excel-prog...ml#post2474970

Hi stvgarner
Glad to have been of help (if I was).