• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Combine several existing worksheets (The data are not in table but range cell) in Power Query |Power Query|Excel Forum|My Online Training Hub

You are here: Home / Combine several existing worksheets (The data are not in table but range cell) in Power Query |Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryCombine several existing worksheets…
sp_PrintTopic sp_TopicIcon
Combine several existing worksheets (The data are not in table but range cell) in Power Query
Avatar
Chris Yap
Member
Members
Level 0
Forum Posts: 162
Member Since:
August 21, 2019
sp_UserOfflineSmall Offline
1
July 17, 2020 - 11:34 am
sp_Permalink sp_Print

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 !

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4450
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
July 17, 2020 - 2:00 pm
sp_Permalink sp_Print sp_EditHistory

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

Mynda

Avatar
Chris Yap
Member
Members
Level 0
Forum Posts: 162
Member Since:
August 21, 2019
sp_UserOfflineSmall Offline
3
July 17, 2020 - 7:24 pm
sp_Permalink sp_Print

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 !

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4450
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
July 18, 2020 - 4:02 pm
sp_Permalink sp_Print

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

Avatar
Chris Yap
Member
Members
Level 0
Forum Posts: 162
Member Since:
August 21, 2019
sp_UserOfflineSmall Offline
5
July 18, 2020 - 6:31 pm
sp_Permalink sp_Print

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 !

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1518
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
6
July 19, 2020 - 3:36 pm
sp_Permalink sp_Print

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

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4450
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
7
July 19, 2020 - 4:59 pm
sp_Permalink sp_Print

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

Avatar
Chris Yap
Member
Members
Level 0
Forum Posts: 162
Member Since:
August 21, 2019
sp_UserOfflineSmall Offline
8
July 19, 2020 - 5:25 pm
sp_Permalink sp_Print

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

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4450
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
9
July 20, 2020 - 9:14 am
sp_Permalink sp_Print sp_EditHistory

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

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online:
Guest(s) 8
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 205
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
stuart burge
Bruce Tang Nian
Scot C
Othman AL MUTAIRI
Misael Gutierrez Sr.
Attif Ihsan
Kieran Fee
Murat Hasanoglu
Brett Dryland
Saeed Aldousari
Forum Stats:
Groups: 3
Forums: 24
Topics: 6223
Posts: 27295

 

Member Stats:
Guest Posters: 49
Members: 31920
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.