Summary: Microsoft Scripting Guy, Ed Wilson, talks about using Windows PowerShell to find read-only Microsoft Excel files and to change them to read-write.

 Hey, Scripting Guy! I have a problem that hopefully you can resolve. We recently restored a bunch of files from tape, and for some reason they all became marked as read-only. Our Help Desk has been inundated with calls from people who open a Microsoft Excel spreadsheet, make a bunch of changes to it, and then are prompted to save it with a different name. This is a HUGE problem because we have hundreds of spreadsheets that are saved in different places and used on a daily basis by dozens of different departments. For example, we have one spreadsheet with lots of special formulas that the production team updates at night, and the plant manager uses each morning to calculate our cost-per-unit for our plant report to corporate headquarters. His spreadsheet uses input from all the departments to make the calculations. If one spreadsheet changes its name or its path, the whole thing breaks. I simply must figure out a way to find all the Microsoft Excel spreadsheets and change them from being read-only. It might be my job otherwise.

—RS

 Hello RS,

Microsoft Scripting Guy, Ed Wilson, is here. Well, it always seems to take longer to get back into the swing of things after being out of the office. If I spend three days away, it seems to take me five days to get back on track. I did not even look at any scripter@microsoft.com email this past week while I was at Microsoft TechEd in Orlando. So now, I am trying to catch up on those messages. RS, I am sorry you are having such a terrible time. I can certainly help you get things back on line, but for a long-term solution you should really be looking at SharePoint because it is designed to do what you have sort of cobbled together. As you have seen, linking spreadsheets like that can be a bit fragile.

The first thing you need to do is to find all of the Microsoft Excel files in your system. To do this, you will use the Get-ChildItem cmdlet and use the Include parameter to allow you to search for all XLS and XLSX types of files. You will also need to use the Recurse switched parameter to search through the folder. The following command uses the GCI alias for the Get-ChildItem cmdlet.

gci -Include *.xls, *.xlsx -Recurse

If you change your working directory to the location that contains the files, you will not need a Path parameter. This technique is shown here.

Because you more than likely have multiple directories to search, you can supply them to the cmdlet as an array. To do this, use the ­Path parameter as follows.

gci -Include *.xls, *.xlsx -Recurse -Path c:\test, c:\fso

The command and the output associated with the command are shown here.

To determine if a file is read-only, you check the IsReadOnly property. The following command finds all of the Microsoft Excel documents in multiple folders, returns the complete path, and tells whether the file is read-only.

gci -Include *.xls, *.xlsx -Recurse -Path c:\test, c:\fso | select fullname,isreadonly

The command and the output associated with the command are shown here.

You now have two choices. First, you can determine if the file is read-only. If it is, you can set it to read-write. Or, the easier way, you can simply make all of the files in the folder read-write. The net result is the same. Obviously, the second option is the easier code to write. For the remaining examples, I will only use the C:\test directory. In the following example, I use the % alias for the Foreach-Object cmdlet. I use the If statement to determine if the file is read-only. If it is, I change it to read-write by setting the IsReadOnly property to $false. The command is shown here.

gci -Include *.xls, *.xlsx -Recurse | % { if($_.IsReadOnly){$_.IsReadOnly= $false} }

The command and the output associated with the command are shown here.

It is much easier to simply change all the Microsoft Excel documents from read-only. Because I am in my test folder, I first change everything to read-only by using the following command.

gci -Include *.xls, *.xlsx -Recurse | % { $_.isreadonly = $true }

I then change them back by setting the IsReadOnly property on each file to $false as shown here.

gci -Include *.xls, *.xlsx -Recurse | % { $_.isreadonly = $false }

The following image shows the commands and the associated output.

RS, that is all there is to changing read-only files with Windows PowerShell. Join me tomorrow when I will talk about more cool Windows PowerShell stuff.

I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

Ed Wilson, Microsoft Scripting Guy