Creating PivotTable from a 2 million plus csv file

Hashiru

Active Member
Joined
May 29, 2011
Messages
286
Hi all I have the below code. It is using MS Access data source , I want to change it to csv file as the file will now be csv rather than MS Access. Can you help me change the line of codes to do the trick. I am expecting the underline lines of code but will appreciate any change.

Thanks in advance to all.

Sub Report() Dim WS As Woksheet
Dim WB As Workbook

Set WB = ThisWorkbook
Set WS = WB.Worksheets("Report")
Application.ScreenUpdating = False
WS.Cells.Clear
Workbooks(ThisWorkbook.Name).Connections.Add2 _
"DbFile", "", Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=C:\Users\C033732\Desktop\Report Templates\Dennis Report" _
, _
"DbFile.accdb;Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path=" _
, _
""""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;" _
, _
"Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt" _
, _
" Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;" _
, _
"Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Byp" _
, "*** ChoiceField Validation=False"), "SourceTable", 3

Dim PC As PivotCache
Dim PT As PivotTable
Dim PTn As PivotTable
WS.Activate
'Delete Prior PivotTable
Set PC = WB.PivotCaches.Create(SourceType:=xlExternal, SourceData:=WB.Connections("DbFile"), Version:=6)
Set PT = PC.CreatePivotTable(TableDestination:="Report!R3C1", TableName:="xLabor", DefaultVersion:=6)

'For Each PT In WSD.PivotTables
'PT.TableRange2.Clear
'Next PT
'WB.PivotCaches.Create(SourceType:=xlExternal, SourceData:=WB.Connections("DbFile"), Version:=6). _
CreatePivotTable TableDestination:="Report!R3C1", TableName:="Labor", DefaultVersion:=6
'Create the PivotTable and Pivot the Data

'1. xLabor PivotTable
PT.AddFields RowFields:="FAIN", ColumnFields:="Type", PageFields:="System Source"
PT.AddDataField Field:=PT.PivotFields("RMB Amount"), Function:=xlSum

With WS.PivotTables("xLabor").PivotFields("FAIN")
.Orientation = xlRowField
.Position = 1
End With
With WS.PivotTables("xLabor").PivotFields("Package")
.Orientation = xlRowField
.Position = 2
End With
WS.PivotTables("xLabor").PivotFields("Sum of RMB Amount").Caption = "RMB Amount ($'000)"
With WS.PivotTables("xLabor").PivotFields("System Source")
.Orientation = xlPageField
.Position = 1
End With
With WS.PivotTables("xLabor").PivotFields("Activity")
.Orientation = xlPageField
.Position = 1
End With

'Filter the PageField
With WS.PivotTables("xLabor")
.PivotCache.MissingItemsLimit = xlMissingItemsNone
'.PivotCache.Refresh
With .PivotFields("System Source")
.PivotItems(1).Visible = True
For i = 2 To .PivotItems.Count
.PivotItems(i).Visible = False
Next
End With

With .PivotFields("System Source")
.PivotItems("BAP").Visible = True
.PivotItems("BGL").Visible = True
.PivotItems("BTL").Visible = True
.PivotItems(1).Visible = False
End With
End With
'Filter ColumnField
With WS.PivotTables("xLabor")
.PivotCache.MissingItemsLimit = xlMissingItemsNone
'.PivotCache.Refresh
With .PivotFields("Type")
.PivotItems(1).Visible = True
For i = 2 To .PivotItems.Count
.PivotItems(i).Visible = False
Next
End With

With .PivotFields("Type")
.PivotItems("INP").Visible = True
.PivotItems(1).Visible = False
End With
End With
'Filter the RowField
With WS.PivotTables("Labor").PivotFields("FAIN")
.PivotItems("1").Visible = False
.PivotItems("2").Visible = False
.PivotItems("3").Visible = False
.PivotItems("4").Visible = False
.PivotItems("5").Visible = False
.PivotItems("6").Visible = False
.PivotItems("7").Visible = False
.PivotItems("8").Visible = False
.PivotItems("9").Visible = False
.PivotItems("10").Visible = False
.PivotItems("11").Visible = False
.PivotItems("12").Visible = False
.PivotItems("13").Visible = False
.PivotItems("14").Visible = False
.PivotItems("15").Visible = False
.PivotItems("16)").Visible = False
.PivotItems("17").Visible = False
End With
'Change PivotTable to Classic Style
With WS.PivotTables("xLabor")
.InGridDropZones = True
.RowAxisLayout xlTabularRow
End With
'Format the PivotTable
WS.PivotTables("xLabor").PivotFields("FAIN").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
Application.PivotTableSelection = False
WS.PivotTables("xLabor").PivotSelect "", xlDataOnly, True
Selection.NumberFormat = "#,##0_ );(#,##0)"

'WS.PivotTables("Labor").PivotFields("[SourceTable].[System Source].[System Source]").VisibleItemsList = Array("[SourceTable].[System Source].&[BAP]", "[SourceTable].[System Source].&[BGL]", "[SourceTable].[System Source].&[BTL]")

'2. Labor PivotTable
Set PTn = PC.CreatePivotTable(TableDestination:="Report!R20C1", TableName:="Labor", DefaultVersion:=6)
PTn.AddFields RowFields:="FAIN", ColumnFields:="Type", PageFields:="System Source"
PTn.AddDataField Field:=PT.PivotFields("RMB Amount"), Function:=xlSum

With WS.PivotTables("Labor").PivotFields("Activity")
.Orientation = xlPageField
.Position = 1
End With
'Filter the PageField
With WS.PivotTables("Labor")
.PivotCache.MissingItemsLimit = xlMissingItemsNone
With .PivotFields("System Source")
.PivotItems("ACR").Visible = False
.PivotItems("BPO").Visible = False
End With
End With
With WS.PivotTables("Labor")
.PivotCache.MissingItemsLimit = xlMissingItemsNone
With .PivotFields("Activity")
.PivotItems(1).Visible = True
For i = 2 To .PivotItems.Count
.PivotItems(i).Visible = False
Next
End With

With .PivotFields("Activity")
.PivotItems("_ES").Visible = True
.PivotItems("_FA").Visible = True
.PivotItems("_IE").Visible = True
.PivotItems("_OH").Visible = True
.PivotItems("_PM").Visible = True
.PivotItems("_SUPPORT").Visible = True
.PivotItems(1).Visible = False
End With
End With
'Filter the RowField
With WS.PivotTables("Labor").PivotFields("FAIN")
.PivotItems("1").Visible = False
.PivotItems("2").Visible = False
.PivotItems("3").Visible = False
.PivotItems("4").Visible = False
.PivotItems("5").Visible = False
.PivotItems("6").Visible = False
.PivotItems("7").Visible = False
.PivotItems("8").Visible = False
.PivotItems("9").Visible = False
.PivotItems("10").Visible = False
.PivotItems("11").Visible = False
.PivotItems("12").Visible = False
.PivotItems("13").Visible = False
.PivotItems("14").Visible = False
.PivotItems("15").Visible = False
.PivotItems("16)").Visible = False
.PivotItems("17").Visible = False
End With
WS.PivotTables("Labor").PivotFields("Sum of RMB Amount").Caption = "RMB Amount ($'000)"
'Filter ColumnField
With WS.PivotTables("Labor")
.PivotCache.MissingItemsLimit = xlMissingItemsNone
With .PivotFields("Type")
.PivotItems("BLD").Visible = False
.PivotItems("INP").Visible = False
End With
End With
'Format the PivotTable
WS.PivotTables("Labor").PivotFields("FAIN").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
Application.PivotTableSelection = False
WS.PivotTables("Labor").PivotSelect "", xlDataOnly, True
Selection.NumberFormat = "#,##0_ );(#,##0)"


'3. Non-Labor PivotTable
Set PTn = PC.CreatePivotTable(TableDestination:="Report!R20C6", TableName:="Non-Labor", DefaultVersion:=6)
PTn.AddFields RowFields:="FAIN", ColumnFields:="Type", PageFields:="System Source"
PTn.AddDataField Field:=PT.PivotFields("RMB Amount"), Function:=xlSum

With WS.PivotTables("Non-Labor").PivotFields("Activity")
.Orientation = xlPageField
.Position = 1
End With
'Filter the PageField
With WS.PivotTables("Non-Labor")
.PivotCache.MissingItemsLimit = xlMissingItemsNone
With .PivotFields("System Source")
.PivotItems("ACR").Visible = False
.PivotItems("BPO").Visible = False
End With
End With
With WS.PivotTables("Non-Labor")
.PivotCache.MissingItemsLimit = xlMissingItemsNone
With .PivotFields("Activity")
.PivotItems("_ES").Visible = False
.PivotItems("_FA").Visible = False
.PivotItems("_IE").Visible = False
.PivotItems("_OH").Visible = False
.PivotItems("_PM").Visible = False
.PivotItems("_SUPPORT").Visible = False
End With
End With
'Filter the RowField
With WS.PivotTables("Labor").PivotFields("FAIN")
.PivotItems("1").Visible = False
.PivotItems("2").Visible = False
.PivotItems("3").Visible = False
.PivotItems("4").Visible = False
.PivotItems("5").Visible = False
.PivotItems("6").Visible = False
.PivotItems("7").Visible = False
.PivotItems("8").Visible = False
.PivotItems("9").Visible = False
.PivotItems("10").Visible = False
.PivotItems("11").Visible = False
.PivotItems("12").Visible = False
.PivotItems("13").Visible = False
.PivotItems("14").Visible = False
.PivotItems("15").Visible = False
.PivotItems("16)").Visible = False
.PivotItems("17").Visible = False
End With

'Filter ColumnField
With WS.PivotTables("Non-Labor")
.PivotCache.MissingItemsLimit = xlMissingItemsNone
With .PivotFields("Type")
.PivotItems("BLD").Visible = False
.PivotItems("INP").Visible = False
End With
End With
WS.PivotTables("Non-Labor").PivotFields("Sum of RMB Amount").Caption = "RMB Amount ($'000)"
'Format the PivotTable
WS.PivotTables("Non-Labor").PivotFields("FAIN").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
Application.PivotTableSelection = False
WS.PivotTables("Non-Labor").PivotSelect "", xlDataOnly, True
Selection.NumberFormat = "#,##0_ );(#,##0)"

'4. Last Week's Expenses Only
Set PTn = PC.CreatePivotTable(TableDestination:="Report!R20C11", TableName:="LastWeekExpenses", DefaultVersion:=6)
PTn.AddFields RowFields:="FAIN", ColumnFields:="Type", PageFields:="System Source"
PTn.AddDataField Field:=PT.PivotFields("RMB Amount"), Function:=xlSum

With WS.PivotTables("LastWeekExpenses").PivotFields("Activity")
.Orientation = xlPageField
.Position = 1
End With
With WS.PivotTables("LastWeekExpenses").PivotFields("Accounting")
.Orientation = xlPageField
.Position = 1
End With
'Filter the PageField
With WS.PivotTables("LastWeekExpenses")
.PivotCache.MissingItemsLimit = xlMissingItemsNone
With .PivotFields("System Source")
.PivotItems("ACR").Visible = False
.PivotItems("BPO").Visible = False
End With
End With
With WS.PivotTables("LastWeekExpenses")
.PivotCache.MissingItemsLimit = xlMissingItemsNone
With .PivotFields("Accounting")
.PivotItems(1).Visible = True
For i = 2 To .PivotItems.Count
.PivotItems(i).Visible = False
Next
End With
Dim DateF As Worksheet
Set DateF = Worksheets("Date Filters")
With .PivotFields("Accounting")
FilteredDates = DateF.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To FilteredDates
cu = DateF.Cells(i, 1)
.PivotItems(cu).Visible = True
Next
.PivotItems(1).Visible = False
End With
End With
'Filter the RowField
With WS.PivotTables("Labor").PivotFields("FAIN")
.PivotItems("1").Visible = False
.PivotItems("2").Visible = False
.PivotItems("3").Visible = False
.PivotItems("4").Visible = False
.PivotItems("5").Visible = False
.PivotItems("6").Visible = False
.PivotItems("7").Visible = False
.PivotItems("8").Visible = False
.PivotItems("9").Visible = False
.PivotItems("10").Visible = False
.PivotItems("11").Visible = False
.PivotItems("12").Visible = False
.PivotItems("13").Visible = False
.PivotItems("14").Visible = False
.PivotItems("15").Visible = False
.PivotItems("16)").Visible = False
.PivotItems("17").Visible = False
End With

'Filter ColumnField
With WS.PivotTables("LastWeekExpenses")
.PivotCache.MissingItemsLimit = xlMissingItemsNone
With .PivotFields("Type")
.PivotItems("BLD").Visible = False
.PivotItems("INP").Visible = False
End With
End With
WS.PivotTables("LastWeekExpenses").PivotFields("Sum of RMB Amount").Caption = "RMB Amount ($'000)"
'Format the PivotTable
WS.PivotTables("LastWeekExpenses").PivotFields("FAIN").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
Application.PivotTableSelection = False
WS.PivotTables("LastWeekExpenses").PivotSelect "", xlDataOnly, True
Selection.NumberFormat = "#,##0_ );(#,##0)"

'5. Last PivotTable
Set PTn = PC.CreatePivotTable(TableDestination:="Report!R60C8", TableName:="LastPivotTable", DefaultVersion:=6)
PTn.AddFields RowFields:="Accounting", ColumnFields:="Type", PageFields:="System Source"
PTn.AddDataField Field:=PT.PivotFields("RMB Amount"), Function:=xlSum

With WS.PivotTables("LastPivotTable").PivotFields("Activity")
.Orientation = xlPageField
.Position = 1
End With
With WS.PivotTables("LastPivotTable").PivotFields("FAIN")
.Orientation = xlPageField
.Position = 1
End With
'Filter the PageField
With WS.PivotTables("LastPivotTable")
.PivotCache.MissingItemsLimit = xlMissingItemsNone
With .PivotFields("System Source")
.PivotItems("ACR").Visible = False
.PivotItems("BPO").Visible = False
End With
End With
With WS.PivotTables("LastPivotTable")
.PivotCache.MissingItemsLimit = xlMissingItemsNone
With .PivotFields("Accounting")
.PivotItems(1).Visible = True
For i = 2 To .PivotItems.Count
.PivotItems(i).Visible = False
Next
End With
With .PivotFields("Accounting")
FDates = DateF.Cells(Rows.Count, 2).End(xlUp).Row
For i = 2 To FDates
cu = DateF.Cells(i, 2)
On Error Resume Next
.PivotItems(cu).Visible = True
Next
.PivotItems(1).Visible = False
End With
End With
'Filter the RowField
With WS.PivotTables("Labor").PivotFields("FAIN")
.PivotItems("1").Visible = False
.PivotItems("2").Visible = False
.PivotItems("3").Visible = False
.PivotItems("4").Visible = False
.PivotItems("5").Visible = False
.PivotItems("6").Visible = False
.PivotItems("7").Visible = False
.PivotItems("8").Visible = False
.PivotItems("9").Visible = False
.PivotItems("10").Visible = False
.PivotItems("11").Visible = False
.PivotItems("12").Visible = False
.PivotItems("13").Visible = False
.PivotItems("14").Visible = False
.PivotItems("15").Visible = False
.PivotItems("16)").Visible = False
.PivotItems("17").Visible = False
End With
WS.PivotTables("LastPivotTable").PivotFields("Sum of RMB Amount").Caption = "RMB Amount ($'000)"
'Format the PivotTable
WS.PivotTables("LastPivotTable").PivotFields("FAIN").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
Application.PivotTableSelection = False
WS.PivotTables("LastPivotTable").PivotSelect "", xlDataOnly, True
Selection.NumberFormat = "#,##0_ );(#,##0)"
Application.ScreenUpdating = True
WS.Columns("A:Z").EntireColumn.AutoFit
End
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top