4

Even after searching and trying for a long time I am not sure whether it is possible it or not.

I have a PP model using SQL source. Let's say command is: 

select * from FactInternetSales where CustomerKey  = 11001

and I have filter, 'CustomerKey' and based on the filter selection I want to update the command. For e.g:

select * from FactInternetSales where CustomerKey  = 11002

The code I am trying is:

Dim mdl As ModelTable
    Dim wcon As WorkbookConnection
    Dim cmd As String

    Set mdl = ActiveWorkbook.Model.ModelTables("Customers")
    Debug.Print mdl.SourceWorkbookConnection.OLEDBConnection.CommandText
    mdl.SourceWorkbookConnection.OLEDBConnection.CommandText = "select * from      FactInternetSales where CustomerKey  = 11002"

Trying to assign a command results in 'Run-time error 1004'

I just come across this link, it seems possible. But I am not getting the 'temporary tables' part. Please share your opinion (and the VBA) code you have used to address this.

Thanks, Prakash

Prakash Gautam
  • 502
  • 4
  • 13
  • 1004 implies it's a read-only (or at least disallowed for editing) property of the object, and therefore can't be assigned through VBA, I'm also chasing the chap in your link for the script! – baldmosher Jun 08 '17 at 10:26
  • Seems the Connection is not read-only (if you create it through Excel wizard not direct in PP) but the CommandText is read-only [link](https://wordpress.training-nyc.com/vba/macro-to-change-powerpivot-data-connection/) – baldmosher Jun 08 '17 at 10:43
  • ....but as soon as you edit the connection in PP it makes it completely read-only again. – baldmosher Jun 08 '17 at 10:50

1 Answers1

1

I see my own question here after a long time. I have already found the solution so answering my own question.

The solution is that the connection need to be created from Excel Data menu and load/import the data from there ('Only Create Connection' and 'Add this data to Data Model'). The Data Model is marked read-only if the creation is created using PP window. And also if the tables/fields are renamed/added after importing from Excel Data menu, the Data Model is marked read-only but no problem in relationship and measures creation/addition.

Thanks

Prakash Gautam
  • 502
  • 4
  • 13
  • If the connection has been created in PP and is used for a pivot data model. Do I have to re-create the pivot as the connection is read-only? – user 88 91 Sep 21 '21 at 13:54
  • Your purpose is not clear. As long as you do not have to update the queries, you can create the connection either from PP or from Excel Data menu and you will not have to re-create the pivot for the refresh. – Prakash Gautam Sep 23 '21 at 02:53
  • I need to change database server, the old ones has been removed including users. If I create a connection in excel to the new database server can I simply change what connection the pivot will use? – user 88 91 Sep 23 '21 at 11:16