Use PowerShell to Find and Modify Read-Only Files

June 19th, 2012

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

Hey, Scripting Guy! Question Hey, Scripting Guy! I take a problem that hopefully you can resolve. Nosotros 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, brand a bunch of changes to it, and then are prompted to save information technology with a different proper noun. This is a HUGE problem considering we have hundreds of spreadsheets that are saved in unlike places and used on a daily ground by dozens of different departments. For example, we accept i spreadsheet with lots of special formulas that the product team updates at nighttime, and the constitute director uses each morning to calculate our cost-per-unit for our establish report to corporate headquarters. His spreadsheet uses input from all the departments to make the calculations. If 1 spreadsheet changes its name or its path, the whole thing breaks. I merely 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

Hey, Scripting Guy! Answer Hello RS,

Microsoft Scripting Guy, Ed Wilson, is here. Well, it always seems to take longer to get back into the swing of things afterward being out of the role. If I spend iii days abroad, it seems to have me 5 days to get dorsum on runway. I did not even await at any scripter@microsoft.com electronic mail 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 lamentable yous are having such a terrible time. I can certainly help you get things back on line, merely for a long-term solution you should really be looking at SharePoint because it is designed to do what y'all have sort of cobbled together. Every bit you have seen, linking spreadsheets similar that tin exist a bit frail.

The offset affair you need to do is to find all of the Microsoft Excel files in your system. To practise this, you lot will apply the Get-ChildItem cmdlet and utilise 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 binder. The post-obit command uses the GCI allonym for the Get-ChildItem cmdlet.

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

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

Image of command output

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

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

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

Image of command output

To determine if a file is read-simply, you check the IsReadOnly belongings. The following control 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 control and the output associated with the command are shown hither.

Image of command output

You now have two choices. First, you tin can decide if the file is read-only. If it is, you tin can set it to read-write. Or, the easier style, you tin can simply make all of the files in the folder read-write. The internet result is the aforementioned. Plain, the second option is the easier lawmaking to write. For the remaining examples, I will only apply the C:\test directory. In the following instance, I use the % alias for the Foreach-Object cmdlet. I use the If statement to determine if the file is read-simply. If information technology is, I modify information technology to read-write past setting the IsReadOnly property to $false. The command is shown hither.

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

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

Image of command output

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

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

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

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

The following image shows the commands and the associated output.

Image of command output

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

I invite you to follow me on Twitter and Facebook. If you have whatever questions, transport electronic mail to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. Encounter you tomorrow. Until then, peace.

Ed Wilson, Microsoft Scripting Guy