Loop macro for excel & save

[kshivanand21]

Hey Guys...Need your expert help....How to code VBA code, all excel file in a folder need to be looped to run set of task on each excel file than saved in a specified folder & name containing from cell value.

 

[ranman256]

paste the code in to a module,
here the folder to use is in Cell A1, change if needed.
then run : ProcessAllFiles

add your code to ProcessMyFile() for what you want done to the open file.

Option Explicit
Public Sub ProcessAllFiles()
   'give the start folder
ScanFilesIn1Folder  range("A1").value    '"c:\temp\"
End Sub

Private Sub ScanFilesIn1Folder(ByVal pvStartDir)
Dim FileSystem As Object
Dim Folder As Object
Dim oFile As Object
Set FileSystem = CreateObject("Scripting.FileSystemObject")
Set Folder = FileSystem.GetFolder(pvStartDir)
For Each oFile In Folder.Files
    'If InStr(oFile.Name, "backup") > 0 Then GoTo skip1
   
    If InStr(oFile.Name, ".xls") > 0 Then  'only xl files
       ProcessMyFile oFile
    End If
   
skip1:
Next
Set oFile = Nothing
Set Folder = Nothing
Set FileSystem = Nothing
End Sub

Private Sub ProcessMyFile(ByVal poFile)
Workbooks.Open poFile
'do stuff here
ActiveWorkbook.Close True
End Sub

 

[kshivanand21]

Thanks for reply, mate it's working great