There was a request about unlocking / unprotecting Excel files (engineering-international.com). If you can open the Excel file, it means it is not 100% protected. Sometimes you can just open, but cannot edit. Or, cannot rich to VBA part. There are some manual solutions for such a case; renaming .xlsx to .zip, extract, then searching / replacing on it etc.. But, if you have ~300 pcs files, what can you do? I coded small utility which is doing all these things. Here, I am sharing the source code of single file version. You can easily adapt it for more files.
Code:
string extractPath = @".\raduga_temp"; <- temporary directory to unzip files
bool is_patched_ = false;
try
{
ZipFile.ExtractToDirectory(file_1, extractPath); <- unzip target file
string[] files_2 = Directory.GetFiles(extractPath, "*.xml", SearchOption.AllDirectories); <- we will search in *.xml files
foreach (string file_2 in files_2)
{
try
{
string contents = File.ReadAllText(file_2);
string target_1 = @"<workbookProtection"; <- WorkBook protection
string target_2 = @"<sheetProtection"; <- WorkSheet protection
string son = @"/>"; <- end of xml element
bool exist_ = contents.Contains(target_1); <- Is there WorkBook protection?
if (exist_)
{
contents = remove_(contents, target_1, son); <- yes, remove element
Console.WriteLine("WorkBook is Unprotected !");
}
else
{
exist_ = contents.Contains(target_2); <- Is there WorkSheet protection?
if (exist_)
{
contents = remove_(contents, target_2, son); <- yes, remove element
Console.WriteLine("WorkSheet is Unprotected !");
}
}
if (exist_)
{
File.SetAttributes(file_2, FileAttributes.Normal);
File.WriteAllText(file_2, contents); <- Write all changes
is_patched_ = true;
}
}
catch (Exception)
{
Console.WriteLine("Something is wrong !");
}
}
string[] files_3 = Directory.GetFiles(extractPath, "*.bin", SearchOption.AllDirectories); <- we will search in *.bin files
byte[] find_ = Encoding.UTF8.GetBytes("DPB="); <- Signature for VBA macro protection
byte[] replace_ = Encoding.UTF8.GetBytes("DPx="); <- just disturb / rename it
foreach (string file_3 in files_3)
{
try
{
byte[] file = File.ReadAllBytes(file_3); <- binary search / replace part
int i, j, iMax = file.Length - find_.Length;
for (i = 0; i < iMax; i++)
{
for (j = 0; j < find_.Length; j++)
if (file[i + j] != find_[j]) break;
if (j == find_.Length) break;
}
if (i < iMax)
{
for (j = 0; j < find_.Length; j++)
file[i + j] = replace_[j];
File.WriteAllBytes(file_3, file);
Console.WriteLine("VBA part is Unlocked !");
is_patched_ = true;
}
}
catch (Exception)
{
Console.WriteLine("Something is wrong !");
}
}
File.Delete(file_1 + ".bak");
File.Move(file_1, file_1 + ".bak"); <- make a backup
ZipFile.CreateFromDirectory(extractPath, file_1); <- save patched file
DirectoryInfo deleteTheseFiles = new System.IO.DirectoryInfo(extractPath);
deleteTheseFiles.Delete(true);
if (!is_patched_) Console.WriteLine("Nothing is done !");
}
catch (Exception)
{
Console.WriteLine("Something is wrong !");
}
}
static string remove_ (string data_in, string start, string finish)
{
int starting = data_in.IndexOf(start);
int finishing = data_in.IndexOf(finish, starting);
string beginning_part = data_in.Substring(0, starting);
string ending_part = data_in.Substring(finishing + 2);
data_in = beginning_part + ending_part;
return data_in;
}