none
Modifying Commandtext for workbookconnection runtime Excel 2007 RRS feed

  • Question

  • I'm trying to modify a SQL string runtime in Excel 2007.

    I keep getting a runtime 1004 "Application defined or object-defined error".

    I have one pivotcache in my workbook which is being used in 4 different pivottables.

    I connect to a DB2 database.

    First I tried

    set pvc = Activeworkbook.Pivotcaches(1).WorkbookConnection

    pvc.OdbcConnection.Commandtext = vba.Replace(pvc.OdbcConnection.Commandtext , " 'THA' ", "'TH'")

     

    Then I tried to split the commandtext in to array of strings with 127 charachters  - didn't work :-(

    Then I tried to record a macro and can then see that the recorder also splits the long SQL string (884 char) into a array.

    When I try to run the recorded macro - I get the same 1004 - even if I doesnt change the commandtext.

    Can anyone give me a hint ?

     

    Tuesday, February 13, 2007 4:07 PM

Answers

  • Hi Dennis,

    thx for the input - It did'nt solve my problem :(

    It worked if I tried with the northwind database - but when I wanted to it on ODBC datasource - I got the same error

    The solution was to use new the handy method "ChangeConnection" on the pivottable object in xl2007

    Sub ChangeCon()

        Dim wc As WorkbookConnection
        Dim pvt As PivotTable
       
        Set wc = ActiveWorkbook.Connections.Add("NewConn", "Nice method", "Connstring", "SQL-STRING")
       
        Set pvt = ActiveSheet.PivotTables(1)
       
        pvt.ChangeConnection wc

    End Sub

    /Erik

    Thursday, February 15, 2007 9:56 AM

All replies

  • Hi Erik,

    See if the following will make it more clear how to work with the pivotcache:

    Option Explicit

    Sub Update_SQL()
    Dim wbBook As Workbook
    Dim wsSheet As Worksheet
    Dim pc As PivotCache
    Dim pt As PivotTable

    Set wbBook = ThisWorkbook
    Set wsSheet = wbBook.Worksheets(1)

    Set pc = wbBook.PivotCaches(1)

    'The following print the present value in the immediate window.
    Debug.Print pc.CommandText
    pc.CommandText = "SQL Statement"
    'The following print the updated value in the immediate window.
    Debug.Print pc.CommandText

    'Here we use the first p/t to update the pivotchache
    Set pt = wsSheet.PivotTables(1)
    Debug.Print pt.PivotCache.CommandText
    pt.PivotCache.CommandText = "New SQL Expression"
    Debug.Print pt.PivotCache.CommandText
     
    End Sub

    For future pure Excel questions please read the following:
    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=174275&SiteID=1

    Wednesday, February 14, 2007 5:34 PM
  • Hi Dennis,

    thx for the input - It did'nt solve my problem :(

    It worked if I tried with the northwind database - but when I wanted to it on ODBC datasource - I got the same error

    The solution was to use new the handy method "ChangeConnection" on the pivottable object in xl2007

    Sub ChangeCon()

        Dim wc As WorkbookConnection
        Dim pvt As PivotTable
       
        Set wc = ActiveWorkbook.Connections.Add("NewConn", "Nice method", "Connstring", "SQL-STRING")
       
        Set pvt = ActiveSheet.PivotTables(1)
       
        pvt.ChangeConnection wc

    End Sub

    /Erik

    Thursday, February 15, 2007 9:56 AM
  • Erik,

    Thanks for coming back and provide the solution - highly appreciating.

    Thursday, February 15, 2007 10:37 AM
  • I know this is 10 years old... but it still applies!  I had this problem, and this was the best solution! 

    Thanks!

     
    Saturday, October 14, 2017 6:30 PM