Log In
Register
Home
August 21, 2019
Offline
Hi,
I was trying to use = Excel.CurrentWorkbook() to combine multiple worksheets into one single one and realize that this M code is applicable to tables ( correct me if I am wrong), see my attached sample, the output in PQ only Content and Name as header
Currently if we have a - z 26 worksheets and each of them are names, instead of using
= Excel.Workbook(File.Contents("d:\PQ\AtoZ_Combine_Chris.xlsx"),null,true)
can we transform within the same file instead
For all expertise advise please.
thank you !
July 16, 2010
Offline
Yes, as explained here. Obviously you only have one file, but the concept is still the same because you have multiple sheets.
Mynda
August 21, 2019
Offline
Hi Mynda,
understand I got no problem using another blank excel then use from folder method, however I would like to do it within the same file, i.e. a - z worksheets, in the blank query, I attempt to use
= Excel.Workbook(File.Contents("D:\PQ\AtoZ_Combine_Chris.xlsx"),null,true)
it seems work for first time, however when I try to add new name (Cathy) to c worksheet, notice got to Ctrl S and save it, then right-click refresh, and it works, but when I delete Cathy and add Carol, I save it and refresh, the old entry Cathy still there
the scenario is all the a-z worksheet names (non-table) combine using power query within the same file
Thank you !
July 16, 2010
Offline
Hi Chris,
Excel.Workbook cannot get data from an Excel file that is open, therefore you can't reference the current file using this function, which is why the query doesn't refresh.
If you don't want to format your data in an Excel Table then Excel.CurrentWorkbook will also work with defined names.
Mynda
August 21, 2019
Offline
Hi Mynda,
when I use Excel.CurrentWorkbook() in a blank query, it don't take in non-table worksheet
For your further advise
Thank you !
October 5, 2010
Offline
Hi Chris,
Why can't you change the source data into tables?
If Excel functions and other tools are designed to work with this data structure, surely you are better off to use them?
Phil
July 16, 2010
Offline
Hi Chris,
As I said, If you don't want to format your data in an Excel Table then Excel.CurrentWorkbook will also work with defined names. i.e. it doesn't work with undefined ranges of data.
Mynda
August 21, 2019
Offline
Phil / Mynda
Users got quite a number of worksheets and he got to one by one format as tables, that will be tedious, imagine got 100 over worksheets, and if they want to combine within the same file
So is it confirm Excel.CurrentWorkbook() doesn't work with underfined ranges of data ?
Hi Expert, any other way ? VBA to auto format all the undefined ranges of data in one go, then Excel.CurrentWorkbook()
thank you for your patience
July 16, 2010
Offline
Yes, that's what I said. Excel.CurrentWorkbook doesn't work with undefined ranges of data. You can either get the data into another workbook using Excel.Workbook, or you can use VBA to format all of the lists into tables or define names for them.
You can try consolidating the data into another workbook and then bring it from there back into the workbook with all the sheets, just remember that Excel.Workbook requires the source file to be closed.
Mynda
1 Guest(s)
