Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rating: Thread Rating: 1545 votes, 5.00 average. Display Modes
Old 07-02-2013, 01:24 AM   #1
winshent
Newly Registered User
 
Join Date: Mar 2008
Location: London, UK
Posts: 162
Thanks: 35
Thanked 7 Times in 7 Posts
winshent is on a distinguished road
Build Pivot on CSV using VBA

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-prog...html?p=3304958

winshent is offline   Reply With Quote
Old 07-07-2013, 03:08 PM   #2
noboffinme
Newly Registered User
 
Join Date: Nov 2007
Location: Melbourne
Posts: 288
Thanks: 11
Thanked 11 Times in 11 Posts
noboffinme is on a distinguished road
Re: Build Pivot on CSV using VBA

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?
__________________
Change is inevitable, except from a vending machine.
noboffinme is offline   Reply With Quote
The Following User Says Thank You to noboffinme For This Useful Post:
winshent (07-17-2013)
Old 07-08-2013, 02:18 AM   #3
winshent
Newly Registered User
 
Join Date: Mar 2008
Location: London, UK
Posts: 162
Thanks: 35
Thanked 7 Times in 7 Posts
winshent is on a distinguished road
Re: Build Pivot on CSV using VBA

Quote:
Originally Posted by noboffinme View Post
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..

winshent is offline   Reply With Quote
Old 07-08-2013, 04:08 PM   #4
noboffinme
Newly Registered User
 
Join Date: Nov 2007
Location: Melbourne
Posts: 288
Thanks: 11
Thanked 11 Times in 11 Posts
noboffinme is on a distinguished road
Re: Build Pivot on CSV using VBA

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
__________________
Change is inevitable, except from a vending machine.
noboffinme is offline   Reply With Quote
The Following User Says Thank You to noboffinme For This Useful Post:
winshent (07-17-2013)
Old 07-15-2013, 01:38 AM   #5
winshent
Newly Registered User
 
Join Date: Mar 2008
Location: London, UK
Posts: 162
Thanks: 35
Thanked 7 Times in 7 Posts
winshent is on a distinguished road
Re: Build Pivot on CSV using VBA

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 by winshent; 01-16-2014 at 04:06 AM. Reason: SOLVED
winshent is offline   Reply With Quote
Reply

Tags
csv , excel , excel 2003 , external datasource

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
No Pivot Table or Pivot Chart options? stoolpigeon General 6 11-03-2013 02:25 PM
Question Sync Pivot chart/Pivot table Filter msadiqrajani General 0 10-19-2012 05:26 AM
Required Build Qty vs. Actual Build Qty - multiple tables new_2_prog Reports 0 06-10-2011 06:13 AM
Build Excel Pivot table in Access (Columns) greyhound48 Modules & VBA 5 08-11-2006 08:49 PM
create pivot chart from pivot table?? dhx10000 Tables 0 03-13-2006 04:11 PM




All times are GMT -8. The time now is 05:05 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World