How do I programmatically changing the Command Text property of a Connection in MS Excel 2007

This should be and easy one, but for the life of me I can’t figure it out.  I have the SQL String, now all I have to do is get it updated in the connection text property of the specified connection and refresh the data.

I need this ASAP…
LVL 2
Cristal638Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RichardSchollarCommented:
This is an example provided by MS in their xl2007 Help:

This assumes you have a querytable.  If this doesn't work for you, it is probably because this hasn't been updated for 2007, try changing the first line to:

Set qtQtrResults = _
    Workbooks(1).Worksheets(1).ListObjects(1).QueryTables(1)   'amend workbook/worksheet refs to suit

Richard
Set qtQtrResults = _
    Workbooks(1).Worksheets(1).QueryTables(1)
With qtQtrResults
    .CommandType = xlCmdSQL
    .CommandText = _
        "Select ProductID From Products Where ProductID < 10"
    .Refresh
End With

Open in new window

0
Cristal638Author Commented:
I had already tried that and I got a script out of range error.  

I typed in Workbooks(1).Worksheets(1).QueryTables.count in the immediate window and 0 was returned.  So I don't think I have a querytable.  I'm an Access guy and Excel 2007 is very unfamiliar to me.

I just want to filter the data in a pivot table when the user clicks a button.  When they click it a multi select list box appears, they select a few things which is used in the where clause.  I then want to take that new Filtered SQL and update the command text then refresh the data.
0
RichardSchollarCommented:
So you have a pivot table where the data is returned to?  Then you need to access the commandtext property of the pivot cache:

Note that this assumes you have an ODBC connection (which I presume you do given the tags for the question).




ThisWorkbook.PivotCaches(1).CommandText = "SELECT * FROM Table1"

Open in new window

0
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Cristal638Author Commented:
I think we're getting close.

If I type in the immediate window
?ThisWorkbook.PivotCaches(1).CommandText

The commandtext SQL I want to change is returned.  The problem is that I get a Run-Time Error '1004': Application-Defined or Object-Defined Error.

If I manually copy the SQL String into the commandtext, it works perfectly.  I just need to do this programmatically.
0
RichardSchollarCommented:
Can you explain what you mean by this:

"If I manually copy the SQL String into the commandtext, it works perfectly.  I just need to do this programmatically."

Where are you manually copying the SQL String into commandtext?
0
Cristal638Author Commented:
Excel 2007
go to the "Data" tab
click "Connections"
Select the connection and then and click the properties button
Go to the "Definition" tab
There's an area to type your Command Text:
It's where I manually copied the SQL String I created and that works just fine.  What I need to do is have that process done programmatically.
0
RichardSchollarCommented:
So you have Read-Only access to the CommandText property (in the Immediate Window) but not Write access (seemingly)?  Have you tried it with very simple SQL strings (which are, say, only one field different from the current SQL that you know works)?
0
Cristal638Author Commented:
Yes, I've tried just about everything.  I even tried "SELECT * FROM TABLENAME".  You can't get much simpler than that.

I don't know why this is so difficult.  It seems like it should be a piece of cake.
0
RichardSchollarCommented:
What value is returned if you type in:

?ActiveWorkbook.PivotCaches(1).QueryType

into the Immediate Window whilst this workbook is active?
0
Cristal638Author Commented:
?ActiveWorkbook.PivotCaches(1).QueryType returns 1 in the Immediate Window
0
RichardSchollarCommented:
Well, that's as it should be (showing it's an ODBC datasource).

I'm really not sure why it isn't working to change it programmatically then.  I'll ask a friend to take a look as he might be able to resolve your problem.

Richard
0
RichardSchollarCommented:
In the Immediate Window can you type in the following and in your reply paste exactly what is returned:

?ActiveWorkbook.PivotCaches(1).Connection

Next type in this and also paste the return in your reply:

?ActiveWorkbook.PivotCaches(1).CommanText

Next type in this and also paste in your reply:

?ActiveWorkbook.PivotCaches(1).CommandType

This will help me and anyone else to (hopefully) figure out why it isn't working for you.

Richard
0
RichardSchollarCommented:
There was a typo in the CommandText bit of code above ;)
0
RichardSchollarCommented:
Another thought (unlikely from what you've said so far) but you're not trying to target another database when changing the SQL?  You are using the same db, just targetting another table/view/query?
0
Rory ArchibaldCommented:
Do you by any chance have multiple pivot tables based on each other?
0
Cristal638Author Commented:
?ActiveWorkbook.PivotCaches(1).Connection
ODBC;DBQ=C:\lair\data\SHADdata.mdb;DefaultDir=C:\lair\data;Driver={Microsoft Access Driver (*.mdb)};DriverId=281;FIL=MS Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;PWD=Kugel;SafeTransactions=0;SystemDB=c:\lair\sys\lairsys.mdw;Threads=3;UID=training;UserCommitSync=Yes;

?ActiveWorkbook.PivotCaches(1).CommandText
SELECT `CPAD data qry`.BGROUP, `CPAD data qry`.LEVEL, `CPAD data qry`.COMMITTED, `CPAD data qry`.REG, `CPAD data qry`.TERR, `CPAD data qry`.DIST, `CPAD data qry`.HUSI, `CPAD data qry`.NAME, `CPAD data qry`.CITY, `CPAD data qry`.STATE, `CPAD data qry`.ITEM, `CPAD data qry`.UNIT, `CPAD data qry`.SALE, `CPAD data qry`.MONTH, `CPAD data qry`.YEAR, `CPAD data qry`.QTR, `CPAD data qry`.YTD, `CPAD data qry`.COMM, `CPAD data qry`.BSGP, `CPAD data qry`.`GROUP`, `CPAD data qry`.CAT, `CPAD data qry`.CODE, `CPAD data qry`.Channel, `CPAD data qry`.RDIV, `CPAD data qry`.RSALE, `CPAD data qry`.RUNIT
FROM `c:\lair\data\SHADdata`.`CPAD data qry` `CPAD data qry`

?ActiveWorkbook.PivotCaches(1).CommandType
 2

Another thought (unlikely from what you've said so far) but you're not trying to target another database when changing the SQL?  You are using the same db, just targetting another table/view/query?  Nope. Same mdb, just want to filter the data.

Do you by any chance have multiple pivot tables based on each other? No.

0
RichardSchollarCommented:
I wonder if it's because of the back quotes in the command text - were you using these when you tried to change the commandtext?

Try changing CommandText (programmatically) to the following SQL:

"SELECT T.BGROUP, T.NAME, T.YEAR FROM [CPAD data qry] T"

copy the above exactly as it appears and try and change the pivotcache's CommandText via the Immediate Window in the VBE.

Please let us know how you get on.

Richard
0
Rory ArchibaldCommented:
What is the result of:
?ActiveWorkbook.PivotCaches.Count

and how many pivot tables do you have in the workbook?

Also, if you try a test in a new workbook setting up a pivot table off an Access db without using a system database, can you then alter the commandtext there?
0
Cristal638Author Commented:
Finally I figured it out.

It seems to be a flaky/bug thing in excel, when you have more than one Pivot table in a workbook.  The solution was to change the odbc connection to ole, set the commandtext and then reset the connection back to odbc.  Sounds dumb to me, but it works…

Here's the code:

Public Function FilterCPADConnectionData(strWhere As String)
Dim strSQL As String, strOldSQL As String

    strSQL = "SELECT * " & _
              "FROM [CPAD data qry] " & _
              "WHERE BGROUP " & strWhere & " " & _
              "ORDER BY BGROUP;"

    dbODBCtoOLEDB  'Change ODBC to OLE
   
    ThisWorkbook.PivotCaches(1).CommandText = strSQL
   
    dbOLEDBtoODBC  'Change OLE to ODBC

End Function

Private Sub dbODBCtoOLEDB()
    Dim pcPivotCache As PivotCache
    Dim strConnection As String
    For Each pcPivotCache In ActiveWorkbook.PivotCaches
        If pcPivotCache.QueryType = xlODBCQuery Then
            strConnection = _
                Replace(pcPivotCache.Connection, "ODBC;DBQ", "OLEDB;DBQ", 1, 1, vbTextCompare)
            pcPivotCache.Connection = strConnection
        End If
    Next pcPivotCache
End Sub

Private Sub dbOLEDBtoODBC()
    Dim pcPivotCache As PivotCache
    Dim strConnection As String
    For Each pcPivotCache In ActiveWorkbook.PivotCaches
        If pcPivotCache.QueryType = xlOLEDBQuery Then
            strConnection = _
                Replace(pcPivotCache.Connection, "OLEDB;DBQ", "ODBC;DBQ", 1, 1, vbTextCompare)
            pcPivotCache.Connection = strConnection
        End If
        pcPivotCache.Refresh
    Next pcPivotCache
End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rory ArchibaldCommented:
That should only be necessary if they are sharing the same cache, BTW.
0
RichardSchollarCommented:
If you can use OLEDB why are you using ODBC if you don't mind me asking?
0
AzDaytonIT GuyCommented:
Unfortunately, that won't work if you have workbooks with multiple connection, where some are ODBC and some are OLEDB, because that code will try to change them ALL back to ODBC (even the ones that should be left as OLEDB).

I'm trying to fix things for our Finance dept, who make their own workbooks and pivot tables, of course (and have for years, which is probably why most are ODBC but some are OLEDB).  They simply want to move the location of an .MDB file, and this bug is killing us!  Should be simple!

If anyone's found a better solution by now (above solution is 2 yrs old), please let me know!

Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.