Hi Nick
I'm very much in the exploratory stage of all this, but my end goal is to be
able to take spreadsheets written by our users and to convert the
calculations found in them to C# code that we can then compile and make
available in our main applications.
Unless I can find a third party tool that does this, I need to get Excel to
tell me what the order of the calculations is along with what each
calculation does, so that for example I could convert '=A1*A2' in cell A3
into
double a3 = CalcValue(a1, a2);
where CalcValue looks something like:
public double CalcValue(double a1, double a2)
{
double a3 = a1 * a2;
return a3;
}
I appreaciate that this is a very simple example and that there will be lots
of issues around the use of Excel functions (such as MIN, MAX, FLOOR, IF
etc.) and that is why I was hoping for a third party control. I guess life
is never that simple though!
I hope this makes sense.
Best regards
Marek
"NickHK" wrote:
> Whilst I'm familiar with the hooks, what do you (both) mean by "the
> calculation" or "cell calculation call" ?
> What do you look for in the WndProcSub ?
>
> NickHK
>
> "Wei Lu [MSFT]" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Hello Marek,
> >
> > I did not have a sample but in VSTO you could use the hook.
> >
> > And here is a sample which use the hook to sub-classes the Excel window
> and
> > handles the Paste event. You may modify it to hook the calculation and use
> > it in VSTO.
> >
> > This is the Hook.cs class that sub-classes the main Excel window using
> > SetWindowLong
> >
> > using System;
> > using System.Collections;
> > using System.Collections.Generic;
> > using System.Text;
> > using System.Windows.Forms;
> > using System.Runtime.InteropServices;
> > using System.Diagnostics;
> > using System.Reflection;
> >
> > namespace SampleXLS
> > {
> > class Hook
> > {
> > private const int WM_KEYDOWN = 0x0100;
> >
> > [DllImport("user32.dll")]
> > private static extern IntPtr FindWindowEx(IntPtr hParent, IntPtr hChild,
> > string sClass, string sWindow);
> >
> > [DllImport("user32.dll")]
> > private static extern bool GetKeyState(int nVirtKey );
> >
> > public delegate int WndProcDelegate(IntPtr hwnd, uint msg, uint wParam,
> int
> > lParam);
> > public static WndProcDelegate wpd;
> > public static IntPtr lpPrevWndProc;
> > public bool IsHooked;
> > public IntPtr gHW;
> >
> > public void SetHook(int hWnd)
> > {
> > try
> > {
> > if (IsHooked)
> > MessageBox.Show("Don't hook it twice without unhooking, or you
> > will be unable to unhook it.");
> > else
> > {
> > gHW = new IntPtr(hWnd);
> > gHW = FindWindowEx(gHW, IntPtr.Zero, "XLDESK", string.Empty);
> >
> > gHW = FindWindowEx(gHW, IntPtr.Zero, "EXCEL7",
> > "SampleXLS.xls");
> >
> > wpd = new WndProcDelegate(WndProcSub);
> > lpPrevWndProc = Win32.SetWindowLong(gHW, -4,
> > Marshal.GetFunctionPointerForDelegate(wpd));
> > //Marshal.GetFunctionPointerForDelegate(m_delegate));
> > IsHooked = true;
> >
> > }
> > }
> > catch (Exception ex)
> > {
> > Debug.Print(ex.Message);
> > }
> >
> > }
> >
> > public void SetUnhook()
> > {
> > IntPtr temp;
> > temp = Win32.SetWindowLong(gHW, -4, lpPrevWndProc);
> > IsHooked = false;
> > }
> >
> > private int WndProcSub(IntPtr hWnd, uint msg, uint wParam, int lParam)
> > {
> > uint vkCode = wParam;
> > if(GetKeyState((int)Win32.VK_CONTROL))
> > {
> > if(vkCode == Win32.VK_V)
> > Debug.Print("Paste Called");
> > }
> >
> > return Win32.CallWindowProc(lpPrevWndProc, hWnd, msg, wParam,
> > lParam);
> > }
> > }
> > }
> >
> > This is the code in VSTO where I call the SetHook and SetUnHook methods.
> >
> > private void ThisWorkbook_Startup(object sender, System.EventArgs e)
> > {
> > WorkbookSheetActivate();
> > oControl = Application.CommandBars["Worksheet Menu
> > Bar"].Controls["Edit"];
> > oControls =
> >
> (Microsoft.Office.Core.CommandBarControls)oControl.GetType().InvokeMember("C
> > ontrols"
> > , System.Reflection.BindingFlags.GetProperty, null, oControl, null);
> > oButton = (Microsoft.Office.Core.CommandBarButton)oControls["Paste"];
> > oButton.Click += new
> >
> Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(oButton_Clic
> > k);
> >
> > int i = Application.Hwnd;
> > objHook.SetHook(i);
> >
> > }
> >
> > private void ThisWorkbook_Shutdown(object sender, System.EventArgs e)
> > {
> > objHook.SetUnhook();
> > }
> >
> > Hope this helps.
> >
> > Sincerely,
> >
> > Wei Lu
> > Microsoft Online Community Support
> >
> > ==================================================
> >
> > When responding to posts, please "Reply to Group" via your newsreader so
> > that others may learn and benefit from your issue.
> >
> > ==================================================
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
>
>
>
Similar Threads
