10/21/2008  12:55 PM    <DIR>          CONFIR~4     ConfirmTHEOrder

10/17/2008  09:39 AM    <DIR>          CONFIR~3     ConfirmTHEClub

10/17/2008  09:39 AM    <DIR>          CONFIR~2     ConfirmTHEClubUS

10/21/2008  09:25 AM    <DIR>          CONFIR~1     ConfirmTHEOrders

What would happen if another directory was created between THEOrder and ConfirmTHEOrders?

So I did a little looking around and found this.

Set objFile=objFSO.GetFile(strPath1)
    strCMD1="Wscript " &  objFile.ShortPath

That's right, I put the fully quallified path in strPath1 and it returns the short path name.

Too cool.

Here is the sample I found by Jeffery Hicks for wrapping up powershell in a VBScript:

Dim objShell,objFSO,objFile

Set objShell=CreateObject("WScript.Shell")
Set objFSO=CreateObject("Scripting.FileSystemObject")

'enter the path for your PowerShell Script
'strPath="e:\documents and settings\jhicks\my documents\scripts\posh\Get-DiskSize.ps1?

'verify file exists
If objFSO.FileExists(strPath) Then
'return short path name
    set objFile=objFSO.GetFile(strPath)
    strCMD="powershell -nologo -command " & Chr(34) & "&{" &_
     objFile.ShortPath & "}" & Chr(34)
    'Uncomment next line for debugging
    'WScript.Echo strCMD

    'use 0 to hide window, True makes it wait until it's finishes.
    objShell.Run strCMD,0, True

Else

'Display error message
    WScript.Echo "Failed to find " & strPath
    WScript.Quit

End If

In case you are wondering; the Chr(34) puts a double quote in the string.

Yet another way of dealing with quotes.

Or in Pure Powershell:

$a = New-Object -ComObject Scripting.FileSystemObject $f = $a.GetFile("C:\Program Files\Internet Explorer\ExtExport.exe") $f.ShortPath

Which answered the question, How do I attach a file?

[System.Net.Mail.Attachment] $attachment = new-object
System.Net.Mail.Attachment $file

It is necessary to cast the $attachment variable to type
System.Net.Mail.Attachment, since new-object (in this instance)
returns a REFERENCE to an object of that type, not an ACTUAL OBJECT of
the type. But $msg.Attachments.Add($attachment) requires an actual
object. Hence the need for the cast.

from Shay Levy
Windows PowerShell MVP
http://blogs.microsoft.co.il/blogs/ScriptFanatic
PowerShell Toolbar: http://tinyurl.com/PSToolbar

To add 2 recipients
$msg.To.Add("te...@domain.com")
$msg.To.Add(te...@domain.com)
In the same way add cc and bcc:
$msg.cc.Add(...)
$msg.bcc.Add(...)

Yes I do use VBScript, a lot. I was recently asked to pull a record of all the scheduled tasks on a Windows 2000 server.

All errors and mistakes are my own. Thanks to all who have helped
along the way.

________________________________________________________________________

How do I create an Excel object?

$xl = new-object -comobject excel.application

The Scripting Guys have a column about tab expansion with an Excel
example.
________________________________________________________________________

How do I make Excel visible?
$xl.Visible = $true

________________________________________________________________________

How do I add a workbook?

$wb = $xl.Workbooks.Add()

________________________________________________________________________

How do I add a worksheet?

$xl = new-object -comobject excel.application

$xl.Visible = $true
$wb = $xl.Workbooks.Open("C:\Scripts\ado_object_model.xls")
$ws = $xl.Sheets.Add()

________________________________________________________________________

How do I change the value of the active cell?

$xl.ActiveCell.Value2 = "x"

________________________________________________________________________

How do I change the value of a specified cell?

$xl.ActiveSheet.Range("B1").Value2 = "y"

________________________________________________________________________

How do I list the workbook's name?

$wb.Name

________________________________________________________________________

How do I loop through a range of cells by row number?

$xl = new-object -comobject excel.application
$xl.Visible = $true
$wb = $xl.Workbooks.Add()
$ws = $wb.Worksheets.Item(1)

for ($row = 1; $row -lt 11; $row++)
{
    $ws.Cells.Item($row,1) = $row

How do I write a list of files to Excel?

$xl = new-object -comobject excel.application
$xl.Visible = $true
$wb = $xl.Workbooks.Add()
$ws = $wb.Worksheets.Item(1)

$row = 1
$s = dir
$s | foreach -process `
{ `
    $ws.Cells.Item($row,1) = $_; `
    $row++ `

How do I write a list of processes to Excel?

function Release-Ref ($ref) {

#[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($ref)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

}

# -----------------------------------------------------

$xl = New-Object -comobject Excel.Application

$xl.Visible = $True

$wb = $xl.Workbooks.Add()
$ws = $wb.Worksheets.Item(1)
$range = $ws.Cells.Item(1,1)

$row = 1
$s = Get-Process | Select-Object name
 $s | foreach -process {
    $range = $ws.Cells.Item($row,1);
    $range.value2 = $_.Name;
    $row++ }

$xl.DisplayAlerts = $False
$wb.SaveAs("C:\Scripts\Get_Process.xls")

Release-Ref $range

Release-Ref $ws

Release-Ref $wb

$xl.Quit()

Release-Ref $xl

***For a remote machine try

$strComputer = (remote machine name)
$P = gwmi win32_process -comp $strComputer

______________________________________________________________

How do I open a workbook?

#an existing Workbook
$xl.Workbooks.Open("C:\Scripts\ado_object_model.xls")

________________________________________________________________________

How do I open a new workbook?

$xl = new-object -comobject excel.application
$xl.Visible = $true
$wb = $xl.Workbooks.Add()
$xl.ActiveCell.Value2 = "x"
$xl.ActiveSheet.Range("B1").Value2 = "y"

________________________________________________________________________

How do I write the command history to Excel?

function Release-Ref ($ref) {

([System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComOb­ject]
$ref) -gt 0)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

$xl = New-Object -comobject Excel.Application

$xl.Visible = $True

$wb = $excel.Workbooks.Add()
$ws = $workbook.Worksheets.Item(1)
$range = $worksheet.Cells.Item(1,1)

$row = 1
$s = Get-History | Select-Object CommandLine $s | foreach -process { `
$range = $worksheet.Cells.Item($row,1); `
$range.value2 = $_.CommandLine; `
$row++ }

$xl.DisplayAlerts = $False
$wb.SaveAs("C:\Scripts\Get_CommandLine.xls")

Release-Ref $range

Release-Ref $ws

Release-Ref $wb

$xl.Quit()

Release-Ref $xl

________________________________________________________________________

How Can I Convert a Tilde-Delimited File to Microsoft Excel Format?

# Script name: ConvertTilde.ps1
# Created on: 2007-01-06
# Author: Kent Finkle
# Purpose: How Can I Convert a Tilde-Delimited File to Microsoft Excel
Format?

$s = gc C:\Scripts\Test.txt
$s = $s -replace("~","`t")
$s | sc C:\Scripts\Test.txt
$xl = new-object -comobject excel.application
$xl.Visible = $true
$wb = $xl.Workbooks.Open("C:\Scripts\Test.txt")

________________________________________________________________________

Add Validation to an Excel Worksheet

$comments = @'
Script name: Add-Validation.ps1
Created on: Wednesday, September 19, 2007
Author: Kent Finkle
Purpose: How can I use Windows Powershell to Add Validation to an
Excel Worksheet?
'@

#-----------------------------------------------------
function Release-Ref ($ref) {
([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
[System.__ComObject]$ref) -gt 0)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

$xlValidateWholeNumber = 1
$xlValidAlertStop = 1
$xlBetween = 1

$xl = new-object -comobject excel.application
$xl.Visible = $True

$wb = $xl.Workbooks.Add()
$ws = $wb.Worksheets.Item(1)
$r = $ws.Range("e5")
$r.Validation.Add($xlValidateWholeNumber, ` $xlValidAlertStop,
$xlBetween, "5", "10") $r.Validation.InputTitle = "Integers"
$r.Validation.ErrorTitle = "Integers"
$r.Validation.InputMessage = "Enter an integer from five to ten"
$r.Validation.ErrorMessage = "You must enter a number from five to
ten"

$a = Release-Ref $r
$a = Release-Ref $ws
$a = Release-Ref $wb
$a = Release-Ref $xl

________________________________________________________________________

Add a Chart to an Excel Worksheet

$xrow = 1
$yrow = 8
$xl = New-Object -c excel.application
$xl.visible = $true
$wb = $xl.workbooks.add()
$sh = $wb.sheets.item(1)
1..8 | % { $sh.Cells.Item(1,$_) = $_ }
1..8 | % { $sh.Cells.Item(2,$_) = 9-$_ }
$range = $sh.range("a${xrow}:h$yrow")
$range.activate
# create and assign the chart to a variable
$ch = $xl.charts.add()
$ch.chartType = 58
$ch.setSourceData($range)
$ch.export("C:\test.jpg")
$xl.quit()

# excel has 48 chart styles, you can cycle through all
1..48 | % {$ch.chartStyle = $_; $xl.speech.speak("Style $_"); sleep 1}
$ch.chartStyle = 27                          # <-- use the one you like

________________________________________________________________________

Sort a column in an Excel Worksheet

$xlSummaryAbove = 0
$xlSortValues = $xlPinYin = 1
$xlAscending = 1
$xlDescending = 2

# one-column sort --> works
[void]$range1.sort($range2, $xlAscending)
[void]$range1.sort($range3, $xlAscending)
# two-column sort --> doesn't sort both columns
# the 4th arg [xlSortType] gives problems so pass $null or '',
# it may be for PivotTables only
[void]$range1.sort($range2, $xlAscending, $range3, '', $xlAscending)

________________________________________________________________________

SubTotal a column in an Excel Worksheet

How to use xlConstants

$xlAutomatic=-4105
$xlBottom = -4107
$xlCenter = -4108
$xlContext = -5002
$xlContinuous=1
$xlDiagonalDown=5
$xlDiagonalUp=6
$xlEdgeBottom=9
$xlEdgeLeft=7
$xlEdgeRight=10
$xlEdgeTop=8
$xlInsideHorizontal=12
$xlInsideVertical=11
$xlNone=-4142
$xlThin=2

$xl = new-object -com excel.application
$xl.visible=$true
$wb = $xl.workbooks.open("d:\book1.xls")
$ws = $wb.worksheets | where {$_.name -eq "sheet1"}
$selection = $ws.range("A1:D1")
$selection.select()

$selection.HorizontalAlignment = $xlCenter
$selection.VerticalAlignment = $xlBottom
$selection.WrapText = $false
$selection.Orientation = 0
$selection.AddIndent = $false
$selection.IndentLevel = 0
$selection.ShrinkToFit = $false
$selection.ReadingOrder = $xlContext
$selection.MergeCells = $false
$selection.Borders.Item($xlInsideHorizontal).Weight = $xlThin
________________________________________________________________________

About autofill in excel

$xlFillWeekdays = 6

$xl = new-object -com excel.application
$xl.visible=$true
$wb = $xl.workbooks.add()
$ws = $wb.worksheets | where {$_.name -eq "sheet1"}
$range1= $sheet.range("A1")
$range1.value() = (get-date).toString("d")
$range2 = $sheet.range("A1:A25")
$range1.AutoFill($range2,$xlFillWeekdays)
$range1.entireColumn.Autofit()
# $wb.close()
# $xl.quit()

________________________________________________________________________

How to get a range in excel

# get-excelrange.ps1
# opens an existing workbook in Excel 2007, using PowerShell
# and turns a range bold # Thomas Lee - t...@psp.co.uk

# Create base object
$xl = new-object -comobject Excel.Application

# make Excel visible
$xl.visible = $true

# open a workbook
$wb = $excel.workbooks.open("C:\Scripts\xlsx1.xlsx")

# Get sheet1
$ws = $wb.worksheets | where {$_.name -eq "sheet1"}

# Make A1-B1 bold
$range = $ws.range("A1:B1")
$range.font.bold = "true"

# Make A2-B2 italic
$range2 = $sheet1.range("A2:B2")
$range2.font.italic = "true"

# Set range to a value
$range3=$ws.range("table1")
$Range3.font.size=24

# now format an entire row
$range4=$ws.range("3:3")
$range4.cells="Row 3"
$range4.font.italic="$true"
$range4.font.bold=$True
$range4.font.size=10
$range4.font.name="comic Sans MS"

# now format a Range of cells

$ws.Range("D1:F5").NumberFormat = "#,##0.00"
______________________________________________________________________

How do I add a comment to a cell in Excel

$xll = New-Object -com Excel.Application
$xl.visible = $True
$wb = $xl.Workbooks.Add()
$ws = $wb.Worksheets.Item(1)
$ws.Cells.Item(1,1) = “A value in cell A1.”
[void]$ws.Range("A1").AddComment()
[void]$ws.Range("A1").comment.Visible = $False
[void]$ws.Range("A1").Comment.text("OldDog: `r this is a comment")
[void]$ws.Range("A2").Select

The 'r adds a line feed after the comment's author. This is required!

________________________________________________________________________

How do I copy and Paste Special in Excel

        $xlPasteValues = -4163          # Values only, not formulas
        $xlCellTypeLastCell = 11        # to find last used cell

        $used = $ws.usedRange
        $lastCell = $used.SpecialCells($xlCellTypeLastCell)
        $row = $lastCell.row

        $range = $ws.UsedRange
        [void]$ws.Range("A8:F$row").Copy()
        [void]$ws.Range("A8").PasteSpecial(-4163)