Powershell Megathread: Verb-Noun Scripting is the future

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Powershell Megathread: Verb-Noun Scripting is the future

Rabid Snake: Aug 6, 2004

Is there an easy way to send an email to a email alias when a script throws an error? We're using Office 365 to handle our exchange server so the SMTPclient class won't work for us. We've tried to use outlook but it throws security errors. Anyone have experience with using outlook to send an email with a log attachment file when the script throws an error?

# ¿ Aug 2, 2011 19:52

Adbot: ADBOT LOVES YOU

# ¿ May 2, 2024 02:23

Rabid Snake: Aug 6, 2004

Anyone have any experience with NetCmdlets and PowerShell? I'm trying to recursively download a directory in a FTP and than save it onto a local drive. The NetCmdlet's Get-FTP does not do recursive like Get-ChildItems so I wrote a simple script to try and get around that. It seems to fail when grabbing ChildItem5 due to scoping issues that I've been trying to iron out.

Parent
|
| |--ChildItem3
|--ChildFolder1-|--ChildFolder3--|-ChildItem5
|
|--ChildFolder2-|ChildItem4
|
|--ChildItem1
|--ChildItem2

Anyone write a script that can download FTP directories recursively?

EDIT:

Is it also possible to automate this?
http://blogs.technet.com/b/heyscriptingguy/archive/2008/11/10/how-can-i-check-spelling-and-grammar-in-microsoft-word.aspx

Instead of going through all spelling and grammar errors manually, is there a way to accept all suggestions?

Rabid Snake fucked around with this message at 21:54 on Aug 24, 2011

# ¿ Aug 24, 2011 21:25

Rabid Snake: Aug 6, 2004

Is there anyway to automate this process with Windows Office 2010? I want to copy a table from a Microsoft Word document and paste it in excel.

I wrote code that opens up a folder of .docx files, copies the whole table, opens up a specific Excel file, and than I'm stuck at appending (pasting) the table to the excel file. I'd assume that Powershell has access to the clipboard provided in Office 2010, correct?

# ¿ Dec 1, 2011 00:10

Rabid Snake: Aug 6, 2004

I've written a Powershell script that copies a table from Microsoft Word and pastes them to an excel document. I want to append it after the last used row in column A.

I got this code so far:
---
$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $True
$ExcelWordBook = $Excel.Workbooks.Open($ExcelPath)
$ExcelWorkSheet = $Excel.WorkSheets.item("Sheet1")
$ExcelWorkSheet.activate()

This is where I get confused, I want to be able to find the last row that is used in the Excel WorkSheet.

$lastRow = $ExcelWorkSheet.UsedRange.rows("A").count
$nextRow = $lastRow + 1
$ExcelWorkSheet.Range("A$nextRow").Select
$ExcelWorkSheet.Paste()

---
I feel like I'm so close after this. I was trying to use the UsedRange property in the Office Interlop to determine the last row in the Excel WorkSheet. (http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.worksheet.usedrange(v=vs.80).aspx) Any ideas? I feel like I'm using the UsedRange function incorrectly.

# ¿ Dec 9, 2011 23:19

Rabid Snake: Aug 6, 2004

adaz posted:

You were really close. There are also some other ways of doing it including using specialCells property on the range interface.

code:

$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $True
$ExcelWordBook = $Excel.Workbooks.Open($ExcelPath)
$ExcelWorkSheet = $Excel.WorkSheets.item("sheet1")
$ExcelWorkSheet.activate()


$lastRow = $ExcelWorkSheet.UsedRange.rows.count
$Excel.Range("A" + $lastrow + 1).Activate()
$ExcelWorksheet.Paste()

Appreciate the quick response and help! This works except when it appends, it leaves empty rows between the last piece of data and the appended piece of data. I guess it's including blank cells (that are active, but blank) in it's count. I'm going to try to look into this.

edit: it might be because I have three columns and it's counting all three columns' rows.

edit edit: appreciate the help a lot by the way, was stuck on this for quite some time. Donald Driver is my favorite receiver on the GB receiving corp :3:

Rabid Snake fucked around with this message at 00:20 on Dec 10, 2011

# ¿ Dec 10, 2011 00:17

Rabid Snake: Aug 6, 2004

Actually I just figured it out using your first solution! I changed this.

code:

$Excel.Range("A" + $lastrow + 1).Activate()

Say if the last row was A1. By adding that 1 in the end, it'd activate A11. The next last row was A30. It would than activate A301. And it'd go on creating this blank space. I ended up using:

code:

$lastRow = $ExcelWorkSheet.UsedRange.rows.count + 1
$Excel.Range("A" + $lastRow).Select()
$ExcelWorkSheet.Paste()

Thanks very much adaz for the original code!

# ¿ Dec 10, 2011 00:55

Rabid Snake: Aug 6, 2004

This is another odd question. Is it possible to use Powershell to remove al sentences flagged by the Microsoft Word Grammar Checker? Or delete all sentences not flagged by Word's grammar checker? I'm checking the Word Object Model (http://msdn.microsoft.com/en-us/library/bb225019(v=office.12).aspx) but can't really find anything that returns if a sentence is flagged by Word's grammar checker.

edit:

Found something real interesting!

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.word.range.gotonext.aspx

or more specifically
http://msdn.microsoft.com/en-us/library/microsoft.office.interop.word.wdgotoitem.aspx

Apparently the Range Interface provides a GoToNext method which contains a way to move the pointer to a grammar area. Time to mess with Powershell!

edit edit:

Found a way to find a grammatical incorrect sentence and I can delete it. The problem now is looping through the document. I can't seem to find a boolean value in the Office interop that returns true or false if the document still contains grammar errors...

Here's what I got so far

code:

cd c:\testruns\
$docPath = "" + $(Get-Location) + "\Grammar\document.docx"
$Word = New-Object -ComObject Word.Application
$Word.Visible = $True
$doc = $Word.documents.open($docPath)
$docSelection = $Word.selection

#WdGoToItem
$wdGoToSpellingError = 13
$wdGoToGrammaticalError = 14

#WdGoToDirection
$wdGoToFirst = 1
$wdGoToLast = -1
$wdGoToNext = 2
while (!$AnymoreGrammar) {
    [void]$docSelection.GoTo($wdGoToGrammaticalError, $wdGoToNext).delete()
}

I need a valid boolean test in the while loop...

Rabid Snake fucked around with this message at 23:27 on Dec 21, 2011

# ¿ Dec 21, 2011 22:23

Adbot: ADBOT LOVES YOU

# ¿ May 2, 2024 02:23

Rabid Snake: Aug 6, 2004

adaz posted:

Rabid Snake not sure I have to look at the office interop stuff for a word project i'm working on I'll see if I spot anything there tomorrow.

Found one! If someone in the near future, for some odd reason, has the same problem I had, I just used the GrammaticalErrors Property.

msdn.microsoft.com/en-us/library/aa213190(v=office.11).aspx

So my while loop looked like this

code:

$errorCount = $doc.GrammaticalErrors.Count
while ($errorCount -ne 0)

# ¿ Dec 26, 2011 16:15

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Powershell Megathread: Verb-Noun Scripting is the future