4

I use Invoke-Item "some-file.xlsx" to launch Excel and open a file. Now I would like to open this file in read only mode. It this possible from PowerShell?

To clearify, I am not looking to open the Excel sheet and read data from it to the PowerShell script, the script is intended to help the user finding a file and opening it.

Jan H
  • 4,287
  • 5
  • 24
  • 34
  • I don't work with powershell but is this what you are trying? `$objWorkbook = $objExcel.Workbooks.Open($excelfile, 2, $True)` – Siddharth Rout Nov 11 '13 at 11:31
  • No, I tried `Invoke-Item "some-file.xlsx"`. I think the $objExcel.Workbooks.Open() will open the Excel file in the scope of te PowerShell script only. Closing the PowerShell will close the Excel sheet. – Jan H Nov 11 '13 at 12:02
  • This COM+ method you suggested often causes errors like `Exception calling "Add" with "0" argument(s): "Old format or invalid type library. (Exception from HRESULT: 0x80028018`. This error has a workaround, but it is a lot of COM+ fiddling. I hope there is a non-COM+ way to do this. – Jan H Nov 11 '13 at 14:55

2 Answers2

1

Invoke-item only allows the default action for a file.
To open the file as read only, either:

  • Set read-only attribute on file, launch file, unset read-only attribute, 

    attrib +r "some-file.xlsx"
Invoke-Item "some-file.xlsx"
attrib -r "some-file.xlsx"

or

  • Invoke excel directly, and pass /r as the command file

    $xl = (Get-ItemProperty -Path HKCR:\Applications\EXCEL.EXE\Shell\OpenAsReadOnly\command).'(default)'
$fle = '"some-file.xlsx"'
& $xl $fle

check Executing a Command stored in a Variable from Powershell to get a syntax that works for you

Community
  • 1
  • 1
SeanC
  • 15,695
  • 5
  • 45
  • 66
  • Thanks, I tought of using the excel.exe method, but I think there is no simple way to find the path to the MSOffice bins. – Jan H Nov 11 '13 at 15:01
  • it's in the registry - I added the command that will get the registry key. I haven't done any executing of variables in powershell, so I'm not 100% on the need for quotes – SeanC Nov 11 '13 at 15:58
  • Thanks for this nice solution. It's great to be able to browse through the registry like this. I addded one line your code to mak the HKCR: drive. Unfortunately I think something is wrond on mu machine, Excel 2010 is installed, but the registry key you mentioned is not available. Also other keys like in http://www.idothink.com/2009/11/c-detect-excel-install-location-in.html and http://support.microsoft.com/kb/240794 do not work for me. – Jan H Nov 12 '13 at 09:07
  • HKCR is the shorthand for HKey_Classes_Root. to use microsofts answer, you could first set the clsid to `HKLM:\Software\Classes\Excel.Application\CLSID\(Default)` and then use that clsid to find `HKLM:\Software\Classes\CLSID\$clsid\LocalServer32\(Default)` – SeanC Nov 12 '13 at 13:15
  • @JanH typically the Office path is in the path environment variable also, so just typing `excel.exe` might do the trick. – MDMoore313 Feb 07 '14 at 20:23
0

Read-only is defined as an attribute on the file. So my recommendation would be to set the file to read only then open normal with Invoke-item. Alternatively, you can use code to set the file attribute. The following should do the trick:

set-itemproperty "some-file.xlsx" -Name Attributes -Value "ReadOnly"

Reference:

http://blogs.technet.com/b/heyscriptingguy/archive/2011/01/27/use-powershell-to-toggle-the-archive-bit-on-files.aspx

Steps for setting a file to read-only:

  1. Make sure that the file is not open in Excel.
  2. On the Windows Start menu, point to Programs, point to Accessories, and then click Windows Explorer.
  3. In the Exploring window, open the drive or folder that contains the file.
  4. Click the file name.
  5. On the File menu, click Properties.
  6. Click the General tab.
  7. Select the Read-only check box.

Reference:

http://office.microsoft.com/en-gb/excel-help/save-a-workbook-as-read-only-or-remove-read-only-status-HP005201902.aspx

Arcass
  • 932
  • 10
  • 19
  • 3
    Thanks for your detailed answer. The answer explains how to make the file readonly. This is a bit from opening the file in Excel's readonly mode. The fist will mark the file readonly for all users, the latter will instruct Excel to open the file in readonly mode, will keep it editable for other users, and will prevent that Excel asks the user for the Excel sheet's password. – Jan H Nov 11 '13 at 14:44
  • This answer should probably be deleted for the reasons Jan H states. It couldn't be implemented in any practical production environment without users complaining sooner or later. – Graham May 26 '21 at 17:09