For all same value of first column value , all related values have to be moved in new file.
When i run the script , It randomly leaves some rows and throws this error.
Error is showing Exception Calling "Paste" with "0" argument(s).
Plz help
I need i urgently.
Thanks
Script::::
param($file="")
$working_directory=(pwd).tostring()
$filename=$working_directory+"\"+$file
#source file
$excels = new-object -comobject excel.application
$excels.Visible = $true
$wbs = $excels.Workbooks.open($filename) #$wb = $excel.Workbooks.Add()
$wss = $wbs.Worksheets.Item(1)
$row=2
while($wss.cells.item($row,1).value2 -ne $null)
{
#Destination file
$exceld = new-object -comobject excel.application
$exceld.Visible = $true
$wbd = $exceld.Workbooks.add()
$wsd = $wbd.Worksheets.Item(1)
#filling the first row
$range=$wss.Range("A1").entirerow.copy()
$wsd.Paste()
$currentserver=$wss.cells.item($row,1).value2
$server=$currentserver
$rowd=2
while($server -eq $currentserver)
{
$range=$wss.range("A"+$row).entirerow.copy()
#Pointing destination file to first column of 2nd row
$r = $wsd.Range("A"+$rowd)
$exceld.GoTo($r) | out-null
$wsd.Paste()
$rowd+=1
$row+=1
$currentserver=$wss.cells.item($row,1).value2
}
$dfilename=$working_directory+"\"+$server+".xlsx"
$wbd.SaveAs($dfilename)
#Nulling all remaining variables connected to Exceld
$Exceld.Quit()
$Exceld=$Null
$wbd=$null
$wsd=$null
#[GC]::Collect()
}
#Nulling all remaining variables connected to Excels
$Excels.Quit($false)
$Excels=$Null
$wbs=$null
$wss=$null
#finally releasing the Object Wrapper.
[GC]::Collect()