Custom workbook consolidation (conceptual question) 1

[VBAjedi]

Ok I'm in the classic position of being asked to provide some quick-fixes for an Excel Hell not of my making. :^) Here's the situation:

VBA is being used to chop a dataset into several hundred pieces which are each inserted into a separate workbook (all with an identical layout, number of rows, etc). Manual changes may or may not be made to the data (not layout) of those individual workbooks, and then we want to roll them back up again to a new executive summary workbook.

Delicious, no?

So conceptually, the interim fix I'm considering is writing a custom "SpiderSum" function that would be used in the worksheet formulas of the executive summary workbook. This function would accept two arguments (both range references):
* "SourceWorkbooks" would be a range containing a list of complete filepaths in column 1, and worksheet names in column 2.
* "TargetRange" would be the range (could be more than one cell) containing the values that need to be summed.

So =SpiderSum(SourceWorkbooks,TargetRange) would loop through every workbook listed in SourceWorkbooks column 1, creating a running total of all the values in TargetRange on the sheet specified in SourceWorkbooks column 2.

Even with xlCalculation set to manual and ScreenUpdating off, this would definitely to take a while to run. The advantage is that it would give the users the ability to modify the Executive Summary layout and source workbooks without rewriting any VBA...

I think I can write the code for this but conceptually, what do you think? Anyone done something similar? Am I reinventing the wheel or creating a monster (keeping in mind that I'm aware that the true/best solution is to not fragment the data in the first place)?

Fire away! ;-)


VBAjedi

 

[Gruuuu]

How likely is the "changes may or may not be made"?

You may be able to save time if you can refrain from updating from files that have not changed. (have the same modified date as the time they were updated by the main book?)

Another option may be to have those Little-Bastard-Workbooks (for lack of a more appropriate term) kick off a process before saving. Maybe something that only reports any changes made? You wouldn't be able to append the data to a single file, since you would get read-only issues. But a flat text file of change only data is a lot smaller than an excel file with all the data, which translates to less processing time.

 

[SkipVought]

...chop a dataset...

THAT's the SOURCE!

Why go thru all those fragments in workbooks???

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Ahhh, I see that the data in the workbooks is being modified.

I might be apt to write a summing function via ADODB.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[VBAjedi]

Hey Skip!

Trust me, that was the FIRST question I asked. And that's where I want to take things back to eventually I just have to convince them that I can create a tool using PivotTables and Filters that gets them the answers they need without physically chopping the data up.

The catch, as you might guess, is that each of those data fragments is "owned" by a different division/person and we don't trust them with access to the whole chunk of data, but they need to be able to make changes to their area. That piece will be more difficult to implement in Excel (the whole thing really needs a relational database solution and so is well beyond what Excel was designed for).

Anyway, for now I'm in quick-fix-land (as in, "Deliver by Friday!"). I confess that I haven't worked with ADODB. Worth trying to get up to speed on that for a quick fix? I'm leaning towards building a VBA SpiderSum function because that's what I'm familiar with...

VBAjedi

 

[VBAjedi]

Gruuu,

I think your suggestions will play a role in my eventual overhaul of this process. For now the powers that be will gladly take a little longer code execution time in exchange for not having to trust my code to know when something has changed!

Thanks!

VBAjedi

 

[Gruuuu]

:/

What about, a source data page, an "editing" page, and a "difference between the two" page? It's a bit overt, but should be enough to show them that Excel does all the thinking?
Seriously, 98% of your processing time will be in retrieving data from all the workbooks. The application has to actually open the file to link to/retrieve from it (it's locked and everything). I assume all of these workbooks are on a network drive? If so, you're also downloading each one over your network every time you link to it.

Of course, if you make it TOO efficient, they may not ever want to walk away from the process to do it up right...

 

[VBAjedi]

That would work... I just don't have time or permission to restructure all those fragment workbooks by Friday. The more I think about it, the more I conclude a UDF is the quickest fix...

Quick question... I want to store this UDF in an add-in instead of in the workbook(s) that will be using it. In a UDF, what's the correct syntax to set a Workbook variable to the workbook calling the UDF (when you don't know that workbooks name?). Something like:

Set DestWkbk = ActiveWorkbook

except I don't know that ActiveWorkbook will work (I'm thinking the workbook that calls my UDF might not have the focus?).

VBAjedi

 

[PHV]

What about Application.Caller ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[VBAjedi]

Hmmm... that UDF is proving more difficult to write than I thought. For some reason the "Set SourceWkbk = ..." line isn't working (SourceWkbk is still nothing) in this code:

Function SpiderSum(SourceWorkbooksList As Range, TargetRange As Range)
' SourceWorkbooksList needs to have complete filepathnames in column 1, and
' a target worksheet name for each workbook in column 2

  Dim DestWkbk As Workbook, SourceWkbk As Workbook
  Dim SourceSheet As Worksheet
  Dim FilePathname As String

  Set DestWkbk = ActiveWorkbook
  a = DestWkbk.Name

  For x = 1 To SourceWorkbooksList.Rows.Count
    FilePathname = SourceWorkbooksList.Cells(x, 1)
    On Error Resume Next
    B = Dir(FilePathname)
    If B <> "" Then    ' File Exists
      Set SourceWkbk = Application.Workbooks.Open(FilePathname)
      c = SourceWkbk.Name
      Set SourceSheet = SourceWkbk.Sheets(SourceWorkbooksList.Cells(x, 2))
    End If
    On Error GoTo 0
    If Not SourceSheet Is Nothing Then
      ' Add code here to sum TargetRange values
    Else
      ' Alert user that workbook was not found
    End If
    Set SourceSheet = Nothing
    Set SourceWkbk = Nothing
  Next x

End Function

I think I've used this approach before successfully... am I running into one of the limitations of working in a Function instead of a Sub?

VBAjedi

 

[SkipVought]

Here's some code using MS Query, no ADODB required...

Sub CombineAllSheets()
'SkipVought: 2010 feb 26
'assuming that ALL sheets have
' 1) the same number of columns of data
' 2) one row of headings in row 1
' 3) each corresponding column of data has the same TYPE of data on each sheet
' then the query will consolidate the data into the Summary sheet

    Dim ws As Worksheet, sSQL As String, iCnt As Integer, sConn As String
    
    On Error Resume Next
    If IsError(Sheets("Summary")) Then Worksheets.Add.Name = "Summary"
    On Error GoTo 0
    
    sConn = "ODBC;DSN=Excel Files;"
    sConn = sConn & "DBQ=" & ThisWorkbook.Path & "\" & Split(ThisWorkbook.Name, ".")(0) & ".xls;"
    sConn = sConn & "DefaultDir=" & ThisWorkbook.Path & ";"
    sConn = sConn & "DriverId=790;MaxBufferSize=2048;PageTimeout=5;"
    
    For Each ws In Worksheets
        If ws.Name <> "Summary" Then
            iCnt = iCnt + 1
            sSQL = sSQL & "Select *, '" & ws.Name & "' as Sheet"
            sSQL = sSQL & vbLf
            sSQL = sSQL & "From [" & ws.Name & "$]"
            sSQL = sSQL & vbLf
            If iCnt < Worksheets.Count - 1 Then
                sSQL = sSQL & "UNION ALL"
                sSQL = sSQL & vbLf
            End If
        End If
    Next
    Debug.Print sSQL
    
    With Sheets("Summary")
        If .QueryTables.Count = 0 Then
            With Sheets("Summary").QueryTables.Add(Connection:=sConn, _
                Destination:=Sheets("Summary").Range("A1"))
                .CommandText = sSQL
                .Name = "qryALL"
                .FieldNames = True
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .BackgroundQuery = True
                .RefreshStyle = xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .PreserveColumnInfo = True
                .Refresh BackgroundQuery:=False
            End With
        Else
            With Sheets("Summary").QueryTables("qryALL")
                .Connection = sConn
                .CommandText = sSQL
                .Refresh BackgroundQuery:=False
            End With
        End If
    End With
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[VBAjedi]

Ok Skip, that feels like it's on the right track to a good solution here. Big star for that!

Now to figure out if it will work in my situation... instead of consolidating a bunch of sheets in a single workbook, I need to consolidate a single sheet from a bunch of workbooks. Is it possible to create a connection string (sConn) to generate a querytable that references a specific range (ex. A1:K50) on a single sheet in 50 or so different workbooks at once?

I'm reading the VBA help files on querytables now, but as usual they are underwhelmingly helpful ;-)



VBAjedi

 

[SkipVought]

Yes. Loop using a FileSystemObject to get the workbook names, assuming they are all in a folder or defined set of folders. Assign the conection string and query.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]