Unselect cells

[scottbass]

Hi,

Although the below is a Powershell script, my question is more about the Excel class model, finding the right method to call to get my desired results. Thus, I'm posting here. Please (gently) let me know if I should post elsewhere.

The below script is used to shrink an Excel file created in Excel XML (via SAS). The XML file is hugely bloated, and running Excel "in batch mode" as a COM object is very useful in shrinking this file. In the process, I'm also autofitting the data to the widest length in each column.

My desired result is to return to Sheet 1, cell A1, with all cells unselected. However, I can't seem to find the right search string in Google to find the correct Excel method to call.

Here is the Powershell script. Any suggestions are welcome!

Regards,
Scott

[CmdletBinding(SupportsShouldProcess=$true)]
param(
   # full path to the original Excel file (usually in XML format)
   [Parameter(
      Position=0,
      Mandatory=$true
   )]
   [Alias("Path")]
   [ValidateScript({Test-Path $_})]
   [System.IO.FileInfo]$xlFile
   ,
   # ouptput format
   [Parameter(
      Position=1
   )]
   [Alias("Format")]
   [ValidateSet("xlsx","xlsm","xlsb","xls")]
   [String]$xlFormat="xlsb"
)


# See http://technet.microsoft.com/en-us/library/ff730962.aspx
# or Google "ReleaseComObject Excel"
function release-comobject($ref) {
   while ([System.Runtime.InteropServices.Marshal]::ReleaseComObject($ref) -gt 0) {}
   [System.GC]::Collect()
}


$ErrorActionPreference="Continue"


$xlPath=$xlFile.DirectoryName
$xlBasename=$xlFile.Basename


switch ($xlFormat) {
   "xlsx" {$xlFormatNum=51; break}
   "xlsm" {$xlFormatNum=52; break}
   "xlsb" {$xlFormatNum=50; break}
   "xls"  {$xlFormatNum=56; break}
}


try {
   $Excel = New-Object -ComObject excel.application
   $Excel.visible = $False
   $Excel.displayalerts = $False
   $Workbook = $Excel.Workbooks.Open($xlFile)


   #=============================================================================#
   # autofit row/column                                                          #
   # before we save the file, call AutoFit to improve the appearance             #
   #=============================================================================#


   foreach ($Worksheet in $Workbook.Worksheets) {
      $range = $Worksheet.UsedRange
      $rc = $range.EntireColumn.AutoFit()
      $rc = $range.Rows.AutoFit()
      release-comobject $range
      $rc = $Worksheet.Activate
      $range = $Worksheet.Range("A1")
      $rc = $range.Select
      release-comobject $range
      release-comobject $Worksheet
   }


   $Workbook.Sheets.Item(1).Activate()
   $Workbook.SaveAs((Join-Path $xlPath "$xlBasename.$xlFormat"), $xlFormatNum)
   $Excel.quit()
}
catch {
   Write-Error $_
}
finally {
   # See http://technet.microsoft.com/en-us/library/ff730962.aspx
   release-comobject $Workbook
   release-comobject $Excel
}

 

[Andrew Poulsom]

Does this work for you?

$Workbook.Sheets.Item(1).range("A1").select()

 

[scottbass]

Does this work for you?

$Workbook.Sheets.Item(1).range("A1").select()

Hi, thanks for your reply.

I've got these lines in the foreach loop:

$rc = $Worksheet.Activate
$range = $Worksheet.Range("A1")
$rc = $range.Select

which are meant to unselect the cells from $Worksheet.UsedRange

Then this line after the foreach loop:

$Workbook.Sheets.Item(1).Activate()

which is meant to select Sheet1.

When I open the saved Workbook, Sheet1 is selected, but all the cells are still selected in all worksheets.

I'll try replacing the code in the foreach loop when I'm at work tomorrow, but unless I'm missing something, it's very much like your code???

BTW, the reason I'm coding this "one dot level only" has to do with releasing the Excel COM objects so Excel closes down properly (according to some Google hits I've read)

Thanks again,
Scott

 

[Andrew Poulsom]

Did you actually try the code I posted? The difference between yours and mine is that nothing is assigned to a variable.

 

[scottbass]

Did you actually try the code I posted? The difference between yours and mine is that nothing is assigned to a variable.

Hi Andrew,

Sorry, I didn't make the connection that the important bit was dropping $rc=.

On further development of my script today, I realize I don't want to select A1, but rather the first cell after the frozen header row (and I don't want to hardcode "A2").

After a bit of Googling, here's my current script. I include the entire script for completeness, but the important bits are in the foreach loop:

[CmdletBinding(SupportsShouldProcess=$true)]param(
   # full path to the original Excel file (usually in XML format)
   [Parameter(
      Position=0,
      Mandatory=$true
   )]
   [Alias("Path")]
   [ValidateScript({Test-Path $_})]
   [System.IO.FileInfo]$xlFile
   ,
   # ouptput format
   [Parameter(
      Position=1
   )]
   [Alias("Format")]
   [ValidateSet("xlsx","xlsm","xlsb","xls")]
   [String]$xlFormat="xlsb"
)


# See http://technet.microsoft.com/en-us/library/ff730962.aspx
# or Google "ReleaseComObject Excel"
function release-comobject($ref) {
   while ([System.Runtime.InteropServices.Marshal]::ReleaseComObject($ref) -gt 0) {}
   [System.GC]::Collect()
   [System.GC]::WaitForPendingFinalizers() 
}


$ErrorActionPreference="Continue"


$xlPath=$xlFile.DirectoryName
$xlBasename=$xlFile.Basename


switch ($xlFormat) {
   "xlsx" {$xlFormatNum=51; break}
   "xlsm" {$xlFormatNum=52; break}
   "xlsb" {$xlFormatNum=50; break}
   "xls"  {$xlFormatNum=56; break}
}


try {
   $Excel = New-Object -ComObject excel.application
   $Excel.visible = $False
   $Excel.displayalerts = $False
   $Workbook = $Excel.Workbooks.Open($xlFile)


   #=============================================================================#
   # autofit row/column                                                          #
   # before we save the file, call AutoFit to improve the appearance             #
   #=============================================================================#


   foreach ($Worksheet in $Workbook.Worksheets) {
      [Void] $Worksheet.Activate
      $range=$Worksheet.UsedRange
      [Void] $range.EntireColumn.AutoFit() 
      [Void] $range.Rows.AutoFit()
      
      # equivalent of Cntl-Home if freeze panes is active
      $row=$Excel.ActiveWindow.SplitRow + 1
      $col=$Excel.ActiveWindow.SplitColumn + 1
      [Void] $Worksheet.Range("A1").Select
      [Void] $Worksheet.Cells.Item($row,$col).Select
      
      release-comobject $range
      release-comobject $Worksheet
   }


   [Void] $Workbook.Sheets.Item(1).Activate()
   [Void] $Workbook.SaveAs((Join-Path $xlPath "$xlBasename.$xlFormat"), $xlFormatNum)
   [Void] $Excel.quit()
}
catch {
   Write-Error $_
}
finally {
   # See http://technet.microsoft.com/en-us/library/ff730962.aspx
   release-comobject $Workbook
   release-comobject $Excel
}

To test it, I created an Excel workbook as follows:

Sheet1 has a header row NAME VALUE. Rows are A 1, B 2, C 3 (they could be anything, just whack in some test rows/columns).
I froze the top row and turned on auto filtering.
I pressed Shift-Cntl-End to select all the rows.
I saved this workbook in XML Spreadsheet 2003 format.

After running the script, in the output Book1.xlsb file, the active cell is A1, instead of A2, and all the cells are still selected (highlighted).

I would have thought that:

[Void] $Worksheet.Range("A1").Select

would have unselected all the highlighted cells and just selected A1. And I'd hoped:

[Void] $Worksheet.Cells.Item($row,$col).Select

would have moved the active cell to A2 (the equivalent of pressing Cntl-Home in the worksheet).

Thanks,
Scott

 

[Andrew Poulsom]

I don't have PowerShell to test, but this:

[Void] $Worksheet.Range("A1").Select

isn't the same as:

$Worksheet.Range("A1").Select()

is it?

 

[scottbass]

I don't have PowerShell to test, but this:

[Void] $Worksheet.Range("A1").Select

isn't the same as:

$Worksheet.Range("A1").Select()

is it?

Hi Andrew,

Thanks for the replies, much appreciated.

If you have Excel, you probably have Windows And if you have any fairly modern version of Windows, you have Powershell. On Win 7: Start --> All Programs --> Accessories --> Windows Powershell.

Having said that, I don't expect you to run and debug my script!

$Worksheet.Range("A1").Select, without capturing the output to a variable, sends object information to the console. Casting the return to [Void] eliminates this output.

I don't know why some method calls have terminating parentheses and others don't. For example:

[Void] $Worksheet.Activate, vs.
[Void] $range.Rows.AutoFit() or
[Void] $Workbook.Sheets.Item(1).Activate()

I'd prefer consistency, but if I code

[Void] $Worksheet.Activate()

I get a runtime error in Powershell.

AFAIK, [Void] $Worksheet.Range("A1").Select is functionally equivalent to $Worksheet.Range("A1").Select(). And, in fact, if I code the latter, I get this error:

$Worksheet.Range("A1").Select()

and the results:

True
C:\Powershell\Scripts\ShrinkExcel.ps1 : Exception calling "Select" with "0" argument(s): "Select method of Range class failed"
At line:1 char:2
+ . <<<<  'C:\Powershell\Scripts\ShrinkExcel.ps1' C:\Temp\excel_tagsets.xml
    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException,ShrinkExcel.ps1

"True" is the output from the method call, without capturing it in a variable or casting to [Void], and the exception is the result of adding the terminating parentheses.

Here is another related script, accepted into the Technet library (and no negative commentary):

Office AutoFit Columns in an Excel Spreadsheet

Perhaps I should just bite the bullet and convert this to vbscript, but I'd like to keep running with Powershell for now.

Thanks again for your replies, much appreciated.

Kind Regards,
Scott

 

[Andrew Poulsom]

I don't use PowerShell, but since you hadn't had any replies I Googled it. I found the syntax I posted here:

https://groups.google.com/forum/#!topic/microsoft.public.windows.powershell/zl4oe4LjplU

Building Excel Reports with PowerShell

 

[scottbass]

I don't use PowerShell, but since you hadn't had any replies I Googled it. I found the syntax I posted here:

https://groups.google.com/forum/#!topic/microsoft.public.windows.powershell/zl4oe4LjplU

Building Excel Reports with PowerShell

Hi Andrew,

Again, thanks for the help. And yes, I did have a bug...

I think this is the correct explanation of the issue.

This line:

[Void] $Worksheet.Range("A1").Select

is trying to return the Select property from the Worksheet.Range object. Since this property doesn't exist, it's a no-op. Powershell just returns nothing for a non-existent property. And of course the method was never called.

And this line:

[Void] $Worksheet.Range("A1").Select()

Is trying to call the Select() method against the wrong object, generating an error. $Worksheet.Range("A1") returns a Range COM object, which does have a Select() method. But, the Worksheet COM object does not have a Range("A1").Select() method.

The correct syntax is (note the parentheses):

[Void] ($Worksheet.Range("A1")).Select()

or else:

$range=$Worksheet.Range("A1")
$range.Select()

I've also discovered that, if I use the parentheses plus nested constructs, I don't have to do as much cleanup handling on the COM objects. IOW, if I don't assign the COM object to a Powershell variable, I don't have to clean it up, and Excel closes down properly, rather than remaining running in the background.

Your links inspired this realization. I Googled all over the place before posting, but obviously didn't find those links.

Here is the final script. Thanks so much for all the help. Problem solved.

Scott

[CmdletBinding(SupportsShouldProcess=$true)]
param(
   # full path to the original Excel file (usually in XML format)
   [Parameter(
      Position=0,
      Mandatory=$true
   )]
   [Alias("Path")]
   [ValidateScript({Test-Path $_})]
   [System.IO.FileInfo]$xlFile
   ,
   # ouptput format
   [Parameter(
      Position=1
   )]
   [Alias("Format")]
   [ValidateSet("xlsx","xlsm","xlsb","xls")]
   [String]$xlFormat="xlsb"
)


# See http://technet.microsoft.com/en-us/library/ff730962.aspx
# or Google "ReleaseComObject Excel"
function release-comobject($ref) {
   while ([System.Runtime.InteropServices.Marshal]::ReleaseComObject($ref) -gt 0) {}
   [System.GC]::Collect()
   [System.GC]::WaitForPendingFinalizers() 
}


$ErrorActionPreference="Continue"


$xlPath=$xlFile.DirectoryName
$xlBasename=$xlFile.Basename


switch ($xlFormat) {
   "xlsx" {$xlFormatNum=51; break}
   "xlsm" {$xlFormatNum=52; break}
   "xlsb" {$xlFormatNum=50; break}
   "xls"  {$xlFormatNum=56; break}
}


try {
   $Excel = New-Object -ComObject excel.application
   $Excel.visible = $False
   $Excel.displayalerts = $False
   $Workbook = $Excel.Workbooks.Open($xlFile)


   #=============================================================================#
   # autofit row/column                                                          #
   # before we save the file, call AutoFit to improve the appearance             #
   #=============================================================================#


   foreach ($Worksheet in $Workbook.Worksheets) {
      [Void] $Worksheet.Activate()
      [Void] ($Worksheet.UsedRange).EntireColumn.AutoFit() 
      [Void] ($Worksheet.UsedRange).Rows.AutoFit()
      
      # equivalent of Cntl-Home if freeze panes is active
      $row=$Excel.ActiveWindow.SplitRow + 1
      $col=$Excel.ActiveWindow.SplitColumn + 1
      [Void] ($Worksheet.Cells.Item($row,$col)).Select()
      
      release-comobject $Worksheet
   }


   [Void] $Workbook.Sheets.Item(1).Activate()
   [Void] $Workbook.SaveAs((Join-Path $xlPath "$xlBasename.$xlFormat"), $xlFormatNum)
   [Void] $Workbook.Close()
   [Void] $Excel.quit()
}
catch {
   Write-Error $_
}
finally {
   # See http://technet.microsoft.com/en-us/library/ff730962.aspx
   release-comobject $Workbook
   release-comobject $Excel
}