Yes, as explained here. Obviously you only have one file, but the concept is still the same because you have multiple sheets.

Mynda

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:PQAtoZ_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 !

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

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 !

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

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

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

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