Macro to paste values for all files in folder

[Brent77]

Can anyone please help me with a macro that I set a folder path and takes all Excel workbooks in that folder and Pastes Special: Values on the whole thing, then saves and closes the workbook and moves onto the next until all the Excel files in the folder are just values. Thanks for all your help!!

 

[Cindy Ellis]

I can get you started...the following code will open each file in the selected directory, do the process you define for it, save and close it, and move on to the next.

Sub ProcessFilesInFolder()
    Dim folderName As String, filePathName As String, FileName As String
    Dim WBName As String, DSName As String
    
    folderName = "C:\temp\"  'Change this to your directory
    FileName = Dir(folderName & "*.xls", vbNormal)
    
    While FileName <> ""
    
        filePathName = folderName & FileName
        Workbooks.Open FileName:=filePathName
            
        WBName = ActiveWorkbook.Name  'use WBName to refer to the name of the current workbook
        DSName = ActiveSheet.Name 'use DSName to refer to the name of the worksheet
                                  'that was created when you opened the text file
                                  
        '***********your code here*************
        
        'do whatever you need to do to the file here.
        MsgBox (WBName)   'this is just for testing...remove it when you know you're getting the files you want
        
        
        '***********end of your code here*************
        
        
        Workbooks(WBName).Close SaveChanges:=True
        
        FileName = Dir()   'this gets the name of the next file
        
    Wend

End Sub

I would suggest that you record a macro to process one of your workbooks, so that the appropriate sheet names, etc. will be captured in the macro (assuming they're all similar in structure), then paste the recorded code into the section between the asterisks.
Hope that helps.
Cindy

 

[Jonmo1]

Welcome to the board..

Try this on for size.
TEST ON DUMMY BOOKS FIRST!!!!

Sub PasteValues()
Dim ws As Worksheet
Dim c As Range, CRange As Range
Dim MyPath As String, BookName As String
Dim MyBook As Workbook
 
MyPath = "C:\Full\Path\To\Files\" 'This nees to include the \ at the end
BookName = Dir(MyPath & "*.xls")
 
Do Until BookName = ""
    Set MyBook = Workbooks.Open(MyPath & BookName)
    For Each ws In MyBook.Worksheets
        On Error Resume Next
        Set CRange = ws.UsedRange.SpecialCells(xlCellTypeFormulas)
        On Error GoTo 0
        If Not CRange Is Nothing Then
            For Each c In CRange
                c.Value = c.Value
            Next c
        End If
    Next ws
    MyBook.Close (True)
    BookName = Dir
Loop
End Sub

Hope that helps.

 

[Brent77]

Thanks everyone!!!! Johnmo1, I tried yours first and it worked!!! You guru's are great! Thanks for the reply too Cindy!