SOLVED

SP lists export - .iqy files

%3CLINGO-SUB%20id%3D%22lingo-sub-852824%22%20slang%3D%22en-US%22%3ESP%20lists%20export%20-%20.iqy%20files%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-852824%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20wanting%20to%20put%20several%20.iqy%20files%20in%20a%20single%20workbook%20but%20not%20sure%20if%20this%20is%20possible.%26nbsp%3B%20I%20can%20achieve%20the%20same%20thing%20by%20just%20creating%20multiple%20connections%20on%20multiple%20work%20sheets%20but%20that%20way%20takes%20forever%20when%20refreshing.%26nbsp%3B%20When%20I%20refresh%20.iqy%20files%2C%20they%20are%20finished%20in%20seconds%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20anyway%20to%20export%20multiple%20SP%20lists%20and%20then%20merge%20them%20into%20a%20single%20workbook%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-852824%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ELists%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-852907%22%20slang%3D%22en-US%22%3ERe%3A%20SP%20lists%20export%20-%20.iqy%20files%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-852907%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F325149%22%20target%3D%22_blank%22%3E%40Bryan123%3C%2FA%3E%26nbsp%3BI%20do%20this%20in%20SharePoint%202013%20on%20premise%20and%20it%20worked%20fine.%20I've%20got%20an%20asset%20checkout%2Fcheckin%20form.%20Three%20different%20lists%20control%20the%20formatting%20of%20the%20form%2C%20so%20I've%20got%203%20tabs%2C%20each%20with%20an%20.iqy%20creating%20the%20tables.%20I%20set%20the%20spreadsheet%20to%20automatically%20refresh%20connections%20on%20startup.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20I%20added%20a%20tab%20at%20the%20beginning%20that%20runs%20through%20all%20the%20data%20as%20needed%20(in%20VBasic)%2C%20allowing%20a%20user%20to%20select%20a%20name%2C%20then%20I%20fill%20in%20the%20blanks%20and%20format%20the%20sheet%20for%20printing...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20also%20use%20this%20for%20quick%20dashboards%20-%20multiple%20sheets%20with%20.iqy's%20attached%2C%20and%20a%20tab%20that%20does%20pivot%20tables%20on%20the%20data%20from%20those%20sheets.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EWhen%20you%20open%20an%20.iqy%20file%2C%20it%20will%20open%20Excel.%20Leave%20Excel%20open.%20When%20you%20open%20the%20second%20.iqy%2C%20it%20will%20ask%20you%20where%20you%20want%20to%20put%20it%20-%20tell%20it%20to%20use%20another%20sheet%20in%20the%20open%20workbook.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-853261%22%20slang%3D%22en-US%22%3ERe%3A%20SP%20lists%20export%20-%20.iqy%20files%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-853261%22%20slang%3D%22en-US%22%3EThank%20you%2C%20that%20worked%20perfectly%3C%2FLINGO-BODY%3E
Contributor

I'm wanting to put several .iqy files in a single workbook but not sure if this is possible.  I can achieve the same thing by just creating multiple connections on multiple work sheets but that way takes forever when refreshing.  When I refresh .iqy files, they are finished in seconds

 

So, anyway to export multiple SP lists and then merge them into a single workbook?

2 Replies
Best Response confirmed by Bryan123 (Contributor)
Solution

@Bryan123 I do this in SharePoint 2013 on premise and it worked fine. I've got an asset checkout/checkin form. Three different lists control the formatting of the form, so I've got 3 tabs, each with an .iqy creating the tables. I set the spreadsheet to automatically refresh connections on startup.

 

Then I added a tab at the beginning that runs through all the data as needed (in VBasic), allowing a user to select a name, then I fill in the blanks and format the sheet for printing...

 

I also use this for quick dashboards - multiple sheets with .iqy's attached, and a tab that does pivot tables on the data from those sheets.

 

When you open an .iqy file, it will open Excel. Leave Excel open. When you open the second .iqy, it will ask you where you want to put it - tell it to use another sheet in the open workbook.

Thank you, that worked perfectly