CODEWITHSUNDEEP
excelvba
Lukas
Lukas

Reputation: 11

Consolidate data from multiple workbooks in a single worksheet



I would like to create a macro function that allows you to collect data from many Excel files (Workbooks) into one a summary statement for a specific stage of implementation and deadline in another file.

All those files will be collected in one folder (also this summary file). I would like to choose the implementation stage and deadline by manually entering (input box) or selecting from the drop-down list of available options after running the macro function (eg. Phase1, term 40 – like on screenshot) and should be supported if another phase is added.

After going through all the files in the above-mentioned folder (completing the data download), macro function needs to sort this data by name or index and calculate the total amount of demand (sum of quantities). (select and calculate values ​​for unique items) but before that it need to sort it in such a way that only the main indexes will be taken for calculations and for the final result (eg. only 1, 2, 3 without 1.1., 1.1.1, 1.2., 2.1. etc). I would like to avoid using filters and pivot table for this if that’s possible and all of this should be done by one macro function.

All of those files will always have only one sheet and may have different names.

I have to do that but i don’t have any knowledge about programming in VBA or even making marcos In Excel so I would be grateful for any help.

I'm adding some screenshots how i see that should working (for Phase 1 and Term 40) and I hope that will help to understand my problem.enter image description here

At this moment I'm working with that code and it works pretty well but i don't know how to change name for reading Workbooks form "Source" to "*" - any name (i mean this Path to file) and loop that for bigger amout of files. Also don't know if i should make another macro for filtering data or combine with this.

Sub CollectData()

Dim i As Long, lCurrRow As Long, lRow As Long, n As Long
Dim wb As Workbook, ans As VbMsgBoxResult

For i = 1 To 5 Step 1

On Error Resume Next
Set wb = Workbooks.Open(ThisWorkbook.Path & "\Source" & i & ".xls")
If Not Err.Number = 0 Then
  Err.Clear

  Set wb = Workbooks.Open(ThisWorkbook.Path & "\Source " & i & ".xls")
  If Not Err.Number = 0 Then
    Err.Clear

    ans = MsgBox("Could not find Source " & i & " Workbook." & vbNewLine & "Do you wis" & _
                 "h to continue?", vbInformation + vbYesNo, "Error")
    If ans = vbNo Then Exit Sub
    GoTo NextI
  End If
End If

With wb.Sheets("RAW_DATA")
  If Not Err.Number = 0 Then
    Err.Clear

    ans = MsgBox("Could not find Source " & i & " Workbook's 'Data Output' tab." & _
                 vbNewLine & "Do you wish to continue?", vbInformation + vbYesNo, "Error")
    If ans = vbNo Then
      wb.Close False
      Exit Sub
    End If
    GoTo NextI
  End If

  If i = 1 Then
    lRow = 1
  Else
    lRow = 2
  End If

  Do Until .Range("A" & lRow).Value = vbNullString
    lCurrRow = lCurrRow + 1
    For n = 0 To 6 Step 1
      Me.Range("A" & lCurrRow).Offset(ColumnOffset:=n).Value = .Range("A" & lRow).Offset(ColumnOffset:=n).Value
    Next n
    lRow = lRow + 1
  Loop
End With
  NextI:
  wb.Close False
Next i
Set wb = Nothing
  End Sub

Upvotes: 0

Views: 189

Answers (1)

AJD
AJD

Reputation: 2438

The Dir() and FileSystemOpbect can be used to find relevant files. The FileSystemObject can be used to locate the Folder, and the Files collection can return a list of files. There are plenty of answers here on StackOverflow that describe the use of both methods.

Im the original code, the workbooks are all opened first. Once you have opened the workbooks:

For Each wb in Application.Workbooks
    ' Code here
Next wb

An alternative approach is to have an array of names of workbooks:

Dim wbNames as Variant
Dim wbName as String
Dim iterator as Lon

wbNames = Array("1.xls", "2.xls")
For iterator = LBound(wbNames) to UBound(wbNames)
    wbName = ThisWorkbook.Path & "\" & wbNames(iterator)
    Set wb = Application.Workbooks.Open(wbName)
    ' Code here
Next iterator

But, if you are using the Files collection, you could also use the lines

For each fFile in foundFiles '<--- remember to declare!
    wbName = ThisWorkbook.Path & "\" & fFile.Name

Depending on how you extract the file names, you could even use:

    wbName = fFile.Path & fFile.Name ` Check this, can't remember if Path includes file name

Breaking code down into manageable chunks by creating sub-functions is usually a good idea too.

Upvotes: 1

Related Questions