By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,726 Members | 2,465 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,726 IT Pros & Developers. It's quick & easy.

SendKeys out of sync...

P: n/a
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;

}

}

}

}
Nov 15 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Try something like this:

Excel.Application oXL;
Excel._Workbook oWB;

oXL = new Excel.Application();
oXL.Visible = true;
oWB = (Excel._Workbook)(oXL.Workbooks.Open("c:\\MyWorkbo ok.xls",
Typee.Missing,Type.Missing,Type.Missing,
Type.Missing,Type.Missing,Type.Missing,
Type.Missing,Type.Missing,Type.Missing,
Type.Missing,Type.Missing,Type.Missing,
Type.Missing,Type.Missing));

oXL.VBE.ActiveVBProject = oWB.VBProject;

SendKeys.SendWait("{ENTER}");
SendKeys.SendWait("p");
SendKeys.SendWait("a");
SendKeys.SendWait("s");
SendKeys.SendWait("s");
SendKeys.SendWait("w");
SendKeys.SendWait("o");
SendKeys.SendWait("r");
SendKeys.SendWait("d");
SendKeys.SendWait("{ENTER}");

--

"Bryan" <bm****@elite-oregon.com> wrote in message news:<##*************@TK2MSFTNGP09.phx.gbl>...
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;

}

}

}

}

Nov 15 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.