Hi Alma,

I'm not sure why changing workbooks would trigger this error, sorry. That said, I would not be using the legacy web connector. I'd try to connect directly from Power Query to your SQL database since there is a connector already built in Power Query for this.

Mynda

Thank you for your help. I work for an international employer and direct access to SQL-Server is restricted.
That's why even the usual feature "Import from Web Page" doesn't work an I have to choose legacy Web import. And I can't open this legacy connection in powerquery 🙁

I'd ask the database administrator to write a view that you can connect to with Power Query. After all, you're getting the data via your legacy connector, so really doesn't make any difference. Anyhow, I'm sure you've probably tried already. Maybe they can give you an export file you can import instead.

Thank you very much. They wouldn't change any access types ;(
Anyhow I continue searching why the error occurs by switching active workbooks 🙂

Thank you very much!

Best,

Alma

Hi Mynda,

first I want to thank you for your support and by now I've watched already 3 Webinars from you on Youtube...

Awesome!!!
I want to thank you for sharing your skills!

-------------------------------

I figured out why my error occurs:

The source of my Query is defined as:
Excel.CurrentWorkbook(){[Name="Query!example"]}[Content]

Thats why it always produces erorrs if any other Workbook is activated. It tries to autoupdate the table located in "CurrentWorkbook".

I want to share this Workbook with many people and I don't know, where they are going to save (location) it. I'm not comfortable with Power Query Code, but my VBA skills don't help me at this point.

Is there any option to define the source as:

Excel.ThisWorkbook (like in VBA?)

Tank you very much!

Best,

Alma

Hi Alma,

It's great to hear you found my tutorials helpful 🙂

I'm confused whether you're referring to the old query connector or Power Query. I don't understand how you can refresh a query that isn't in the current workbook. If you're using the Excel.CurrentWorkbook connector then it doesn't matter where it is saved as it's only referencing the current file, not looking for a file somewhere else. 

Mynda

The source of my Query is defined as:
Excel.CurrentWorkbook(){[Name="Query!example"]}[Content]

Are you sure?

The exclamation mark is an invalid char in table names or defined names, so this does not look like a valid reference: "Query!example"

CurrentWorkbook should be the same thing as ThisWorkbook in VBA, was not able to replicate what you said: added 2 workbooks, one with a query in it, one empty; activated book 2, the run a code to refresh the book1 query:

workbooks("1.xlsx").Worksheets("Table1").Listobjects("Output").querytable.refresh

The query refreshed without any problems, even if book 1 was not active when the code run. (?activeworkbook.Name returns "2.xlsx")

My employer hast restricted access to different datas. The legacy import is the only way for me to download the SQL data.

The problem doesn't occur by downloading the data, but by transforming it into the new query.

The exclamation mark is an invalid char in table names or defined names, so this does not look like a valid reference: "Query!example"

It does work.

workbooks("1.xlsx").Worksheets("Table1").Listobjects("Output").querytable.refresh

I know that this works. Try out this:

Setting up autoupdate to 1 minute for the query in the properties.

Open another workbook, wait until 1 minute has passed. Then the autoupdate will try to search the query to update in "CurrentWorkbook".

CurrentWorkbook should be the same thing as ThisWorkbook in VBA

I think CurrentWorkbook is same in VBA as "ActiveWorkbook" not "ThisWorkbook". Makes an important difference if the workbook is not activated.

🙂

For explanation:

My team members need the live data from the SQL database.
That's why I want to create the query with 1 minute autoupdate.

Then you can run the query in a corner of your desktop and go on with your work. But you can't work in other workbooks, because of the error which occurs every time the autoupdate runs in background while another workbook is active.

The autoupdate every 1 minute from legacy import works fine.
Only the update from the list of the legacy import <-- perhaps that's why the problem occurs. Because of the legacy import, I can't transform the imported data in to table.

When I load into a new worksheet, it came up with the error "Expression Error: we couldn't find an Excel Table named '68Ga!_FilterDatabase'.

You have to filter out from Name column whatever contains "FilterDatabase". These are duplicates of the data tables that excel generates in the background when a filter was applied in that range, even if the filter is currently cleared.

You get the error because Excel.CurrentWorkbook listed the output table of the query as well, you can see it in the last row of the image attached. When you remove those 6 last rows, one filterDatabase remains and that generates the error. Better to apply a filter than removing rows.

The legacy import is the only way for me to download the SQL data.

There is no connection to SQL in the file you sent. Why legacy import works and Power Query does not work? For most office 365 licenses, you do have Get Data>From Database> From SQL Database, then just set database login credentials in the authentication screen.

Just noticed that it's not your topic...

Please do not use other topics to post different problems, start a new topic instead.

I think CurrentWorkbook is same in VBA as "ActiveWorkbook" not "ThisWorkbook". Makes an important difference if the workbook is not activated.

Sorry, can't agree with this statement.

If you create a query from a table in power query (the query will use Excel.CurrentWorkbook() function), and set that query to refresh each minute, IT WILL refresh properly, no matter how many workbooks you have open or which book is active. Tested.

The source of the error is not power query, it's the legacy import query.

thank you for your help, but at my side it looks a bit different:

quotation: [Expression.Error] We couldn't find an Excel table named 'Query!Rejects_Query'

I renamed the Query name. I'm working for Amazon and that's why i can't upload any example workbooks.

And that's why I won't get login credentials for our SQL server too 😉
The direct access is extremly limited.

I can only access the data via Web UI and can create the Query-Link for Excel import. But this works only with the legacy import, because the access to the data is given by cookies in the browser. That's why the SQL data import doesn't work with the query link.

I haven't got any duplicates in my table. In this part, everything looks fine.
Using Excel 2019 Pro.

EDIT: Sorry just have seen my posts have been moved to new topic. Using new topic by next post.

Hi Catalin,

sorry I've posted last answer in the old topic.

The legay import is not the problem. It works with autoupdate fine.

Just the "import" from sheet 1 to sheet 2 by PQ from the legacy list, doesn't work if the workbook isn't active.

As I noticed in the other post, I'm sorry, but I'm not allowed to post any excamples workbooks.

Perhaps it's the query which is generated from the legay query, because the legacy query has not really a table, and is just a connection.

Is there any possibility to change the source:

e.g.: = Excel.CurrentWorkbook(){[Name="Table9"]}[Content]

to something like: Excel.CurrentWorkbook(){[Sheet="Sheet1" Range="A1:F100"}[Content]

Excel.CurrentWorkbook() only returns defined names (only those referring to a range) and table objects, not sheets.

If you want to get the range "A1:F100", give a name to that range, MyRange for example, then use:

Excel.CurrentWorkbook(){[Name="MyRange"]}[Content]