myITforum.com : One step further - to open a working range in excel worksheet

Here I will extend a little bid further from my two previous posts and to open a working range inside the target worksheet.

This article was Previously posted on Ying Li's Blog

################################################################################

$excel = new-object -comobject Excel.Application
$excel.visible = $True

# Inputbox
$x = new-object -comobject MSScriptControl.ScriptControl
$x.language = "vbscript"
$x.addcode("function getInput() getInput = inputbox(`"Enter the full path of your excel file`",`"Workbook Name`") end function" )
$excelfilename = $x.eval("getInput")

$x = new-object -comobject MSScriptControl.ScriptControl
$x.language = "vbscript"
$x.addcode("function getInput() getInput = inputbox(`"Enter the worksheet name`",`"WorkSheet Name`") end function" )
$worksheetname = $x.eval("getInput")

$x = new-object -comobject MSScriptControl.ScriptControl
$x.language = "vbscript"
$x.addcode("function getInput() getInput = inputbox(`"Enter the Range name`",`"Working Range Name`") end function" )
$rangename = $x.eval("getInput")

function global:Open-Range()
{
param($excel, [string]$excelfilename, [string]$worksheetname,[string]$rangename)
if (!$(test-path $excelfilename))
{
write-host "File doesn't exist..."
return $null
}
if ([string]::IsNullOrEmpty($worksheetName))
{
write-host "Worksheet name cannot be null or empty."
return $null
}
if ([string]::IsNullOrEmpty($rangeName))
{
write-host "Range name cannot be null or empty."
return $null
}
$worksheet = $($excel.Workbooks.Open($excelfilename)).Worksheets.Item($worksheetName)
$range = $worksheet.Range($RangeName)
}

Open-Range $excel $excelfilename $worksheetname $rangename

#################################################################################

Now we have the target range open, we could manipulate the data, create an array out of certain column, do some comparision, update the open excel sheet and save the results - to be continued…


		myITforum.com ©2010 \| Legal \| Privacy