Error while creating pivot with csv file through access

Hi All,

I've posted my issue that I am facing in another forum also. Since I am in urgent need I need your help.
http://www.access-programmers.co.uk/...89#post1504689

Any help appreciated.

I am posting it here also.

Thanks in advance
aganesan99
-----------------------------------------------------------------------------------------
Hi All,

I am using Excel 2010 and access 2010 versions.

I am trying to create a pivot based on a ".csv" file. With the help of previous posts in this forum, I have written the below codes. But I am getting runtime error 5 "Invalid procedure call or argument" in the below code.

.CreatePivotTable tabledestination:=sFile & "Pivot!R6C1", _
        Tablename:="Pivot1" ', DefaultVersion:=xlPivotTableVersion14

Below is the entire code. Please help.

Sub SubcoPivot()

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

Set xl = CreateObject("Excel.Application")
xl.Visible = True
Set wb = xl.Workbooks.Open(FileName:=CurrentProject.Path & "\Output Files\Subco_Pivot.xlsb", ReadOnly:=False)

Set ws = wb.Worksheets("Pivot")
sDir = CurrentProject.Path & "\Output Files\"
sFile = "Subco_Pivot.xlsb"
wb.Application.ActiveSheet.Name = "Pivot1"
wb.Application.Worksheets.Add
wb.Application.ActiveSheet.Name = "Pivot"
wb.Application.Worksheets("Pivot1").Delete
wb.Application.Range("A6").Select

'Set pc = wb.PivotCaches.Add(SourceType:=xlExternal)
    wb.Application.ActiveWorkbook.Connections.Add "Query from Subco Data", "", "ODBC;DBQ=" & sDir & ";" & _
        "DefaultDir=" & sDir & ";Driver={Microsoft Access Text Driver (*.txt, *.csv)};" & _
        "DriverId=27;Extensions=txt,csv,tab,asc;FIL=text;MaxBufferSize=2048;" & _
        "MaxScanRows=25;PageTimeout=5;SafeTransactions=true;Threads=3;UID=admin;UserCommitSync=No;" & _
        "SELECT `Subco Data`.Account, `Subco Data`.`IRIS Code`, `Subco Data`.`Profit Center`, `Subco Data`.Period, " & _
        "`Subco Data`.DocumentNo, `Subco Data`.RefDocNo, `Subco Data`.`Cost Ctr`, `Subco Data`.`WBS Element`, " & _
        "`Subco Data`.AccText, `Subco Data`.PurchDoc, `Subco Data`.Year, `Subco Data`.Vendor, `Subco Data`.Row, " & _
        "`Subco Data`.Text, `Subco Data`.TrPrt, `Subco Data`.`Direct/Indirect`, `Subco Data`.`In co code currency`, " & _
        "`Subco Data`.Customer, `Subco Data`.`Vendor Name`, `Subco Data`.`Account Group`, `Subco Data`.ServiceLine" & _
         Chr(13) & "" & Chr(10) & "FROM `Subco Data.csv` `Subco Data`", 2
    With wb.Application.ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, Version:=xlPivotTableVersion14)
        .Connection = "ODBC;DBQ=" & sDir & ";DefaultDir=" & sDir & ";Driver={Microsoft Access Text Driver (*.txt, *.csv)};" & _
        "DriverId=27;Extensions=txt,csv,tab,asc;FIL=text;MaxBufferSize=2048;" & _
        "MaxScanRows=25;PageTimeout=5;SafeTransactions=True;Threads=3;UID=admin;UserCommitSync=No;"
        .CommandType = xlCmdSql
        .CommandText = "SELECT `Subco Data`.Account, `Subco Data`.`IRIS Code`, `Subco Data`.`Profit Center`, `Subco Data`.Period, " & _
        "`Subco Data`.DocumentNo, `Subco Data`.RefDocNo, `Subco Data`.`Cost Ctr`, `Subco Data`.`WBS Element`, `Subco Data`.AccText, " & _
        "`Subco Data`.PurchDoc, `Subco Data`.Year, `Subco Data`.Vendor, `Subco Data`.Row, `Subco Data`.Text, `Subco Data`.TrPrt, " & _
        "`Subco Data`.`Direct/Indirect`, `Subco Data`.`In co code currency`, `Subco Data`.Customer, `Subco Data`.`Vendor Name`, " & _
        "`Subco Data`.`Account Group`, `Subco Data`.ServiceLine" & Chr(13) & "" & Chr(10) & "FROM `Subco Data.csv` `Subco Data`"
        .CreatePivotTable tabledestination:=sFile & "Pivot!R6C1", _
        Tablename:="Pivot1" ', DefaultVersion:=xlPivotTableVersion14
        '.CreatePivotTable tabledestination:="[sFile]" & wb.Application.ActiveSheet.Name & "A6", Tablename:="PivotT1", defaultversion:=xlPivotTableVersion14
    End With
    With wb.Application.ActiveSheet.PivotTables("PivotTable1").PivotFields("Account Group")
        .Orientation = xlRowField
        .Position = 1
    End With
    wb.Application.ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("In co code currency"), _
        "Count of In co code currency", xlCount
    With wb.Application.ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "Count of In co code currency")
        .Caption = "Sum of In co code currency"
        .Function = xlSum
    End With
    With wb.Application.ActiveSheet.PivotTables("PivotTable1").PivotFields("Period")
        .Orientation = xlColumnField
        .Position = 1
    End With

wb.Close (True)
 
  Set ws = Nothing
  Set wb = Nothing
  xl.Quit
  Set xl = Nothing

End Sub