Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works

Opening multiple files and applying a macro to all of them

  • Please log in to reply



    New Member

  • Member
  • Pip
  • 1 posts
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()

End Sub

Sub a()
' a Macro
' Keyboard Shortcut: Ctrl+g
'Keyboard Shortcut: Ctrl g
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

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

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


End Sub

'continue with loop in folder

Thank you

Attached Thumbnails

  • 9-4-2013 9-25-05 PM.jpg

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

  • 0


Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP