Combine excel sheets without opening

[jay2vel]

I have set of excel files in a folder with 52(week names) sheets in each workbook, e,g have 10 workbooks.
each week need to enter data in the particular week sheet.

i would like to consoildate, particular week sheet data (eg WK22 sheet ) as combine one as in particular location workbook sheet.
this has to be done without opening excel file.

kindly support & let me know any additional information required.

 

[Joe4]

Welcome to the Board!

this has to be done without opening excel file.

Why?
The typical way of doing this is creating a macro that will open the files, copy the data, and then close the files. It doesn't involve you having to open the files manually, the macro will do it.

The only way I know of how to do it without opening the file is to NOT copy the data, but to link to it. But in order to do that, you would need to know (without looking at the file) the exact location on each sheet where you want to copy values so you can populate the sheets with linking formulas to the other sheets. Having 52 sheets filled with linking formulas will probably be very slow and inefficient, unless you then convert all those linking formulas to hard-coded values.

 

[sandy666]

this has to be done without opening excel file.

Look at PowerQuery (Get&Transform) with From Folder feature

 

[Joe4]

Hey Sandy,

I have not been able to use PowerQuery yet, but I am curious. Can it get data from other files without opening them up?
If so, how does it do that?

Thanks

 

[sandy666]

Hi Joe,

How to explain
It works in "background", doesn't open the file but "importing" data to the new file in PQ editor where you can do whatever you want.
PQ uses ggost data/file (like temporary file) but if normal temporary file you can see with ~ sign the PQ temporary file you can't
All the time you work on a temporary copy and not on the original
I can make video but imho it doesn't make sense because you'll see result of the import , nothing more

edit:
in short: PQ doing in background temporary copy (link to) of the file / data without opening original file

 

[Joe4]

Very interesting and good to know.
Thanks for explaining it.

 

[sandy666]

My explanation is very poor
The best way is try it in practice somewhere

it's something like: Data - From Access where you don't need to open Access file but you can see data from there after import

Fzrom Microsoft:

Microsoft Power Query provides a powerful “get data” experience that encompasses many features. A core capability of Power Query is to filter and combine, that is, to “mash-up” data from one or more of a rich collection of supported data sources. Any such data mashup is expressed using the Power Query Formula Language (informally known as "M"). Power Query embeds M documents in Excel and Power BI workbooks to enable repeatable mashup of data.

 

[alansidman]

Joe,
Ken Puls and Miguel Escobar have written a really good book on PQ called M is for (Data) Monkey. PQ is fairly easy to learn the basics and you can do a lot of data manipulation with it that is very quick.

Alan

 

[Joe4]

Ken Puls and Miguel Escobar have written a really good book on PQ called M is for (Data) Monkey. PQ is fairly easy to learn the basics and you can do a lot of data manipulation with it that is very quick.

Thanks. It is one of those things I should learn and play around with someday...

 

[sandy666]

here is a link to What is Power Query
there is many information and examples from very simply to more complicated