I have 30 xl files in one folder. Each xl file contains 1 query cold Sales. As the format of the Sales source varies queries are different, however the outputs of these queries are the same.
I would like to append all data resulting of the queries in an other query in an other files.
How can I call a query which is in another file ?
if I write Excel.Workbook(), I can see the tab but not queries...
(I could retrieve the queries in each file and then append... but it will be really heavy and I think we might find another solution...)
Hi Thyf,
Have you loaded each query output to a worksheet in these 30 Excel files? If so, you can use Get Files > From Folder to get them in a new workbook and consolidate them into one table before loading to the worksheet or data model.
Mynda
Hi Mynday
thanks a lot for your answer.
No, I did not because my file would become super heavy. I appreciate that it could be one solution but I was hopping it exists a formula which will disclose queries in a file.
thanks
Thierry
Your file won't become 'heavy' because the 30 Excel files will contain one query and one query output into a table in the worksheet. You then get all of the data from each of the 30 files in a new Excel file in a single query and load it to one worksheet or preferably, the data model. It will be a very light file this way.
Mynda
OK
not that simple because, I have 3 queries in each file for 3 different purposes... but I will do this
thanks for your help
Oh, ok. Originally you said "I have 30 xl files in one folder. Each xl file contains 1 query cold Sales."
