Bex query re-embed from VBA

[Former Member]

Is there a way for VBA to execute a "Quit and Use query" exit from Query Design mode for all queries in an existing Excel workbook?

The problem is that I have inserted many queries with different variables (in this case Accounts) into an Excel workbook and then made some changes to the query itself.

I notice that a simple refresh doesn't properly refresh with the latest changes I made in query mode. And I want to avoid manually going to each query and entering design mode - only to Quit and Use query to force an proper refresh.

Thank you very much.

KK

[Former Member]

Hi KK,

I know of no way to do exactly what you want. I hope someone else does.

I can tell you a few tricks ... each of which has its own drawbacks. While you are in VBA ... are you familiar with the VERY hidden worksheets named SAPBEXqueries and SAPBEXfilters? If not, while you are in VBA Editor, select these sheets and change their Visible property to visible.

In Excel, on the worksheet named SAPBEXqueries you will see (now that you have made the sheets visible) in column C, starting in row 4 are the server IDs of the queries embedded in your workbook. If you do one insertion manually, you can then copy the new server ID into the other cells in that column and have the new query connected to each of the worksheets (that already have queries embedded, that it).

There is a problem with doing this. Sometimes. OK, often. The problem is if you have unpublished structures (as is often the case with Key Figures), they have their own unique ID on the server. When you changed the query, that ID probably changed also. If so, when you do the trick of copying the new query server ID into the other cells in column C and refresh the queries you will have no Key Figures appear in any of those other query results tables. You have to use Change query (local view) to get them back. Or, you have to do a global find and replace of the old unique ID with the new unique ID for that structure. This is easy to do either manually or with VBA. But, you should practice with a spare copy of your workbook until you have confidence that you are doing it all correctly.

I hope this helps. I will be interested to hear back if this solved your problem.

The other approach ... if your main concern is losing the filters in the workbook ... would be to simply keep a copy of the old workbook, then replace the filters (on the SAPBEXfilters worksheet) after you have re-embedded the new queries. You can often do this with nothing more than pasting all of the cells from the old workbook (on the sheet named SAPBEXfilters) to the new workbook (on the sheet named SAPBEXfilters). The problem with this second approach is if you have Conditions ... which, as you know, will look a lot like filters, but set on Key Figures ... which means set on those structures whose names have all changed. Same problem we had before!

- Pete

[Former Member]

Hi Pete,

Yes I am aware of the hidden SAPBEXqueries and have been studying it but find it hard to understand the details without any documentation.

Many thanks for the tip on copying the new query server ID. I will test this out soon. I think I agree with you that automating this may not be possible.

So instead I have written a VBA macro to generate a report of all SAP queries and their variables used in the workbook. The idea is that hopefully at least users can identify the queries in each worksheet that need to be manually refreshed.

Many thanks again for your helpful comments.

Regards,

KK

[Former Member]

Hi KK,

I know of no formal documentation of the hidden SAPBEXqueries sheet. Here is link to a posting you might find useful; it attempts to begin to explain what is found on each of the two hidden sheets:

Another posting that is also related to understanding the tables in the hidden sheets:

- Pete