Welcome › Forums › General PowerShell Q&A › How to import specific column from all the excel files and export it as a table
This topic contains 4 replies, has 3 voices, and was last updated by

AuthorPosts

April 8, 2018 at 12:32 am #98223
I have several excel file, I just want to copy the particular column from all sheet to new workbook. Below code is not working for me, Any help will be appreciated.
excel_test = NewObject ComObject Excel.Application
$excel_test.visible =$false
$excel_test.DisplayAlerts =$true$excelFiles = GetChildItem Path C:\Users\SSampath\Desktop\Test Include *.xlsx Recurse
Foreach ($file in $excelFiles)
{
$workbook = $excel_test.Workbooks.open($file)
$Worksheet = $Workbook.WorkSheets.item("Sheet1")
$range = $WorkSheet.range("A1:AC1").EntireColumn
$averages=$range.Copy("A1").EntireColumn
$averages.SaveAs("C:\Users\SSampath\Desktop\Test\Summary.xlsx") 
April 8, 2018 at 1:16 am #98224
Not precisely sure, but this doesn't look right:
$range = $WorkSheet.range("A1:AC1").EntireColumn
A1:AC1 doesn't seem like a valid range. In Excel itself, A:A is used to refer to an entire column.

April 8, 2018 at 8:36 am #98235
Joel – you can of course in Excel have multichar columns in Excel.
Columns got from A – Z of course, but when you past Z, they start over with A[AZ].
If you'd exceed that, then it's B[AZ]... and so on.For Example (here is a sum of row A out to 52 columns): '=SUM(A1:AZ1)' the results is 52
This is a left to right thing, not a left to right and top to bottom of each column.It is normally unusual to see, but some I have seen many from accountants with massive workbooks/spreadsheets.
So, you are correct in saying the range the OP is using is not correct, but it is because the OP is not asking for the column range but the row range.
So, this is really not a PoSH issue, but how the OP needs to call the Excel DOM which the OP needs to work through.
So, the OP must collect all rows and columns.
Example: "=SUM(A1:AZ3)", which in my sample XLS is 156
Of course that 3*52 (row & columns) in that single row.See this post.
Read and Get Values from Excel File using PowerShell Script
http://mekalikot.blogspot.com/2014/08/readandgetvaluesfromexcelfile.htmlOr looking at the Excel docs and leverage the Range function from Excel.
More simply the OP could just do this: "=SUM(A:AZ)"
Not adding the Row number, means use the whole column.One can easily see this by doing the selection natively in Excel and copy and past the formula.

April 8, 2018 at 9:06 am #98236
Hi I got hundreds of Excel file which cant easily copy and paste the formula, I am looking for some solution to do this.

April 8, 2018 at 7:27 pm #98272
Understood, but you'd only need to do what I suggest above on one of them, if they are all exactly the same format / layout.
Yet, again, this is an Excel issue not a PoSH one, though you are using PoSH to automate this. You still need to fully understand / work the Excel DOM to get what you are after.
So, this means:
 knowing how each workbook is different and what if any builtin ranges are already there and using those range names.
 Using the Excel DOM to dynamically create your own named ranges based in the number of rows / columns in the workbooks.
 What about any nested/multisheet workbooks in the files set that you'd have to deal with.
Etc...


AuthorPosts
The topic ‘How to import specific column from all the excel files and export it as a table’ is closed to new replies.