0

I'm improving my own tools for preparing corporate reports in Excel.

Current exercise: to reduce clicks count related to Power Query editor opening.

Current regular scenario once it needs to change existing query (see screenshot; sorry for non-EN locale in it)

  • A. Go to ribbon menu "Data"
  • B. Click "Book queries"
  • C. In "Book queries" pane select relevant query
  • D. Do double-click on it

Target/preferred scenario:

    1. Do right-click on smart-table [assosiated with power query]
    1. Click on my(custom) context menu item for opening Power Query editor

For now:

  • I know how to add my(custom) context menu item, available only over the smart-table
  • I know how to handle this menu item click in VBA
  •    check is it really clicked over the smart-table

  •    check/find associated query name

Problem: I haven`t found any way how to start/open Power Query editor [for specific query] from VBA code [or what ever else].

Thoughts:

  • looks like Power Query editor is not available via Excel Application Object Model
  • may be it would be available for automation via Commands("...") collection ?
  • may be it has representation in VSTO via C# ?
  • other ideas ?

enter image description here

braX
  • 11,506
  • 5
  • 20
  • 33
Andrey K
  • 23
  • 3
  • Perhaps you could parameterise your existing power query so you never need to change it – Nick.Mc Aug 02 '22 at 09:36
  • If possible, what will you do next? You can put a command button on QAT to run PQ. I think it would be the shortest effort to run it. – ALeXceL Aug 02 '22 at 10:52
  • BTW now I saw the button on your QAT (image). – ALeXceL Aug 02 '22 at 11:21
  • @ALeXceL, haven't found command "Power Query editor" in list for QAT (Excel 2016). Would you show how it looks like at your end? _what will you do next?_ Ideally would like to have following VBA method =) `sub openPowerQueryEditorFor(QueryName as String)` – Andrey K Aug 02 '22 at 13:58
  • @AndreyK It is the rightmost icon of the QAT displayed in the image you posted (spreadsheet with brush) – ALeXceL Aug 02 '22 at 16:33
  • @ALeXceL, nope. There rightmost command icon is "Create style for Pivot Table". If you mean "Customize QAT", then I tried it: looked frough the list of "All commands" and didn't find there something like "[open] Power Query [editor]". If you see such command, let me know ... or show a screenshot. – Andrey K Aug 02 '22 at 21:46
  • Mine is the same image: "Iniciar Editor do Power Query" in plain Pt-Br :-) See this image: [link](https://encrypted-tbn0.gstatic.com/images?q=tbn:ANd9GcSk_gufB-65zy17ocyiPL8ZqKK1FjmcTIQYE4czO1n6_9c1ZBtY3AiNvNFBxEi5EO3C6yE&usqp=CAU) – ALeXceL Aug 03 '22 at 12:23
  • @AndreyK I don't have access to XL2016 frontend but in images on www there are the "Insert" Tab from where you probably get it. Mine is 32Bit XL365 Desktop – ALeXceL Aug 03 '22 at 12:31

1 Answers1

1

Thanks to @ALeXceL for moving me to available option.

  1. Application.CommandBars.ExecuteMso "PowerQueryLaunchQueryEditor"; see about CommandBars.ExecuteMso and how to identify idMso

  2. ONCE active/selected cell is inside smart table assosiated to query THEN editor opens on this query; thats why there is no "input parameter" like QueryName

  3. Revised my "Target/preferred scenario"(from initial post); decided to use QAT+ShortCut as it was recommended in comments )

Andrey K
  • 23
  • 3