Creating PivotTable from a 2 million plus csv file

H

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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
You must log in or register to reply here.

Similar threads

M
  • Question Question
Faster vba code for adding a total column, creating pivot and then copying pasting data
Replies
0
Views
581
Monicasinha
M
C
  • Question Question
VBA to keep the pivot tables from updating its filters in tandem
Replies
1
Views
690
Alex Blakenburg
A
H
  • Question Question
PivotTable Fields not Showing
Replies
0
Views
517
Hashiru
H
Z
  • Question Question
[VBA] Excel PivotTable Filtering
Replies
5
Views
1K
DanteAmor
DanteAmor
H
  • Question Question
Filter OR HIDE PIVOT ITEMS IN CUBEFIELD
Replies
0
Views
2K
Hashiru
H

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

Share this page

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