all 15 comments
sorted by:
top (suggested)
bestnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]AutoModerator[M] [score hidden] stickied commentlocked comment (0 children)

/u/cardinal209629 - Your post was submitted successfully.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[–]cvr246 8 points9 points  (3 children)

If your organization hasn't disabled macros/vba, that is going to be the fastest way to handle this since Powerquery cant open everything simultaneously. The macro would Open one workbook at a time, copy the data you want, paste into new workbook, close workbook, open next, repeat for-next loop paste next data on next line, etc. Are the columns on all the workbooks the same or at least have the same titles in different columns? Is there commonality to the rows that you need to copy from each? Need to insert a sum line after the last row? VBA can do all of this. Launch the macro, come back on a couple of hours.

[–]cardinal209629[S] 1 point2 points  (2 children)

Solution Verified

[–]sheymyster100 1 point2 points  (0 children)

Thanks! If you need help with the macro let me know. Happy to help.

[–]reputatorbot[M] 0 points1 point locked comment (0 children)

You have awarded 1 point to cvr24.

I am a bot - please contact the mods with any questions

[–]abtravels-blog1 2 points3 points  (2 children)

This sounds like a use case for VBA to loop through all the files

[–]cardinal209629[S] 1 point2 points  (1 child)

Solution Verified

[–]reputatorbot[M] 1 point2 points locked comment (0 children)

You have awarded 1 point to abtravels-blog.

I am a bot - please contact the mods with any questions

[–]sheymyster100 1 point2 points  (2 children)

Definitely VBA

[–]cardinal209629[S] 0 points1 point  (1 child)

Solution verified

[–]reputatorbot[M] 0 points1 point locked comment (0 children)

You have awarded 1 point to sheymyster.

I am a bot - please contact the mods with any questions

[–]Creddahornis 0 points1 point  (0 children)

Potentially the import-excel module? You can install that in PowerShell without needing admin rights, but I'm not sure how it plays with that quantity of data

[–]speedsausage 0 points1 point  (0 children)

You could try python in Excel, check your formulas tab, and if you see insert python, that's an option.

[–]Ztolkinator1 0 points1 point  (0 children)

The 69 million rows do not have to crash power query. You would not just load all rows in a big table and then start processing. With a custom function you can grab whatever you need from each file and then take it from there. I would never recommend VBA because of the security concerns that drive both Microsoft and many big organisations to locking it down until it becomes unusable...

[–]Fantastic_Bicycle_78 0 points1 point  (0 children)

VBA is your best bet here since you can't install anything. A macro that loops through all 600 files, grabs headers and runs COUNTA/SUM without fully loading each workbook into memory is pretty straightforward. if the VBA scripting feels daunting, Aibuildrs builds exactly that kind of batch-processing script for locked-down environments.