PDA

View Full Version : Opening multiple files and applying a macro to all of them



masi_zeyb
09-04-2013, 07:10 AM
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],
[*=1]1st row is description line,

[*=1]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.
[*=1]Change format of 1st col. (A-Date) to DMY.
[*=1]Calculate summation for each month. (summation of values in col.C)
[*=1]Write the results in another excel file in following format:

Please refer to the image.

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