Pages

Wednesday, January 20, 2010

Copying data from multiple excel files to one excel file through Macro / VBA

This is my first VBA program:) I wanted to copy a specific data form 14 files into one file. The macro I developed to do this is below. Its exactly developed in the way I wanted it but u can change it according to ur requirements. Now I am working on to use for loop to make the code efficient.

   1: Sub Macro5()


   2: '


   3: Dim FileName1 As String


   4: Dim Day1 As String


   5: Dim FileName2 As String


   6: Dim FileName3 As String


   7: Dim FileName4 As String


   8: Dim FileName5 As String


   9: Dim OutputFileName1 As String


  10:  


  11: FileName1 = "\\folder\"


  12: FileName5 = "1- January 2010\"


  13: Day1 = "Jan 11"


  14: Day2 = "Jan 12"


  15: Day3 = "Jan 13"


  16: Day4 = "Jan 14"


  17: Day5 = "Jan 15"


  18: Day6 = "Jan 16"


  19: Day7 = "Jan 17"


  20: FileName2 = "\file_"


  21: FileName3 = " 2010.xls"


  22:  


  23: FileName4 = "\file2_"


  24:  


  25: OutputFileName1 = "Week 02 - KPIs.xlsm"


  26:     


  27: ' A -----------------------------------------------------------------------


  28:     


  29:     Workbooks.Open Filename:=FileName1 + FileName5 + Day1 + FileName2 + Day1 + FileName3


  30:     Rows("3:8").Select


  31:     Selection.Copy


  32:     Windows(OutputFileName1).Activate


  33:     Sheets("A").Select


  34:     Range("A02").Select


  35:     ActiveSheet.Paste


  36:     Application.CutCopyMode = Flase


  37:     Workbooks(Right(FileName2, 22) + Day1 + FileName3).Close


  38:  


  39:     


  40:     Workbooks.Open Filename:=FileName1 + FileName5 + Day2 + FileName2 + Day2 + FileName3


  41:     Rows("3:8").Select


  42:     Selection.Copy


  43:     Windows(OutputFileName1).Activate


  44:     Range("A08").Select


  45:     Sheets("A").Select


  46:     ActiveSheet.Paste


  47:     Application.CutCopyMode = Flase


  48:     Workbooks(Right(FileName2, 22) + Day2 + FileName3).Close


  49:     


  50:     Workbooks.Open Filename:=FileName1 + FileName5 + Day3 + FileName2 + Day3 + FileName3


  51:     Rows("3:8").Select


  52:     Selection.Copy


  53:     Windows(OutputFileName1).Activate


  54:     Range("A14").Select


  55:     Sheets("A").Select


  56:     ActiveSheet.Paste


  57:     Application.CutCopyMode = Flase


  58:     Workbooks(Right(FileName2, 22) + Day3 + FileName3).Close


  59:     


  60:     Workbooks.Open Filename:=FileName1 + FileName5 + Day4 + FileName2 + Day4 + FileName3


  61:     Rows("3:8").Select


  62:     Selection.Copy


  63:     Windows(OutputFileName1).Activate


  64:     Range("A20").Select


  65:     Sheets("A").Select


  66:     ActiveSheet.Paste


  67:     Application.CutCopyMode = Flase


  68:     Workbooks(Right(FileName2, 22) + Day4 + FileName3).Close


  69:     


  70:     Workbooks.Open Filename:=FileName1 + FileName5 + Day5 + FileName2 + Day5 + FileName3


  71:     Rows("3:8").Select


  72:     Selection.Copy


  73:     Windows(OutputFileName1).Activate


  74:     Range("A26").Select


  75:     Sheets("A").Select


  76:     ActiveSheet.Paste


  77:     Application.CutCopyMode = Flase


  78:     Workbooks(Right(FileName2, 22) + Day5 + FileName3).Close


  79:     


  80:     Workbooks.Open Filename:=FileName1 + FileName5 + Day6 + FileName2 + Day6 + FileName3


  81:     Rows("3:8").Select


  82:     Selection.Copy


  83:     Windows(OutputFileName1).Activate


  84:     Range("A32").Select


  85:     Sheets("A").Select


  86:     ActiveSheet.Paste


  87:     Application.CutCopyMode = Flase


  88:     Workbooks(Right(FileName2, 22) + Day6 + FileName3).Close


  89:     


  90:     Workbooks.Open Filename:=FileName1 + FileName5 + Day7 + FileName2 + Day7 + FileName3


  91:     Rows("3:8").Select


  92:     Selection.Copy


  93:     Windows(OutputFileName1).Activate


  94:     Range("A38").Select


  95:     Sheets("A").Select


  96:     ActiveSheet.Paste


  97:     Application.CutCopyMode = Flase


  98:     Workbooks(Right(FileName2, 22) + Day7 + FileName3).Close


  99:     


 100:     Range("A02").Select


 101:     


 102: 'M -------------------------------------------------------------------------


 103:     


 104:     Workbooks.Open Filename:=FileName1 + FileName5 + Day1 + FileName4 + Day1 + FileName3


 105:     Rows("3:8").Select


 106:     Selection.Copy


 107:     Windows(OutputFileName1).Activate


 108:     Sheets("M").Select


 109:     Range("A02").Select


 110:     ActiveSheet.Paste


 111:     Application.CutCopyMode = Flase


 112:     Workbooks(Right(FileName4, 19) + Day1 + FileName3).Close


 113:  


 114:     


 115:     Workbooks.Open Filename:=FileName1 + FileName5 + Day2 + FileName4 + Day2 + FileName3


 116:     Rows("3:8").Select


 117:     Selection.Copy


 118:     Windows(OutputFileName1).Activate


 119:     Range("A08").Select


 120:     Sheets("M").Select


 121:     ActiveSheet.Paste


 122:     Application.CutCopyMode = Flase


 123:     Workbooks(Right(FileName4, 19) + Day2 + FileName3).Close


 124:     


 125:     Workbooks.Open Filename:=FileName1 + FileName5 + Day3 + FileName4 + Day3 + FileName3


 126:     Rows("3:8").Select


 127:     Selection.Copy


 128:     Windows(OutputFileName1).Activate


 129:     Range("A14").Select


 130:     Sheets("M").Select


 131:     ActiveSheet.Paste


 132:     Application.CutCopyMode = Flase


 133:     Workbooks(Right(FileName4, 19) + Day3 + FileName3).Close


 134:     


 135:     Workbooks.Open Filename:=FileName1 + FileName5 + Day4 + FileName4 + Day4 + FileName3


 136:     Rows("3:8").Select


 137:     Selection.Copy


 138:     Windows(OutputFileName1).Activate


 139:     Range("A20").Select


 140:     Sheets("M").Select


 141:     ActiveSheet.Paste


 142:     Application.CutCopyMode = Flase


 143:     Workbooks(Right(FileName4, 19) + Day4 + FileName3).Close


 144:     


 145:     Workbooks.Open Filename:=FileName1 + FileName5 + Day5 + FileName4 + Day5 + FileName3


 146:     Rows("3:8").Select


 147:     Selection.Copy


 148:     Windows(OutputFileName1).Activate


 149:     Range("A26").Select


 150:     Sheets("M").Select


 151:     ActiveSheet.Paste


 152:     Application.CutCopyMode = Flase


 153:     Workbooks(Right(FileName4, 19) + Day5 + FileName3).Close


 154:     


 155:     Workbooks.Open Filename:=FileName1 + FileName5 + Day6 + FileName4 + Day6 + FileName3


 156:     Rows("3:8").Select


 157:     Selection.Copy


 158:     Windows(OutputFileName1).Activate


 159:     Range("A32").Select


 160:     Sheets("M").Select


 161:     ActiveSheet.Paste


 162:     Application.CutCopyMode = Flase


 163:     Workbooks(Right(FileName4, 19) + Day6 + FileName3).Close


 164:     


 165:     Workbooks.Open Filename:=FileName1 + FileName5 + Day7 + FileName4 + Day7 + FileName3


 166:     Rows("3:8").Select


 167:     Selection.Copy


 168:     Windows(OutputFileName1).Activate


 169:     Range("A38").Select


 170:     Sheets("M").Select


 171:     ActiveSheet.Paste


 172:     Application.CutCopyMode = Flase


 173:     Workbooks(Right(FileName4, 19) + Day7 + FileName3).Close


 174:   


 175:     Range("A02").Select


 176:     Sheets("A").Select


 177:     


 178:     


 179: End Sub


 180:  




References:



For taking substring



http://www.programmersheaven.com/mb/vba/97607/97607/substring-function-in-vba/



http://www.xtremevbtalk.com/showthread.php?t=277326



Function Right()



http://www.techonthenet.com/access/functions/string/right.php



Closing a file



http://www.contextures.com/xlfaqMac.html#Close



http://www.vbforums.com/showthread.php?t=534068



http://www.vbforums.com/showthread.php?t=511723



Closing a file with copypaste mode off



http://www.exceltip.com/st/Close_a_workbook_using_VBA_in_Microsoft_Excel/469.html



String concatenation



http://www.vbtutor.net/lesson6.html



Opening a file



http://www.ozgrid.com/VBA/loop-through.htm



Variables



http://www.ozgrid.com/VBA/variables.htm



Duplicating Conditional Formatting Rules



http://excelusergroup.org/forums/p/497/1480.aspx



http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.excel.programming&tid=80c0a31b-6383-461a-bc36-b8b88aef0ed7&cat=en_US_3a793e1f-4961-419d-9ec7-899d6e6086cd&lang=en&cr=US&sloc=&p=1



http://blogs.msdn.com/excel/archive/2006/05/09/594200.aspx



Function IF()



http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=242



http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=225















- urShadow

No comments:

Post a Comment