• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot table from multiple workbooks [SOLVED]

jassybun

Member
Hey guys! I have been looking at Kirill's workbook for using multiple workbooks in a pivot table...I am wondering if you guys know if it can handle my situation. In Kirill's file http://www.pivot-table.com/2010/08/30/pivot-table-from-data-in-multiple-workbooks/, the workbooks all have their data in "Sheet1"...however my data is set up a bit different.


I have a file for each week: "06292013" (there will be a new one for every week) ..within this file there are 5 workbooks...one for each day of the week...Monday, Tuesday, Wednesday, Thursday, Friday...in each of these days the first 3 tabs are BV, LV and GL. Each of these sheets have the same top row headers. In the end I need a separate workbook (similar to Kirills "Report" workbook) that has the data from the whole week for each area, BV, LV and GL that I can manipulate in a pivot table. There are additional worksheets in the workbook that are not going to be used, only the BV, LV and GL sheets need to be included.


Can anyone help me with adjusting this workbook for my situation.....I am not so great with arrays....


Thanks!

Jassybun
 
So you need to do a master pivot that contains data from multiple sheets from multiple workbooks?


If so,that's a heck of a lot of programming, and quite some modification from Kirill's file. For starters, I think you would have to pull all the different workbooks into one, because I believe SQL can only operate on one workbook at a time i.e. you can't select something from one workbook and something else from another using SQL within Excel. Although you could do it in batches.


So unless you do that, maybe that rules Kirill's approach out.


That said, maybe it would be simpler to simply copy each table and stick it into a master data table in your summary book, and create a pivot out of that master table.


I'm not volunteering, mind. This might be such a large job that you have to hire a pro.
 
Perhaps I am not explaining it right....Kirill's file already is set to pull data from several workbooks, however many you need. That is not the issue - it is just that his file pulls up "Sheet1" of every workbook you select. I know I can probably change it from "sheet1" to "BV", and gain access too all workbooks with just the BV sheet, but I wanted to grab the other two (LV and GL) as well....


I found this on how to change the names of each workbook:

http://www.contextures.com/images/pivot/PivotMulti02.png
 
Okay, I get it now. Replace Kirill's MergeFiles sub with this, and see if this does the trick. (It does on my sample dataset)

[pre]
Code:
Sub MergeFiles()
Dim PT As PivotTable
Dim PC As PivotCache
Dim arrFiles As Variant
Dim strSheet As String
Dim strPath As String
Dim strSQL As String
Dim strCon As String
Dim rng As Range
Dim i As Long
Dim strTabs() As String
Dim lngTabs As Long
strPath = CurDir
ChDirNet ThisWorkbook.Path

arrFiles = Application.GetOpenFilename("Excel Workbooks (*.xls), *.xls", , , , True)
strTabs() = Split("BV;LV;GL", ";")

If Not IsArray(arrFiles) Then Exit Sub

Application.ScreenUpdating = False

If Val(Application.Version) > 11 Then DeleteConnections_12

Set rng = ThisWorkbook.Sheets(1).Cells
rng.Clear
For lngTabs = 0 To UBound(strTabs)
strSheet = strTabs(lngTabs)
For i = 1 To UBound(arrFiles)
If strSQL = "" Then
strSQL = "SELECT * FROM [" & strSheet & "$]"
Else
strSQL = strSQL & " UNION ALL SELECT * FROM <code>" & arrFiles(i) & "</code>.[" & strSheet & "$]"
End If
Next i
Next lngTabs
strCon = _
"ODBC;" & _
"DSN=Excel Files;" & _
"DBQ=" & arrFiles(1) & ";" & _
"DefaultDir=" & "" & ";" & _
"DriverId=790;" & _
"MaxBufferSize=2048;" & _
"PageTimeout=5"

Set PC = ThisWorkbook.PivotCaches.Add(SourceType:=xlExternal)

With PC
.Connection = strCon
.CommandType = xlCmdSql
.CommandText = strSQL
Set PT = .CreatePivotTable(TableDestination:=rng(6, 1))
End With

With PT
With .PivotFields(1)                            'Rep
.Orientation = xlRowField
.Position = 1
End With
.AddDataField .PivotFields(8), "Sales", xlSum   'Total
With .PivotFields(3)                            'Region
.Orientation = xlPageField
.Position = 1
End With
With .PivotFields(2)                            'Date
.Orientation = xlColumnField
.Position = 1
.DataRange.Cells(1).Group _
Start:=True, _
End:=True, _
Periods:=Array(False, False, False, False, True, False, True)
End With
End With

'Clean up
Set PT = Nothing
Set PC = Nothing

ChDirNet strPath
Application.ScreenUpdating = True
End Sub
[/pre]
 
I am getting this error:


Run-time error '1004':

{Microsoft}{ODBC Excel Driver} Characters found after end of SQL statement.


When I click on debug, it highlighted this:


Set PT = .CreatePivotTable(TableDestination:=rng(6, 1))


Here is a sample of the workbooks I am trying to combine

(there usually is a lot more data in there, but for testing purposes, I removed most of it)


https://dl.dropboxusercontent.com/u/28469410/06292013.rar
 
This works fine on my system. If you take Kirill's original sample files, create two copies of the Sheet1 tab in each of the regional workbooks and change the name of all three tabs to BV, LV, and GL respectively, and then run the code with my additions above, does it work?


Can you upload a sample files with dummy (non-confidential) info?
 
Yes - here is the file...sorry if I am not explaining my situation correctly...I make it sound more complicated then it is :)


https://dl.dropboxusercontent.com/u/28469410/06292013.rar
 
One reason is that my code didn't paste well. Scan the above code, and you'll see that the line that starts with this:

strSQL = strSQL & " UNION ALL SELECT * FROM


...has some the word Code and an ampersand and the word quot in two places.

That's because the code I posted unfortunately contains the backtick character, which this blog interpets to mean "there is some code here, put it in a code block"


Replace that line with strSQL = strSQL & " UNION ALL SELECT * FROM !" & arrFiles(i) & "!.[ & strSheet & $]"


...and replace the ! with a backtick character (see the 'Allowed Markup' section below to visually see what the backtick character looks like)


Refer to the same line in Kirill's original code to see what I mean.
 
Also note that your source files are in a bit of a mess, and you will need to tidy them up for this code to work. For instance, you will need to delete any columns to the right of column W for the BV, LV, and GL sheets, because Excel needs a nice table with column headers in each row, whereas you have some summary formulas out past column W.


ANd you might have to delete the folmulas at the bottom of the row too.


Take a close look at Kirill's file to see how the individual tables on Sheet1 are structured. This is what SQL expects.
 
They seem messy, but once they are filled out they are incredibly helpful. They are just dummy files that aren't filled out completely...I only put in enough rows to try and show what the file does. Thanks for the tip and all your help :)
 
Back
Top