Hello everyone,
I’ve been trying to figure out how to read from an Excel file to output line by line from an entire row, to a text file, or another Excel file. Ideally, I’m trying to make a script that Reads an Excel File (Which contains computernames, filepaths, and other useless info) to Test-Path the computer name \Computername\Filepath, and if valid output to the text file, or output to Another Excel file. From there If I can figure out the reading of an entire column, I can hopefully set each for a variable and send a Remove-Item “\$Computername\Filepath” … But that’s much further down the road. I haven’t been able to figure out the key details of manipulating XLSX files with PowerShell (partly because I just started learning it a week ago and don’t know any other type of code/script language)
If anyone could point me in the direction of reading an entire column and outputting it to another excel file IDEALLY or a text file is fine I would greatly appreciate it. Also if anyone has any valuable resources to read upon/understand excel manipulation in PowerShell, along with objects, variables(like $_, and the like since I’m basically stupid at this)
This is what I have so far, I know it looks bad but if anyone could look it over, any advice is appreciated.
[pre]
$xl= New-Object -ComObject “Excel.Application”
$xl.Visible = $true
$wb = $xl.Workbooks.Open(“C:\Users\TEST\Desktop\Test.xlsx”)
$ws = $wb.WorkSheets.item(“Sheet1”)
$usedrange = $ws.UsedRange
$rowcount = $usedrange.rows.count
$ws.activate()
$range = $ws.Range(“A1:A$rowcount”).text
$range | Out-File $env:USERPROFILE\Desktop\Text.txt
[/pre]
Appreciate any help.
-Sushi