XLA routines: EE_CreatePivotFromCSV

Nick's picture
EE_CreatePivotFromCSV creates a pivot without opening the csv - used for massive files that don't open in Excel
Function EE_CreatePivotFromCSV(ByVal strFullCSVFilePath As String, wksPivotSheet As Worksheet) As Boolean
'- takes a CSV file path
'- takes Destination sheet name
'- creates pivot into new sheet
'- returns true if success

    Dim conADO        As Object
    Dim rstADO        As Object
    Dim pvtCache      As PivotCache
    Dim pvtTable      As PivotTable
    Dim rngPvtTarget  As range
    Dim strSQL        As String
    Dim strFileName   As String
    Dim strFilePath   As String
'http://excelexperts.com/xla-routines-eeCreatePivotFromCSV    for updates on this function

    On Error GoTo ErrPivot
    Set rngPvtTarget = wksPivotSheet.range("A1")
    strFilePath = Left(strFullCSVFilePath, InStrRev(strFullCSVFilePath, "\"))
    strFileName = Replace(strFullCSVFilePath, strFilePath, "")
    Set conADO = CreateObject("ADODB.Connection")
    conADO.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" _
                    & strFilePath _
                    & ";Extensions=asc,csv,tab,txt;Persist Security Info=False"
    strSQL = "SELECT * FROM " & strFileName
    Set rstADO = CreateObject("ADODB.Connection")
    Set rstADO = conADO.Execute(strSQL)
    'Excel 2003
    If Application.Version < 12 Then
        Set pvtCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
    '>= Excel 2007 Use
        Set pvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal)
    End If
    Set pvtCache.Recordset = rstADO
    Set pvtTable = pvtCache.CreatePivotTable(TableDestination:=rngPvtTarget)
    On Error GoTo 0: Err.Clear: On Error GoTo -1
    EE_CreatePivotFromCSV = True
    GoTo ExitFunc
    EE_CreatePivotFromCSV = False
    Set rstADO = Nothing
    Set conADO = Nothing
    Set pvtCache = Nothing
    Set pvtTable = Nothing
    Set rngPvtTarget = Nothing
End Function