Creating a pivot table with multiple sheets

[xlsaffer]

Hi
I am trying to create a pivot table using multiple sheets. I looking for some code that will replace the "consolidated ranges" in the Pivot Table Wizard. I am looking for code because I am writing a macro that will create a different number of worksheets in the Workfile, depending on which dataset I use. i.e File 1 may have 75 worksheets, whereas File 2 may have 120 worksheets. '

The ranges on each of the worksheets will be the same. Range("A2:Av48")

Any help or directions to other references will be gratefully received.

Thanks

 

[Greg Truby]

I really am looking forward to checking this out further tomorrow at work. To facilitate my frequent need to consolidate monthly report data into bigger sets I long ago developed an add-in that uses a userform to list out the worksheets in the workbook and then does a validation check to see that the headers are identical and some other stuff. It also offers the ability to set AutoFilter criteria to try to keep the datasets under 65K. You can bet I'll be exploring how to incorporate this into that Add-In!

 

[ALOHASWING]

This is awesome! How would I change the code to provide the same functionality to a single, similar worksheets in mutliple files? For example, I have a large file each month that contains 25,000 records. I want to do year-to-date reporting using these single workbooks contained in multiple files (one for each month).

Also, how do I ensure my ranges are captured correctly? I tried using the code and it didn't pick up my records. I also tried using the dynamic range name I created, but that didn't work either. Any thoughts?

 

[Fazza]

Hi alohaswing.

I just saw this after replying to your pm. Sorry but I hadn't seen this post already.

A few things.

Dynamic named ranges don't work with these database type queries - external data queries / ADO / etc.

There will be problems (I'm pretty sure there will be, anyway) if you do this sort of thing when the source data files are opened. So could be a problem in a network environment - if this is likely then some choices might be to overcome this with error checking in the VBA, or, have the source data files run a little VBA on file closure to create a data file in a sub-directory. Then the file that pulls the data can rely on these data files in the sub-directory always being closed. I won't worry about this for the exercise.

It is best done with some VBA to create the SQL - as opposed to simply hard coding the source file information. I don't know how your source files are but I'll just assume all in one subdirectory. You'll see that in the code. Though one could easily set up a dialog box to select the files, I'll just assume all files in the subdirectory.

The query needs to identify the data. So either a worksheet name (for example the data in every file is always on a sheet called "monthly data" regardless of which monthly file it is) or a defined name, again the same name everywhere is easier. Such as "tblData"

I need to do some other things right now. (Work!) Will get back when I can.

And I've likely posted on this in the past, maybe a search of old forum posts would find something?

regards, Fazza

 

[Fazza]

PS

As an alternative to the dynamic named ranges, maybe the source data files could have some VBA that runs on file closure to create the defined name. Such as,

wksReference.range("a1").currentregion.name = "tblData"

 

[Fazza]

I'm a bit busy at the moment. Put this together after work but is now weekend time for me. regards, Fazza

Sub demo()
  
  'Sample creation of pivot table from multiple Excel files in one directory
  'Please add your own error checking
  'Assumes common worksheet name though can easily change to common named range
  'Assumes common headers in all files
  'Uses "SELECT *" in SQL for simplicity - instead of "SELECT YourField1, YourField2, etc"
  'There is sample code for the PT fields, modify to suit
  '                                                           Fazza                May '08

  Const strFILE_PATH As String = "D:\temp\"
  Const strSHEET_NAME As String = "Sheet1"

  Dim i As Long
  Dim arSQL() As String
  Dim varFileName As Variant

  Dim objPivotCache As PivotCache
  Dim objRecordSet As Object
  Dim wbkNew As Workbook

  ChDrive strFILE_PATH
  ChDir strFILE_PATH

  'Just one way to get the file names.
  varFileName = Application.GetOpenFilename( _
      FileFilter:="Source Data File (*.xls), *.xls", _
      FilterIndex:=1, _
      Title:="Please select the source data file/s and then press ""Open""", _
      MultiSelect:=True)

  If VarType(varFileName) = vbBoolean Then
    MsgBox Prompt:="No file/s selected,", Buttons:=vbExclamation, Title:="Operation cancelled"
    Exit Sub
  End If

  ReDim arSQL(1 To UBound(varFileName))
  For i = LBound(varFileName) To UBound(varFileName)
    arSQL(i) = "SELECT * FROM `" & varFileName(i) & "`.[" & strSHEET_NAME & "$]"
  Next i

  Set objRecordSet = CreateObject("ADODB.Recordset")
  objRecordSet.Open Join$(arSQL, " UNION ALL "), "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
      ThisWorkbook.FullName & ";Extended Properties=""Excel 8.0;"""

  Set wbkNew = Workbooks.Add(Template:=xlWBATWorksheet)
  With wbkNew
    Set objPivotCache = .PivotCaches.Add(xlExternal)
    Set objPivotCache.Recordset = objRecordSet
    Set objRecordSet = Nothing
    With .Worksheets(1)
      objPivotCache.CreatePivotTable TableDestination:=.Range("A3")
      Set objPivotCache = Nothing

'      'Sample pivot table. CHANGE TO YOUR FIELD NAMES
'      With .PivotTables(1)
'        .PivotFields("FY").Orientation = xlPageField
'        .PivotFields("Cost Code").Orientation = xlRowField
'        .PivotFields("Department").Orientation = xlColumnField
'        .PivotFields("Cost").Orientation = xlDataField
'      End With

    End With
  End With
  Set wbkNew = Nothing
End Sub

 

[Fazza]

PS
If you have different field names in each header, or fields in a different order, or don't want all fields, cover this in the SQL.

Such as (and field order doesn't matter in the source files this way),

SELECT FY, Department, `Cost Code`, Cost
FROM OneSource
UNION ALL
SELECT FinYear AS [FY], Dept AS [Department], `Cost Code`, Cost
FROM AnotherSource
etc, etc

This isn't a great example or explained too well either. Still, learning a little SQL is extremely worthwhile. Some real magic can be done.

Ciao, F

 

[Fazza]

Regarding the code I posted just above, the connection is to ThisWorkbook. It would be better to one of the source data files. So please change this line

  objRecordSet.Open Join$(arSQL, " UNION ALL "), "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
      ThisWorkbook.FullName & ";Extended Properties=""Excel 8.0;"""

to

  objRecordSet.Open Join$(arSQL, " UNION ALL "), _
      Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _
      varFileName(1), ";Extended Properties=""Excel 8.0;"""), vbNullString)

Untested. Thanks, F

 

[mhenk]

I'm running into some trouble implementing this code:

Sub MultipleSheetPTs()

Dim i As Long
Dim objPivotCache As PivotCache
Dim objRS As Object
Dim wbknew As Workbook
Dim wks As Worksheet
Dim arSQL() As String

With ActiveWorkbook
    ReDim arSQL(1 To .Worksheets.Count)
    For Each wks In .Worksheets
        i = i + 1
        arSQL(i) = "SELECT * From [" & wks.Name & "$]"
    Next wks

Set wks = Nothing
Set objRS = CreateObject("ADODB.Recordset")

objRS.Open Join$(arSQL, " UNION ALL "), Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", .FullName, ";Extended Properties=""Excel 8.0;"""), vbNullString)

End With

Set wbknew = Workbooks.Add(template:=xlWBATWorksheet)

With wbknew
    Set objPivotCache = .PivotCaches.Add(xlExternal)
    Set objPivotCache.Recordset = objRS
    Set objRS = Nothing

    With .Worksheets(1)
        objPivotCache.CreatePivotTable TableDestination:=.Range("A1")
    Set objPivotCache = Nothing
    Range("A1").Select
End With
End With
Set wbknew = Nothing
End Sub

I get an "Unspecified Error" at this line:

objRS.Open Join$(arSQL, " UNION ALL "), Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", .FullName, ";Extended Properties=""Excel 8.0;"""), vbNullString)

 

[mhenk]

Fixed that problem. Now, the code is only pulling in the first 12,218 records of each worksheet??

 

[Fazza]

Maybe instead of the [Sheet1$] references to worksheet data try using (non-dynamic) named ranges.