I am writing a C# Windows App that updates out Excel reports' modules. The
app is complete, but has a problem. The only way MS allows you to unprotect
the VBA code in Excel is to do it by hand or sendkeys.
I hate using SendKeys, but I am forced to use them. Anyway, I have written a
function that will unprotect the VBA code in the current work book. It works
about 60% of the time. For some reason it gets out of sync and the SendKeys
don't go to the correct window or are just simply not sent. This means that
VBA never gets unprotected and the rest of the code after that fails. In
this case it throws exceptions every which way. Does anyone know of a way to
make sure that my sendkeys get sent correctly? I have provided my code
below... Thanks in advanced!!
Bryan
using System;
using System.Windows.Forms;
using utils = UpdateWkBks.utils;
using System.Runtime.InteropServices;
namespace UpdateWkBks
{
/// <summary>
/// Summary description for xlProtection.
/// </summary>
public class xlProtection
{
public xlProtection()
{
//
// TODO: Add constructor logic here
//
}
//declare those two apis in the declaration section of your form
[DllImport("User32",EntryPoint="FindWindow")]
private static extern IntPtr FindWindow(string lpClassName,string
lpWindowName);
[DllImport("User32",EntryPoint="BringWindowToTop")]
private static extern bool BringWindowToTop(IntPtr wHandle);
/// <summary>
/// Unprotect Excel VB Code
/// </summary>
/// <param name="Pwd"></param>
public static void UnprotectVBA(string Pwd)
{
Excel.Application objApp = utils.GetXlApp(); //Current XL app
Excel.Workbook WB = utils.GetWkBk(); //Current Wkbk
bool ret;
//This function will UnProtect a VBProject in an Excel file.
WB.Activate(); //Activate the correct WorkBook.
objApp.VBE.MainWindow.Visible = true;
IntPtr hWnd = FindWindow(null, objApp.VBE.MainWindow.Caption);//Find window
if(hWnd != IntPtr.Zero)
{
ret = BringWindowToTop(hWnd); //Bring VBE to top.
}
//Use SendKeys to unlock VBProject.
SendKeys.SendWait("%T");
SendKeys.SendWait("E");
SendKeys.SendWait(Pwd);
SendKeys.SendWait("{ENTER}");
SendKeys.SendWait("{ENTER}");
SendKeys.SendWait("%F");
SendKeys.SendWait("C");
}
/// <summary>
/// Password Protect Excel VB code.
/// </summary>
/// <param name="Pwd"></param>
public static void ProtectVB(string Pwd)
{
Excel.Application objApp = utils.GetXlApp(); //Current XL app
Excel.Workbook WB = utils.GetWkBk(); //Current Wkbk
bool ret;
//This function will protect a VBProject in an Excel file.
//If VBProject isn't already protected.
if (WB.VBProject.Protection.Equals(1) == true)
{
WB.Activate(); //Activate the correct WorkBook.
objApp.VBE.MainWindow.Visible = true;
IntPtr hWnd = FindWindow(null, objApp.VBE.MainWindow.Caption);//change this
caption to the caption of you outlook window
if(hWnd != IntPtr.Zero)
{
ret = BringWindowToTop(hWnd);
}
//Use SendKeys to set VBProject to protected.
SendKeys.SendWait("%T");
SendKeys.SendWait("E");
SendKeys.SendWait("+{TAB}");
SendKeys.SendWait("{RIGHT}");
SendKeys.SendWait("%V");
SendKeys.SendWait("{TAB}");
SendKeys.SendWait(Pwd);
SendKeys.SendWait("{TAB}");
SendKeys.SendWait(Pwd);
SendKeys.SendWait("{ENTER}");
SendKeys.SendWait("%F");
SendKeys.SendWait("C");
objApp.VBE.MainWindow.Visible = false;
}
}
}
}
