1

I want to select and copy columns and rows in which data is present in new workbook.

I found this link which helped me just do the above. How to use powershell to copy several excel worksheets and make a new one?

But the only change i want is that in the progam given below they fetch the first 6 columns while i want to check for columns that have data and pick them up. Can anyone help me in that.

 #Get a list of files to copy from
$Files = GCI 'C:\Users\Matt\Documents\Excel Test\' | ?{$_.Extension -Match "xlsx?"} | select -ExpandProperty FullName

#Launch Excel, and make it do as its told (supress confirmations)
$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $True
$Excel.DisplayAlerts = $False

#Open up a new workbook
$Dest = $Excel.Workbooks.Add()

#Loop through files, opening each, selecting the Used range, and only grabbing the first 6 columns of it. Then find next available row on the destination worksheet and paste the data
ForEach($File in $Files[0..4]){
    $Source = $Excel.Workbooks.Open($File,$true,$true)
    If(($Dest.ActiveSheet.UsedRange.Count -eq 1) -and ([String]::IsNullOrEmpty($Dest.ActiveSheet.Range("A1").Value2))){ #If there is only 1 used cell and it is blank select A1
        [void]$source.ActiveSheet.Range("A1","F$(($Source.ActiveSheet.UsedRange.Rows|Select -Last 1).Row)").Copy()
        [void]$Dest.Activate()
        [void]$Dest.ActiveSheet.Range("A1").Select()
    }Else{ #If there is data go to the next empty row and select Column A
        [void]$source.ActiveSheet.Range("A2","F$(($Source.ActiveSheet.UsedRange.Rows|Select -Last 1).Row)").Copy()
        [void]$Dest.Activate()
        [void]$Dest.ActiveSheet.Range("A$(($Dest.ActiveSheet.UsedRange.Rows|Select -last 1).row+1)").Select()
    }
    [void]$Dest.ActiveSheet.Paste()
    $Source.Close()
}
$Dest.SaveAs("C:\Users\Matt\Documents\Excel Test\Book1.xlsx",51)
$Dest.close()
$Excel.Quit()

Thanks in advance Moe

Community
  • 1
  • 1
user3898271
  • 35
  • 2
  • 3
  • 7

1 Answers1

0

Use $rowMax = ($sheet.UsedRange.Rows).count to check the number of rows that has values. Loop through that $rowMax and store the values in a variable.

Check my post Get Excel Values

Mekalikot
  • 327
  • 2
  • 8