Extract and paste particular column from excel to new one

Dear All,

I have 50+ files (Excel) with 4 or 5 working sheets in it, e.g Main, Jan, Feb, Mar, Apr etc. from each sheet in particular column (Fixed) e.g Customer Number in A1), I need to copy the entire column, again there will be gabs in numbers row wise, all visible numbers to copied and pasted in new excel sheet, the task has to performed for 50+ files (Loop through).

In total 50* 3 sheets = 150 Sheets to be processed, all the customer numbers to be populated in to separate excel sheet.

If some one can help me to fix this will be appreciated.

Kind regards
Satish

I find I often get better and more useful answers when I ask a specific PowerShell question, rather than just stating a need and hoping someone posts a complete script ;).

Have you tried something in particular and gotten stuck, for example? If there’s a specific question, it would be easier to try and aim for an answer.

Note that programming Excel from PowerShell isn’t easy all the time. The Excel programmability bits are old, and imperfectly compatible. It would probably be easier to do this entirely inside Excel, using VBA, and Microsoft has several excellent tutorials and example sets for that.

Hi Jones,

I am not use to power shell, I have tried with SSIS but its not working for me, so thought to give a try with google, most of the options are feasible with power shell.

Well, as we’re all volunteers with existing full-time jobs, I’m not sure anyone’s going to be able to jump in and just tell you how to do it. We’re happy to answer specific questions, if you have one, though! I would personally use VBA entirely within Excel, as it would be easier than PowerShell.

Good luck!

i’ve done a bit of excel work, mostly combining csv files into an excel output.

take a look at the module here:
https://blogs.technet.microsoft.com/heyscriptingguy/2015/11/25/introducing-the-powershell-excel-module-2/

its what i’m using