PowerShell script to open target worksheet in target workbook
In my previous post, I wrote a script to open an excel workbook, now I extend that function a little bid to open your target worksheet in excel workbook.
$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")
function global:Open-Worksheet()
{
param($excel, [string]$excelfilename, [string]$worksheetname)
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
}
$worksheet = $($excel.Workbooks.Open($excelfilename)).Worksheets.Item($WorksheetName)
}
Open-Worksheet $excel $excelfilename $worksheetname