Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Question
Tuesday, June 15, 2010 2:51 PM
Hi everyone. Again, I am having trouble working with Excel via Powershell. I am slowly learning my way around but cannot correctly utilize any information I may find that helps me get the correct results. So far I am working on a project at work that is automating the testing of our build process. I am "borrowing" an excel sheet that I will save to my local machine to save as CSV. However, before saving to CSV I need to remove the empty rows. Unfortunately, the cells are not really blank but still contain links to the server I pulled the workbook from.
I'm not sure what is easiest to do, but the "blanks" are within this range: A49:F320 and this is the range I'd like to delete. The code I am currently working with is:
$Excel = new-object -comobject excel.application
$Excel.Visible = $False
$Workbook = $Excel.Workbooks.Open($BuildXLS)
$Worksheet = $Workbook.Worksheets.Item(1)
$i = 1
If ($Worksheet.Cells.Item($i, 1).Value() -eq " ") {
$Range = $Worksheet.Cells.Item($i, 1).EntireRow
$a = $Range.Delete()
$i -= 1
}
$i += 1
Incidentally, mjolinor helped with an earlier issue parsing through the CSV. It was only after looking at the output that I discovered my real issue is working with the Excel.
$data = import-csv $csv | Where-Object {$_.juris -ne " "}
#format Juris-Version results
foreach ($line in $data)
{
if ($line.juris -eq 'US'){$line.Version = $FedVerNum}
else {$line.Version = $ContentVer}
write-output $line.juris$line.version | Out-File -Append "C:\1_JurisVersion.txt"
}
The output from that help session looks like:
US
$FedVerNum
State
$ContentVer
State
$ContentVer
Is there a away I can get this to read as
US $FedVerNum
state $ContentVer
state $ContentVer
state $ContentVer
Many thanks for the help and expertise!
All replies (12)
Tuesday, June 15, 2010 4:45 PM ✅Answered | 1 vote
Give this a try:
$xlCellTypeLastCell = 11
$xl = New-Object -comobject excel.application
$xl.Visible = $true
$xl.DisplayAlerts = $False
$wb = $xl.Workbooks.Open("C:\Scripts\BuildXLS.xls") # <-- Change as required!
$ws = $wb.Worksheets.Item(1)
$used = $ws.usedRange
$lastCell = $used.SpecialCells($xlCellTypeLastCell)
$row = $lastCell.row
for ($i = 0; $i -le $row; $i++) {
If ($ws.Cells.Item($i, 1) = " ") {
$Range = $ws.Cells.Item($i, 1).EntireRow
$Range.Delete()
$i = $i - 1
}
}
Check my Excel Cookbook at http://olddogsblog.spaces.live.com
Tuesday, June 15, 2010 3:11 PM
Hi,
Write-Output displays one argument per line.
You have two solutions.
First one (using Write-Output) : Write-Output ($line.juris + ' ' + $line.version) | Out-File -Append 'C:\1_JurisVersion.txt'
Second one (Without Write-Output) : Out-File -Append 'C:\1_JurisVersion.txt' -InputObject ($line.juris + ' ' + $line.version)
Hope this helps.
Grégory Schiro - PowerShell MVP - PowerShell & MOF
Tuesday, June 15, 2010 5:36 PM
Thanks Gregory for the quick response-that did the trick!
Tuesday, June 15, 2010 6:52 PM
OldDog1!! I stumbled across your blog a few days ago before starting this madness--thanks for responding and re-directing me [back] to it, I will now bookmark it for further reference!
I ran the above after setting $wb.visible to false and received the following error message:
Exception setting "Item": "Exception from HRESULT: 0x800A03EC"
At :line:13 char:22
+ If ($ws.Cells.Item <<<< ($i, 1) = " ") {
Because this is a gentle push for me in the right direction, I edited your suggestion to use $Range = $ws.range("A49:F320"), $Range.Delete()
in place of the for loop
I also included
$xl.displayalerts=$False
$wb.SaveAs($BuildXLS)
$xl.quit()
if (ps excel) { kill -name excel} (quit() and ps kill does not seem to have an effect)
this yields:
Exception calling "SaveAs" with "1" argument(s): "Cannot access read-only document 'build.xls'."
At :line:24 char:10
+ $wb.SaveAs <<<< ($BuildXLS)
About an hour or so ago, I was having an issue with a previously working script--my SaveCSV.ps1. Turns out, the folder permissions were automagically set to read-only(I'm running XP SP3) Hopefully, this is resolved as I am now able to run the script and see the CSV file(with the correct format--thanks to Gregory!)
Is there a way around the read-only file issue?
Tuesday, June 15, 2010 7:24 PM
I apologize for the hasty response, I worked around the read-only issue by setting a variable equal to $BuildXLS and using that in place of
$wb.SaveAs($NewXLS)
Thanks again for the quick replies and helpful code!!
Thursday, June 17, 2010 3:54 PM
Hi,
How did you determin this?
$Range = $ws.range("A49:F320"), $Range.Delete()
I tried:
If ($ws.Cells.Item($i, 1) = $Null)
And:
If ($ws.Cells.Item($i, 1) = "")
And this:
If ($ws.Cells.Item($i, 1) = " ")
And my script deleted all the rows or none of them.
Then I tried this:
$xlCellTypeLastCell = 11
$xl = New-Object -comobject excel.application
$xl.Visible = $true
$xl.DisplayAlerts = $False
$wb = $xl.Workbooks.Open("C:\Scripts\Test.xls") # <-- Change as required!
$ws = $wb.worksheets | where {$_.name -eq "Servers" }
$used = $ws.usedRange
$lastCell = $used.SpecialCells($xlCellTypeLastCell)
$row = $lastCell.row
for ($i = 1; $i -le $row; $i++) {
If ($ws.Cells.Item($i, 1).Value() -eq $Null) {
$Range = $ws.Cells.Item($i, 1).EntireRow
$Range.Delete()
}
}
And it worked. The trick in finding an Empty Cell is:
If ($ws.Cells.Item($i, 1).Value() -eq $Null)
OldDog
Tuesday, June 22, 2010 5:58 PM
Sorry - I missed your question--I determined the range manually. The empty rows were not really empty but still contained link info to the server I saved from. Because the range is pretty static and will not change from build to build, this seemed the best way to go.
Friday, April 8, 2011 8:29 AM
How did you determin 11 as a value for the below stmt
$xlCellTypeLastCell = 11
Friday, April 8, 2011 3:07 PM
The Excel constants are "magic" numbers that can be found in Excel via the Object Browser in the Visual Basic Editor.
There are several that I use all the time, so I made a list of the ones I use most often:
Const ForReading = 1
Const xlAscending = 1
Const xlGuess = 0
Const xlTopToBottom = 1
Const xlSortNormal = 0
Const xlCount = -4112
Const xlArea = 1
Const xlBar = 2
Const xlColumn = 3
Const xlLine = 4
Const xlPie = 5
Const xlRadar = -4151
Const xlXYScatter = -4169
Const xlCombination = -4111
Const xl3DArea = -4098
Const xl3DBar = -4099
Const xl3DColumn = -4100
Const xl3DLine = -4101
Const xl3DPie = -4102
Const xl3DSurface = -4103
Const xlDoughnut = -4120
Const xlPasteValuesAndNumberFormats = 12
Const xlpasteFormats = -4122
Const xlpasteValues = -4163
Const xlValues = &HFFFFEFBD ' -4163
Const xlNone = &HFFFFEFD2 ' -4142
Const xlSelect = 3
Const xlCellTypeLastCell = 11
Or you can "Include" them like this:
[reflection.assembly]::loadWithPartialname("Microsoft.Office.Interop.Excel") |
Out-Null
$xlConstants = "microsoft.office.interop.excel.Constants" -as [type]
$ws.columns.item("F").HorizontalAlignment = $xlConstants::xlCenter
$ws.columns.item("K").HorizontalAlignment = $xlConstants::xlCenter
If you know what constants you need.
You can read about this here:
http://technet.microsoft.com/en-us/magazine/2009.01.heyscriptingguy.aspx
Sunday, February 9, 2014 11:00 AM
Hi OldDog1,
While I was working on a project , I came across this thread and found it very helpfull, but I want to mention here that $NULL works fine!
Like continue to delete columns unless I want empty col,
while ($ws.Cells.Item(1,5).Value() -ne $NULL )
{
Write-Host "find last.."
$Range = $ws.Cells.Item(1,5).EntireColumn
$Range.Delete([Microsoft.office.interop.excel.xldeleteshiftdirection]::xlShiftToLeft)
}
Tuesday, June 3, 2014 4:16 PM
Very interesting, thank you
Warm Fuzzies!
Tuesday, June 5, 2018 8:06 AM
It's works fine on small files, but I have problem with removing line in large files, $Range.Delete() returns true, it's looks fine but when I save file as csv:
$n = $excelFileName.Replace(".xlsx",".csv")
$ws.SaveAs($n, [Microsoft.Office.Interop.Excel.XlFileFormat]::xlCSV)
it has still all lines :/