Opening multiple files and applying a macro to all of them

[masi_zeyb]

Hi guys,
I'm new in VBA, and I have tried for several days to tackle with my problem by searching in internet, but now it seems I need the professionals help ...
There are more than 100 excel files (*.csv) in different folders. Each excel file contains:

1. 3 columns [A-Date, B-hour (unnecessary), C-Value],
   
2. 1st row is description line,
   
   
3. and 2nd row is col. header (data should be read from 3rd row)

​
Data is stored daily for several years. What I want to do is make it monthly for each year.

So, the description of the macro is like this:

1. Open the excel file. "Sheet1" is renamed.
   
2. Change format of 1st col. (A-Date) to DMY.
   
3. Calculate summation for each month. (summation of values in col.C)
   
4. Write the results in another excel file in following format:

Please refer to the image.

Attachment 10541



'deploy a macro on several excel files in a folder'

Sub main_sub()

Set ThisWB = ActiveWorkbook 'to refer to the original excel'
PathName = "C:\excel macro test\"
Filename = Dir(PathName & "*.csv")
Do While Filename <> ""
Set CurrentWorkbook = Workbooks.Open(PathName & Filename) 'to refer to and write in the newly open file'
' do your stuff
Call a
Call SortMonthly
Filename = Dir()
Loop

End Sub

Sub a()
'
' a Macro
'
' Keyboard Shortcut: Ctrl+g
'
    'Range("A3:A").Select
   'Keyboard Shortcut: Ctrl g
'
    Range("A3:A21100").Select
    Selection.TextToColumns Destination:=Range("A3"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=True
End Sub

Sub SortMonthly()

SheetName = ActiveSheet.Name
i = 3
Sm = 0
r = 4
Fin = True
YR = ActiveSheet.Range("A" & i)
ActiveSheet.Range("E" & i).Select
ActiveCell.FormulaR1C1 = YR

While Fin

While YR = Year(ActiveSheet.Range("A" & i))
M = Month(ActiveSheet.Range("A" & i))

While Month(ActiveSheet.Range("A" & i)) = M
Sm = Sm + ActiveSheet.Range("C" & i)
i = i + 1
Wend

ThisWB.Worksheets("Sheet1").Range("E" & r).Select
ActiveCell.FormulaR1C1 = Sm
Mo = MonthName(M)
ThisWB.Worksheets("Sheet1").Range("D" & r).Select
ActiveCell.FormulaR1C1 = Mo
r = r + 1
Sm = 0
Wend

r = r + 1
YR = Year(ActiveSheet.Range("A" & i))
ActiveSheet.Range("E" & r).Select
ActiveCell.FormulaR1C1 = YR
r = r + 1

If ActiveSheet.Range("A" & i) = "" Then Fin = False

Wend

End Sub

continue with loop in folder

Thank you
Mori