I have a requirement to merge specific 'export' tabs (worksheets) that are contained within folders that are within a SharePoint document library. The folders are 'top-level' items within list A. Within each folder are subfolders which contain list items that are of type excel. I would like to use powershell to iterate over list A and it's subfolders and grab the worksheet (tab) named 'export' merging them into a new excel workbook within List A (at the top level).

I can easily get the list of all folders/subfolders and list items (of type excel). What I can't figure out is how to get an object instance (for lack of a better term) to each excel workbook (the list items). To powershell, each item is just an item and not of type excel. Can someone advise on how I might do this? Thanks.

  • List? Or do you mean Library? What should the result be? An Excel file? If so, this can be done with Power Query in Excel. – teylyn Apr 2 at 20:03
  • My apologies. Yes it's a list. Would someone provide a reference on how I might do this with Power Query? I have a top-level document library that contains 18 folders. Within each folder are an Archive folder and 3-4 excel workbooks representing monthly sales data. I need to grab the 'export' worksheet from each of those workbooks within each of the 18 folders and combine them into a new workbook that I can then refresh as needed. Much appreciated for any guidance you may be able to provide. – user1513177 Apr 4 at 13:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.