locked
Can't hide Excel in PS script RRS feed

  • Question

  • Hi all,

    New to Powershell but trying to put together a basic script to get the row count of an .xlsx file to then use in some validation.  I don't want the application opening visibly (ideally don't want to open it at all).  So having searched around the net I've put together the below script:

    $filePath = "C:\Users\Showerpell\Desktop\Test.xlsx"
    $sheetName = "Sheet1"
    $Excel = New-Object -ComObject Excel.Application
    $Excel.visible = $False
    $WorkBook = $Excel.Workbooks.Open($filePath)
    $WorkSheet = $WorkBook.sheets.item($sheetName)

    $WorksheetRange = $workSheet.UsedRange
    $RowCount = $WorksheetRange.Rows.Count
    $ColumnCount = $WorksheetRange.Columns.Count
    Write-Host "RowCount:" $RowCount

    The script gives me the correct rowcount, which is all the data I need... but Excel visibly opens whenever I run it regardless of what $Excel.visible is set to.  I've restarted my PC, tried it on more than 1 file, killed all instances of excel.exe in Task manager before starting the script etc.  I'm at a loss as to what to do to ensure the application remains invisible.

    For what it's worth, running Windows 7 and Excel 2013.

    Any ideas?

    Tuesday, August 21, 2018 12:35 PM

All replies

  • If you like to work with Excel files without actually using Excel you can use the module ImportExcel. This way you don't even need Excel installed.  ;-) At least it's worth trying ... I think

    Best regards,

    (79,108,97,102|%{[char]$_})-join''

    Tuesday, August 21, 2018 12:43 PM
  • Hi,

    Thanks for the response, but the machine I run this on can't have any other software installed.

    Really just looking for ideas on why the visible property doesn't seem to be working as advertised

    Tuesday, August 21, 2018 1:23 PM
  • You could even use inplicit remoting. This way you don't need the module installed on the pc you like to use it.

    Best regards,

    (79,108,97,102|%{[char]$_})-join''


    • Edited by BOfH-666 Tuesday, August 21, 2018 1:43 PM
    Tuesday, August 21, 2018 1:42 PM
  • I tried your code and it works fine for me. Running Windows 10 and Excel 2016. I only specified my own filepath and sheetname.

    Tuesday, August 21, 2018 1:55 PM
  • The code is not the issue.  THe Workbook is doing something to force it to become visible.  You cannot override this behavior in code.  It can only be done by fixing the workbook.

    \_(ツ)_/

    Tuesday, August 21, 2018 2:16 PM
  • Thanks for confirming it works on your machine vnip90.  That suggests the code is fine.

    JRV, do you know of any option in Excel that would somehow override the Powershell code and result in the behaviour I'm seeing?  Although the first file I used for testing this script was a .xls originally created several years ago in an older version of Excel, the Test.xlsx file in my example was created fresh in Excel 2016 this morning for testing purposes: I didn't subsequently change any Excel options and there is no VBA embedded in it so I'm perplexed as to why it's not hidden when the code is run.

    Tuesday, August 21, 2018 4:49 PM
  • Then I recommend re-installing Office.  The behavior is not normal.  The solution is outside of the scope of this forum.


    \_(ツ)_/

    Tuesday, August 21, 2018 4:52 PM
  • For reference, earlier today I completely uninstalled Office 2013, restarted the PC, and then reinstalled afresh.  The first thing I did when the install was complete, was run the PS script at the top of this thread, and again, Excel visibly opened.

    Stumped.

    Wednesday, August 22, 2018 1:17 PM
  • Have you tried this on another system?

    The issue is not a scripting issue.  Also you need to fully delete and clean all remnants of office from your system and profile.  I can also recommend creating a new account on the systems and trying again.

    If none of this works you will need to either open an incident with MS support or Re-install you Windows system.


    \_(ツ)_/

    Wednesday, August 22, 2018 7:10 PM
  • Hi, 

    I don't belive that this is the problem, but you can try it, 

    use 

    $ExcelWorkSheet = $Excel.WorkSheets.item($sheetName)
    instead this
    $WorkSheet = $WorkBook.sheets.item($sheetName)

    Thursday, August 23, 2018 8:09 PM
  • Fernando - nice guess but that should not make any difference.  If it does then, perhaps, there is an unreported bug in Excel2013.


    \_(ツ)_/

    Thursday, August 23, 2018 8:14 PM
  • Do you have the same issue with other workbooks? If there are only some workbooks which force visibilty, you can put "$Excel.visible = $false" in the loop over your documents. This way, the misbehaviour would only occur for special documents.

    That means, if it really depends on the document, you are opening... I often use Excel COM object and never found something like this.

    Friday, August 24, 2018 8:56 AM