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)
    Else
    '>= Excel 2007 Use
        Set pvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal)
    End If
 
    Set pvtCache.Recordset = rstADO
    Set pvtTable = pvtCache.CreatePivotTable(TableDestination:=rngPvtTarget)
 
    conADO.Close
 
    On Error GoTo 0: Err.Clear: On Error GoTo -1
    EE_CreatePivotFromCSV = True
    GoTo ExitFunc
ErrPivot:
    EE_CreatePivotFromCSV = False
ExitFunc:
    Set rstADO = Nothing
    Set conADO = Nothing
    Set pvtCache = Nothing
    Set pvtTable = Nothing
    Set rngPvtTarget = Nothing
 
End Function