none
Excel ADODB connexion - PivotTable with thisworkbook.connections collection RRS feed

  • Question

  • Hi,

    I declared in a first module 2 constants

    Public Const ADODB_PROVIDER = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;"
    Public Const PATH_DB = "E:\BkUpData\Projets\Access\GarageHellMotors\facturation_be_test.accdb

    I wrote in a second module a function which implements a connection object. This function is fired on workbook load event.

    Public Function fWkBookCnxAdd()
     
        Dim objWBConnect As WorkbookConnection
     
        Set objWBConnect = ThisWorkbook.Connections.Add( _
        Name:="tcd", Description:="", _
        ConnectionString:=ADODB_PROVIDER & _
        "Data Source=" & PATH_DB, _
        CommandText:="SELECT * FROM qryFactureSumMonthYear", _
        lCmdtype:=xlCmdSql)
    End Function

    On a sheet, I added a commandbutton  and on its click event I wrote this code

    Private Sub cmdTcd_Click() Dim oPivotCache As PivotCache Dim oWkBookCnx As WorkbookConnection Dim oPtTable As PivotTable Dim sSQL As String

    ActiveSheet.Range("A3").CurrentRegion.Clear Set oWkBookCnx = ThisWorkbook.Connections("tcd") Set oPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal, SourceData:=oWkBookCnx) ' Create a tcd. Set oPtTable = oPivotCache.CreatePivotTable( _ TableDestination:=Range("A3"), _ TableName:="tcd") ' ---> Error 1004 'Code to be continued



    Could someone explain me why this code line generates an error 1024?

    Set oPtTable = oPivotCache.CreatePivotTable( _
        TableDestination:=Range("A3"), _
        TableName:="tcd") ' ---> Error 1004
    thanks by advance




    • Edited by technet65 Tuesday, June 26, 2018 10:47 AM
    Tuesday, June 26, 2018 10:25 AM

Answers

  • I found this solution below

    In a module

    Public Const ADODB_PROVIDER = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;"
    Public Const PATH_DB = "E:\Access\test.accdb"
     
    Public Function fWkBookCnxDelAll()
        Dim oWkBookCnx As WorkbookConnection
     
        For Each oWkBookCnx In ThisWorkbook.Connections
            oWkBookCnx.Delete
        Next oWkBookCnx
    End Function
    
    Public Function fWkBookCnxInitAll()    
        Dim objWBConnect As WorkbookConnection
     
        Set objWBConnect = ThisWorkbook.Connections.Add( _
        Name:="tcd", Description:="", _
        ConnectionString:=ADODB_PROVIDER & _
        "Data Source=" & PATH_DB, _
        CommandText:="SELECT * FROM qryFactureSumMonthYear", _
        lCmdtype:=xlCmdSql)
    End Function

    On Thisworkbook load event, the code below

    Private Sub Workbook_Open()
    
        fWkBookCnxDelAll
        fWkBookCnxInitAll
        
    End Sub

    On command button click event  of a worksheet,  the code below

    Private Sub cmdAddTCD_Click()
     
        Dim oCnx As WorkbookConnection
        Dim oPc As PivotCache
        Dim oPt As PivotTable
     
        ActiveSheet.Range("G7").CurrentRegion.Clear '
     
        ' Create a PivotTable cache
        Set oCnx = ThisWorkbook.Connections("tcd")
        Set oPc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, _
        SourceData:=oCnx)
     
        ' Create a tcd.
        Set oPt = oPc.CreatePivotTable( _
        TableDestination:=ActiveSheet.Range("G4"), _
        TableName:="tcd")
     
        With oPt
            .SmallGrid = False
     
            .AddFields _
            RowFields:="idfacture", _
            RowFields:="libelle", _
            ColumnFields:="PeriodemontYear"
     
            .AddDataField _
            Field:=oPt.PivotFields("montant") _
         End With
    End Sub

    A new TCD is now created


    • Edited by technet65 Tuesday, June 26, 2018 2:17 PM
    • Proposed as answer by Terry Xu - MSFT Wednesday, June 27, 2018 6:30 AM
    • Marked as answer by technet65 Wednesday, June 27, 2018 6:57 AM
    Tuesday, June 26, 2018 2:17 PM

All replies

  • I found this solution below

    In a module

    Public Const ADODB_PROVIDER = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;"
    Public Const PATH_DB = "E:\Access\test.accdb"
     
    Public Function fWkBookCnxDelAll()
        Dim oWkBookCnx As WorkbookConnection
     
        For Each oWkBookCnx In ThisWorkbook.Connections
            oWkBookCnx.Delete
        Next oWkBookCnx
    End Function
    
    Public Function fWkBookCnxInitAll()    
        Dim objWBConnect As WorkbookConnection
     
        Set objWBConnect = ThisWorkbook.Connections.Add( _
        Name:="tcd", Description:="", _
        ConnectionString:=ADODB_PROVIDER & _
        "Data Source=" & PATH_DB, _
        CommandText:="SELECT * FROM qryFactureSumMonthYear", _
        lCmdtype:=xlCmdSql)
    End Function

    On Thisworkbook load event, the code below

    Private Sub Workbook_Open()
    
        fWkBookCnxDelAll
        fWkBookCnxInitAll
        
    End Sub

    On command button click event  of a worksheet,  the code below

    Private Sub cmdAddTCD_Click()
     
        Dim oCnx As WorkbookConnection
        Dim oPc As PivotCache
        Dim oPt As PivotTable
     
        ActiveSheet.Range("G7").CurrentRegion.Clear '
     
        ' Create a PivotTable cache
        Set oCnx = ThisWorkbook.Connections("tcd")
        Set oPc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, _
        SourceData:=oCnx)
     
        ' Create a tcd.
        Set oPt = oPc.CreatePivotTable( _
        TableDestination:=ActiveSheet.Range("G4"), _
        TableName:="tcd")
     
        With oPt
            .SmallGrid = False
     
            .AddFields _
            RowFields:="idfacture", _
            RowFields:="libelle", _
            ColumnFields:="PeriodemontYear"
     
            .AddDataField _
            Field:=oPt.PivotFields("montant") _
         End With
    End Sub

    A new TCD is now created


    • Edited by technet65 Tuesday, June 26, 2018 2:17 PM
    • Proposed as answer by Terry Xu - MSFT Wednesday, June 27, 2018 6:30 AM
    • Marked as answer by technet65 Wednesday, June 27, 2018 6:57 AM
    Tuesday, June 26, 2018 2:17 PM
  • Hello technet65,

    I'm glad to hear that your issue has been resolved. I would suggest you mark your solution as answer to close the thread. If you have any other issue, please feel free to post threads to let us know.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, June 27, 2018 6:32 AM