Log In
Register
Home
January 7, 2022
Offline
Hi Mynda,
I got the same problem but a different trigger and hope you can help me as well.
I use a legacy wizard connection from Web to get data from our SQL database which I can access over an user interface from which I get query-link for Excel.
First I need to create the connection to the data, then I turned on autoupdate every 1 minute in the properties.
Because of I get only raw data from this connection and I like it to format it, I used a query from this table and let me show the Data in an organized way on another sheet.
Then I added autoupdate to the properties of the new query.
Everything works fine until I change Excel Workbooks. I can open other applications or do what I want as long as this workbook was the last active workbook from Excel. I I highlight another workbook I always get the Expression Error: We couldn't find an Excel Table named " " for the new query.
I think it's a bug and the other highlighted workbook tries to make the autoupdate from a table which doesn't exists in it.
Thank you for your help in advance 🙂
Best,
Alma
See Answer
July 16, 2010
Offline
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
January 7, 2022
Offline
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 🙁
July 16, 2010
Offline
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.
January 7, 2022
Offline
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
January 7, 2022
Offline
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
July 16, 2010
Offline
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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")
January 7, 2022
Offline
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.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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.
January 7, 2022
Offline
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.
January 7, 2022
Offline
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.
January 7, 2022
Offline
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]
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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]
January 7, 2022
Offline
Okay...sometime Excel is so annoying...
here is how I fixed the problem:
As I supposed, the error was created by the query from the list of the legay query.
I can't transform the legay query into a table, because in this case Excel wants to remove the external connection from the list/table.
Now I got
Sheet1 = Legacy Query
Sheet2 = Formula: A1 = Sheet1!A1
Expanded this formula to A1:E50 because we haven't got so much entries in this live data.
With this range of A1:E50 I created a table and this table is used by the PQ, filtering only used rows of table.
Sheet3 = PQ result
Sheet1 + 2 are hidden and everything works fine... o_O
Many thanks for you assistance 🙂
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Glad to hear you solved it.
To get data from a sheet in the current workbook, if you don't want to use a named range as suggested above, you have to use the file full path like this:
let
Source = Excel.Workbook(File.Contents("C:\Users\Catalin\Desktop\1.xlsx"), null, true),
#"Filtered Rows" = Table.SelectRows(Source, each ([Kind] = "Sheet") and ([Item] = "Sheet1")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Data"}),
#"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Column1", "Column2"}, {"Column1", "Column2"})
in
#"Expanded Data"
You will then be able to select a specific sheet.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
There is no way to get the current file full path in power query...
You still have to use a defined name in a cell with this formula:
=LEFT(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1),1)-1)
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Sheet1 = Legacy Query
Sheet2 = Formula: A1 = Sheet1!A1
Expanded this formula to A1:E50 because we haven't got so much entries in this live data.
With this range of A1:E50 I created a table and this table is used by the PQ, filtering only used rows of table.
Just from curiosity, why didn't you defined a named range for A1:E50 in Sheet1, where your query saves data?
You'll be able to use : Excel.CurrentWorkbook(){[Name="MyRange"]}[Content] to get that Sheet 1!A1:E50 range into power query, it does NOT have to be a table, as mentioned it can also be a NAMED RANGE... A named range is not the same thing as a table object.
Answers Post
1 Guest(s)
