none
Changing pivot commandtext in VBA - alias fields with spaces RRS feed

  • Question

  • Hello,

    I'm trying to change the pivotcache in VBA with SQL that has space in the alias of a field.

    My SQL string looks like: select field1  as "my alias1" from table1

    pt.PivotCache.CommandText = mySqlString  -> returns an error

    It works if I do it manually, but not in VBA. Does anyone know the right syntax?

    Best regards,

    Wouter

    Thursday, September 10, 2015 3:31 PM

Answers

  • Found it!

    The syntax with quotes is alright.

    My problem is that I have 1 connections with 3 pivots. It looks like you can only change the commandtext of a pivot if it is the only one using the connection.

    In case of a connection used by multiple pivots, you have to change the connection, not the picot.commandtext.

    Giving it a rest over night somethimes does wonders....

    Friday, September 11, 2015 6:40 AM

All replies

  • Hi Wouter,

    Please replace the double quotation marks with square brackets. Here is the code for your refernce:

    ActiveSheet.PivotTables(1).PivotCache.CommandText = "select [first name] as [my alias1], [city] from [Office Address List]"
    ActiveSheet.PivotTables(1).PivotCache.Refresh
    Regards & Fei



    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, September 11, 2015 3:21 AM
    Moderator
  • Hello,

    I've tried, but it did not work out.

    Also if I try this manually, this won't work either.

    This looks like Access-SQL syntax no? My pivot queries on DB2, where quotes can be used to surround aliasses.

    Thanks for your suggestion,

    Wouter

    Friday, September 11, 2015 6:26 AM
  • Found it!

    The syntax with quotes is alright.

    My problem is that I have 1 connections with 3 pivots. It looks like you can only change the commandtext of a pivot if it is the only one using the connection.

    In case of a connection used by multiple pivots, you have to change the connection, not the picot.commandtext.

    Giving it a rest over night somethimes does wonders....

    Friday, September 11, 2015 6:40 AM