Build Pivot on CSV using VBA (1 Viewer)

winshent

Registered User.
Local time
Today, 14:14
Joined
Mar 3, 2008
Messages
162
Hi

I have built an MS Access front end which can export filtered data to CSV's from a database. The users then want to pivot this data using Excel..

We are still using Office 2003 here, and typical export will be more than 65k rows..

I now want to automate the next step of creating a new workbook file and building a pivot table within this file that connects to the CSV data..

I am able to write code that creates an excel file, and also build a pivot on data within the workbook... However, I have not been able to find on the forums how to connect a pivot to an external datasource using VBA..

Any ideas?

I have also posted on Excel Forum.. please post on there if possible to prevent double posting

http://www.excelforum.com/excel-pro...9-build-pivot-on-csv-using-vba.html?p=3304958
 

noboffinme

Registered User.
Local time
Tomorrow, 00:14
Joined
Nov 28, 2007
Messages
288
Hi winshent,

I'm not registered on that forum so my answer is going here.

I haven't heard or thought of keeping the Pivot Table source data in Access, so my only suggestion would be - Have you considered transferring the Pivot source data to a worksheet in the Excel worksheet & have this as the Pivot table source?

Could you post the code you've got so far so I can see where you're up to?
 

winshent

Registered User.
Local time
Today, 14:14
Joined
Mar 3, 2008
Messages
162
Hi winshent,

I'm not registered on that forum so my answer is going here.

I haven't heard or thought of keeping the Pivot Table source data in Access, so my only suggestion would be - Have you considered transferring the Pivot source data to a worksheet in the Excel worksheet & have this as the Pivot table source?

Could you post the code you've got so far so I can see where you're up to?

Hi noboffinme

I can't import the table source in to Excel as the number of records will typically exceed 65k and we are still using Excel 2003.. Once I come up with a solution I'll post on here..
 

noboffinme

Registered User.
Local time
Tomorrow, 00:14
Joined
Nov 28, 2007
Messages
288
Hi winshent,

Yes, sorry you did say you were using 2003 & had that limitation.

I tried to replicate what you're doing & had some success.

Please note that I'm using Excel & Access 2010 for this so I hope you can maybe replicate what you need in your version.

I think your problem is writing the connections between Excel & Access into your VBA in order to use your Pivot?

I managed to connect to an Access db from Excel with no data in my Excel sheet. I recorded the code that did this with the macro recorder.

First up I added the Access db ('AWF_TEST_DB')to my computers ODBC connections so that the database appears in the list of data sources when I select the Pivot tables data source.

So from the beginning, I open an Excel worksheet >> Insert Pivot table >> Use an external Data Source >> connect to your new ODBC data source database ('AWF_TEST_DB') & then follow the usual steps to create the Pivot table by dragging the fields into the relevant Pivot areas.

Here's my recorded code in case that helps you find the missing code for the connection, hth

I created a table in my Access db ('AWF_TEST_DB') called 'RAW DATA' with the Fields 'Product', 'Area' & 'Sales' for this exercise.

Code:
Sub Macro1()
'
' Macro1 Macro
'

'
    Range("A1").Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
        ActiveWorkbook.Connections("AWF_TEST_DB.accdb RAW DATA"), Version:= _
        xlPivotTableVersion14).CreatePivotTable TableDestination:="RAW DATA!R1C1", _
        TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion14
    Cells(1, 1).Select
    ActiveWorkbook.ShowPivotTableFieldList = True
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Area")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Product")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Sales"), "Sum of Sales", xlSum
    ActiveWorkbook.ShowPivotTableFieldList = False
End Sub
 

winshent

Registered User.
Local time
Today, 14:14
Joined
Mar 3, 2008
Messages
162
Thanks for looking in to this noboffinme

I have similar code to yours within my Access app.. I am trying to avoid creating an ODBC connection for each export..

I've now managed to get this working with help from a guy on the other forum..

Here is the solution..

Code:
'********************************************************
' FUNCTION:     fnBuildPivot()
'
' PURPOSE:      Builds an Excel file then adds a pivot and connects to
'               the CSV file OR Access database file parsed in. The xls
'               file is saved in the same directory as the data file.
'
' ARGUMENTS:
'   sourceDir:  The directory of the source CSV file which
'               the pivot will be built on
'
'   sourceFile: File name of the CSV.
'
' RETURNS:
'   String:     Path of Pivot File.
'
'********************************************************
Public Function fnBuildPivot(sourceDir As String, sourceFile As String, FileType As ExportFileType) As String
 
  On Error GoTo PROC_ERR
  Dim xl As Excel.Application, wb As Excel.Workbook, ws As Excel.Worksheet, pc As Excel.PivotCache, FileNameLessXtn As String
  Dim sFile As String, sDir As String, cmdText As String
 
  Select Case FileType
    Case CSV
      FileNameLessXtn = Left(sourceFile, InStrRev(sourceFile, ".csv") - 1)
    Case Access2003
      FileNameLessXtn = Left(sourceFile, InStrRev(sourceFile, ".mdb") - 1)
    '  Case Access2007
    '    FileNameLessXtn = Left(sourceFile, InStrRev(sourceFile, ".accdb") - 1)
  End Select
 
  Set xl = New Excel.Application
  Set wb = xl.Workbooks.Add
  wb.SaveAs sourceDir & "\" & FileNameLessXtn & "_pivot.xls", xlWorkbookNormal
  Set ws = wb.Worksheets("Sheet1")
 
  ws.Name = "Pivot"
  wb.Worksheets("Sheet2").Delete
  wb.Worksheets("Sheet3").Delete
 
  ws.Activate
 
  Set pc = wb.PivotCaches.Add(SourceType:=xlExternal)
  With pc
      Select Case FileType
        Case CSV
          .Connection = Array(Array("ODBC;DBQ=" & sourceDir & ";"), Array("Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId=27;Extensions=txt,csv,tab,asc;FIL=text;MaxBufferSize=2048;MaxScanRows=25;PageTimeout=50;SafeTransactions=0;Threads=3;UserCommitSync=Yes;"))
          .CommandType = xlCmdSql
          cmdText = "SELECT * FROM [" & sourceFile & "]"
 
        Case Access2003
          '  .Connection = "ODBC;DSN=MS Access Database;DBQ=C:\Temp\VP_Work\ERA\ExportData.mdb;DefaultDir=C:\Temp\VP_Work\ERA;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
          '  .CommandType = xlCmdSql
          '  .CommandText = Array( _
          '  "SELECT Data.YearMonth, Data.IssCountry, Data.FeeDesc, Data.FeeTier, Data.Member, Data.MerchTier, Data.Product, Data.Programme, Data.TC, Data.BID, Data.BIN, Data.Count, Data.Amount, Data.CashBack, Data" _
          '  , _
          '  ".NetCount, Data.NetAmount, Data.IRF_Product, Data.IRF_Programme2, Data.IRF_FEE, Data.FeeText, Data.NetFeeText, Data.FeeNum, Data.NetFee" & Chr(13) & "" & Chr(10) & "FROM `C:\Temp\VP_Work\ERA\ExportData`.Data Data" _
          '  )
          '  .CreatePivotTable TableDestination:="[RecordCode.xls]Sheet1!R6C2", _
          '  Tablename:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
 
        .Connection = "ODBC;DSN=MS Access Database;DBQ=" & sourceDir & "\" & sourceFile & ";DefaultDir=" & sourceDir & ";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
        .CommandType = xlCmdSql
        ' cmdText = "SELECT Data.YearMonth, Data.IssCountry, Data.FeeDesc, Data.FeeTier, Data.Member, Data.MerchTier, Data.Product, Data.Programme, Data.TC, Data.BID, Data.BIN, Data.Count, Data.Amount, Data.CashBack, Data.NetCount, Data.NetAmount, Data.IRF_Product, Data.IRF_Programme2, Data.IRF_FEE, Data.FeeText, Data.NetFeeText, Data.FeeNum, Data.NetFee FROM Data"
        cmdText = "SELECT * FROM Data"
      End Select
 
      .CommandText = cmdText
      .CreatePivotTable TableDestination:=ws.Name & "!r10c2", Tablename:="Pivot_1", DefaultVersion:=xlPivotTableVersion10
 
  End With
 
  fnAddFieldsToPivot "Pivot_1", ws
 
  wb.Save
  'wb.Close
 
  ' xl.Visible = True
 
  fnBuildPivot = sourceDir & "\" & FileNameLessXtn & "_pivot.xls"
 
PROC_EXIT:
  On Error Resume Next
 
  Set ws = Nothing
  wb.Close
  Set wb = Nothing
  xl.Quit
  Set xl = Nothing
 
  Exit Function
 
PROC_ERR:
  Debug.Print Err.Description
  MsgBox Err.Description
  Resume PROC_EXIT
  Resume
 
End Function
 
 
 
Private Function fnAddFieldsToPivot(PivotTableName As String, ws As Worksheet)
  On Error GoTo PROC_ERR
  If Not fnIsLoaded("frmGrouping") Then Exit Function
  With ws.PivotTables(PivotTableName)
 
    If Forms!frmGrouping!chkNetCount Then
      ' .AddDataField ActiveSheet.PivotTables(PivotTableName).PivotFields("SumOfNetCount"), "Sum of SumOfNetCount", xlSum
      .AddDataField ws.PivotTables(PivotTableName).PivotFields("SumOfNetCount"), "Sum of SumOfNetCount", xlSum
    End If
 
    If Forms!frmGrouping!chkNetAmount Then
      ' .AddDataField ActiveSheet.PivotTables(PivotTableName).PivotFields("SumOfNetAmount"), "Sum of SumOfNetAmount", xlSum
      .AddDataField ws.PivotTables(PivotTableName).PivotFields("SumOfNetAmount"), "Sum of SumOfNetAmount", xlSum
    End If
 
    If Forms!frmGrouping!chkNetAmount Then
      ' .AddDataField ActiveSheet.PivotTables(PivotTableName).PivotFields("SumOfNetFee"), "Sum of SumOfNetFee", xlSum
      .AddDataField ws.PivotTables(PivotTableName).PivotFields("SumOfNetFee"), "Sum of SumOfNetFee", xlSum
    End If
 
    With .DataPivotField
        .Orientation = xlColumnField
        .Position = 1
    End With
 
    If Forms!frmGrouping!chkCountry Then
      With .PivotFields("Country")
        .Orientation = xlRowField
        .Position = 1
      End With
    End If
 
    If Forms!frmGrouping!chkYearMonth Then
      With .PivotFields("YearMonth")
          .Orientation = xlRowField
          .Position = 2
      End With
    End If
 
  End With 'ws.PivotTables(PivotTableName)
 
PROC_EXIT:
  On Error Resume Next
  Exit Function
 
PROC_ERR:
  Debug.Print Err.Description
  MsgBox Err.Description
  Resume PROC_EXIT
End Function
 
Last edited:

Users who are viewing this thread

Top Bottom