How to run Excel Macro on all files in folder

  • Thread starter Thread starter TFMR
  • Start date Start date
T

TFMR

Dear All,

I have Excel Macro and I want to run on all files in folder and then save
all files. Files are in CSV format.

Kindly write the full procedure step by step.

Thanks in advance.
 
Hi TFMR,

There are loads of code snippets in this group that will tell you how
to do this if you have a look around.

This should get you most of the way.

Sub Open_All_Wbks()
'Open all .csv files in specified directory, refresh the query/save/
close
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook

On Error Resume Next
Set wbCodeBook = ThisWorkbook
With Application.FileSearch
.NewSearch
'Change path to suit
.LookIn = "C:\"
.Filename = "*.csv" 'apply to all .csv files
If .Execute > 0 Then 'Workbooks in folder
For lCount = 1 To .FoundFiles.Count 'Loop through all.
'Open Workbook x and Set a Workbook variable to it
'Your code goes here
wbResults.Close savechanges:=True
Next lCount
End If
End With
On Error GoTo 0
End Sub

Cheers,
JF
 
Sub LoopFolders()
Dim oFSO
Dim Folder As Object
Dim Files As Object
Dim file As Object
Dim fldr

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set Folder = oFSO.GetFolder("C:\MyTest")

For Each file In Folder.Files
If file.Type Like "*Comma Separated*" Then
Workbooks.Open Filename:=file.Path
'do stuff
Activeworkbook.save
Activeworkbook.Close
End If
Next file

Set oFSO = Nothing

End Sub
 
Hi Bob,

There is one more query, that files in CSV format and when I run following
macro then again there is popup which asked you want to save and three option
YES NO Cancel

I want to save all the files continiously without asking again. Remember
that files are in CSV format thats why excel verify. Please help me in that.

Thanks & Regards

TFMR
 
Back
Top