[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:

• 3 columns [A-Date, B-hour (unnecessary), C-Value],
• 1st row is description line,
• 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:

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

Please refer to the image. [or http://bit.ly/1aiZJ33]

'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

Attached Thumbnails

• 

Edited by Masi_Zeyb, 04 September 2013 - 09:57 AM.