Dynamic file reading Excel 2007 VBA/Macro

   1: Sub Calculate()

   2: '

   3: ' Calculate Macro

   4: '

   5: Application.ScreenUpdating = False

   6:  

   7: Dim Row1 As Integer     ' to store row number of input file, to perform navigation

   8: Dim Col1 As Integer     ' to store col number of input file, to perform navigation

   9: Dim Row2 As Integer     ' to store row number of s tab of output file, to perform navigation

  10: Dim Col2 As Integer     ' to store col number of s tab of output file, to perform navigation

  11: Dim i As Integer    ' for loop to navigate through 24 hrs

  12: Dim k As Integer    ' for loop to navigate through 5 s

  13: Dim l As Integer    ' for loop to navigate through 7 days

  14: Dim Hrs(1 To 24) As Integer     ' to identify 24 hrs

  15: Dim Path1 As String     ' path of the input/output files

  16: Dim Filename1(1 To 7) As String     ' names of the input files

  17: Dim Filename2 As String     ' name of the output file

  18: Dim Sum1 As Long    ' to store sum of SM-Ds of each hr

  19: Dim Flag1 As Integer    ' to identify if data of any hr is missing

  20:  

  21: For i = 1 To 24     ' to initialize hrs array with hr nums 0 to 23

  22:     Hrs(i) = i - 1

  23: Next i

  24:  

  25: Sheets("S").Select   ' to clear previous values of s tab

  26: Range("B3:AP26").Select

  27: Selection.ClearContents

  28: Range("A1").Select

  29:  

  30: Sheets("Main Menu").Select      ' to read path and file names

  31: Path1 = Range("C2").Text

  32: Filename1(1) = Range("D2").Text

  33: Filename1(2) = Range("D3").Text

  34: Filename1(3) = Range("D4").Text

  35: Filename1(4) = Range("D5").Text

  36: Filename1(5) = Range("D6").Text

  37: Filename1(6) = Range("D7").Text

  38: Filename1(7) = Range("D8").Text

  39: Filename2 = Range("C10").Text

  40:  

  41: Col2 = 2    ' col num of s tab of output file

  42:  

  43: For l = 1 To 7      ' for loop to navigate through 7 days

  44:     

  45:     Workbooks.Open Filename:=Path1 + Filename1(l)       ' open each input file one by one

  46:     Sheets("Throughput per S - table").Select

  47:     Row1 = 10   ' row number of input file

  48:     Col1 = 4    ' col number of input file

  49:     Row2 = 3    ' row number of s tab of output file

  50:     

  51:     For k = 1 To 5    ' for loop to navigate through 5 s

  52:         

  53:         For i = 1 To 24     ' for loop to navigate through 24 hrs

  54:         

  55:             Sum1 = 0    ' to store sum of SM-Ds of each hr

  56:             Flag1 = 0   ' to identify if data of any hr is missing - 0 means data for that hr is not available

  57:             Windows(Filename1(l)).Activate

  58:             Sheets("Throughput per S - table").Select

  59:       

  60:             If Cells(Row1, Col1).Value = Hrs(i) Then    ' first row of an hr

  61:             

  62:                 Flag1 = 1   ' to identify if data of any hr is missing - 1 means data for that hr is available

  63:             

  64:                 If (Left(Cells(Row1, Col1 + 1).Value, 4) = "SM-D") Then     ' if SM-D found

  65:                                                  

  66:                     Sum1 = Sum1 + Cells(Row1, Col1 + 3).Value   ' sum SM-D of that hr

  67:                           

  68:                 End If

  69:                 

  70:                 Row1 = Row1 + 1     ' increment row number of input file

  71:             

  72:                 Do While (Cells(Row1, Col1).Value = 0) And Row1 < 1200  ' 2nd row and onwards of an hr

  73:                 

  74:                     If (Left(Cells(Row1, Col1 + 1).Value, 4) = "SM-D") Then     ' if SM-D found

  75:                                         

  76:                        Sum1 = Sum1 + Cells(Row1, Col1 + 3).Value    ' sum SM-D of that hr

  77:                     

  78:                     End If

  79:                                      

  80:                     Row1 = Row1 + 1     ' increment row number of input file

  81:                                 

  82:                 Loop

  83:                

  84:             End If

  85:                

  86:             If Flag1 = 0 Then   ' if flag1 is 0 then increment row number of s tab of output file to skip empty hr

  87:                

  88:                 Row2 = Row2 + 1

  89:                

  90:             ElseIf Flag1 = 1 Then   ' if flag1 is 1 then put value in output file

  91:             

  92:                 Windows(Filename2).Activate

  93:                 Sheets("S").Select

  94:                 Cells(Row2, Col2).Value = Sum1

  95:                 Row2 = Row2 + 1     ' increment row number of output file

  96:                 

  97:             End If

  98:                  

  99:         Next i          ' for loop to navigate through 24 hrs

 100:         

 101:         Row1 = Row1 + 1     ' increment row number of input file

 102:         Col2 = Col2 + 1     ' increment col number of output file

 103:         Row2 = 3    ' row number of output file

 104:     

 105:     Next k      ' for loop to navigate through 5 s

 106:     

 107:     Col2 = Col2 + 1     ' increment col number of output file

 108:     Workbooks(Filename1(l)).Close

 109:  

 110: Next l      ' for loop to navigate through 7 days

 111:  

 112: Sheets("Main Menu").Select

 113: Application.ScreenUpdating = True

 114:  

 115: End Sub

Reference MsgBox

http://www.excel-vba.com/vba-code-2-7-message-input.htm

-urShadow