El código es el siguiente:
Código
$sesioncsv= 'C:\temp\sesiones.csv' $sesionxlsx= 'C:\temp\sesiones.xlsx' $correoscsv= 'C:\temp\correos.csv' $correosxlsx= 'C:\temp\correos.xlsx' $processes= Import-Csv -Path $correoscsv $Excel = new-object -ComObject excel.application $Excel.Visible = $true $workBook = $Excel.Workbooks.Add() $excel.cells.item(1,1) = "SamAccountName" $excel.cells.item(1,2) = "Name" $i = 2 foreach($process in $processes) { $excel.cells.item($i,1) = $process.samaccountname $excel.cells.item($i,2) = $process.name $i++ } $workBook.saveas($correosxlsx) $Excel.Quit() Remove-Variable -Name excel Remove-Variable -Name i Remove-Item -path $correoscsv $processes= Import-Csv -Path $sesioncsv $Excel = new-object -ComObject excel.application $Excel.Visible = $true $workBook = $Excel.Workbooks.Open($correosxlsx) $workSheet = $Excel.WorkSheets.Add() $worksheet.name = 'sesiones' $excel.cells.item(1,1) = "ServerName" $excel.cells.item(1,2) = "SessionId" $excel.cells.item(1,3) = "UserName" $excel.cells.item(1,4) = "Name" $excel.cells.item(1,5) = "DomainName" $excel.cells.item(1,6) = "ServerIPAddress" $excel.cells.item(1,7) = "TSProtocol" $excel.cells.item(1,8) = "ApplicationType" $excel.cells.item(1,9) = "ResolutionWidth" $excel.cells.item(1,10) = "ResolutionHeight" $excel.cells.item(1,11) = "ColorDepth" $excel.cells.item(1,12) = "CreateTime" $excel.cells.item(1,13) = "DisconnectTime" $excel.cells.item(1,14) = "SessionState" $excel.cells.item(1,15) = "CollectionName" $excel.cells.item(1,16) = "CollectionType" $excel.cells.item(1,17) = "UnifiedSessionId" $excel.cells.item(1,18) = "HostServer" $excel.cells.item(1,19) = "IdleTime" $excel.cells.item(1,20) = "RemoteFxEnabled" $i = 2 foreach($process in $processes) { $excel.cells.item($i,1) = $process.servername $excel.cells.item($i,2) = $process.sessionid $excel.cells.item($i,3) = $process.username $excel.cells.item($i,4).Formula = '=VLOOKUP(C2,Hoja1!A:B,2,"FALSO")' $excel.cells.item($i,5) = $process.domainname $excel.cells.item($i,6) = $process.serveripaddress $excel.cells.item($i,7) = $process.tsprotocol $excel.cells.item($i,8) = $process.applicationtype $excel.cells.item($i,9) = $process.resolutionwidth $excel.cells.item($i,10) = $process.resolutionheight $excel.cells.item($i,11) = $process.colordepth $excel.cells.item($i,12) = $process.createtime $excel.cells.item($i,13) = $process.disconnecttime $excel.cells.item($i,14) = $process.sessionstate $excel.cells.item($i,15) = $process.collectionname $excel.cells.item($i,16) = $process.collectiontype $excel.cells.item($i,17) = $process.unifiedsessionid $excel.cells.item($i,18) = $process.hostserver $excel.cells.item($i,19) = $process.idletime $excel.cells.item($i,20) = $process.remotefxenabled $i++ } $workBook.saveas($sesionxlsx) Remove-Variable -Name excel Remove-Variable -Name i Remove-Item -path $correosxlsx Remove-Item -path $sesioncsv Set-ExecutionPolicy Restricted exit
En principio tengo localizado el error:
Código
$excel.cells.item($i,4).Formula = '=VLOOKUP(C2,Hoja1!A:B,2,"FALSO")'
Tengo que cambiar ese C2 por algo que cambie por cada fila, es decir (C2, C3, C4,...) asi hasta la ultima fila que genere.
(Por temas de RGPD no puedo mostrar como saco el contenido de cada csv, pero bueno, creo que se sobreentiende viendo cada foreach que es lo que saca xD)