none
ActiveWorkbook is Nothing when running a VBA macro from C# RRS feed

  • Question

  • Hi, I am trying to write a Winform application and dock Excel inside it as a control. Everything works until I tried to run an Excel macro (written in VBA) from my c# app. When this macro tried to access ActiveWorkBook, it is Nothing.

    Funny thing is, if I comment out the "SetParent(xlHwnd, this.Handle);", the ActiveWorkBook is available. Seems like the window state of Excel application is affecting the current ActiveWorkbook.

    Below is my code:

    xlApp = newMicrosoft.Office.Interop.Excel.Application();

    xlApp.WindowState = XlWindowState.xlMinimized;

               

    xlApp.DisplayFormulaBar = false;

    xlApp.ShowWindowsInTaskbar = false;

    xlApp.DisplayAlerts = false;

    xlApp.DisplayStatusBar = false;

    xlApp.Interactive = true;

    xlHwnd = (IntPtr)xlApp.Hwnd;

    xlApp.Visible = true;

    //Windows API call to change the parent of the target window.

    SetParent(xlHwnd, this.Handle);

            

    //Wire up the event to keep the window sized to match the control

    SetWindowPos(xlHwnd, 0, this.DisplayRectangle.Left, this.DisplayRectangle.Top,

                    this.DisplayRectangle.Width, this.DisplayRectangle.Height, 0X0040 | 0X4);           

    this.SizeChanged += newEventHandler(Panel1_Resize);

                xlWorkbook = xlApp.Workbooks.Add();           

    vardbAddIn = (AddIn)xlApp.AddIns[3];           

    if(dbAddIn != null)

    {

        WorkbookdbWorkbook = xlApp.Workbooks.Open(dbAddIn.FullName);             

       if(dbWorkbook != null)

       {

              dbWorkbook.Application.Run("myMacro1");

       }

    }

    Friday, February 16, 2018 9:59 PM

All replies

  • Hello Shan,

    To be honestly, I'm not familiar with calling Win API in  Winform application. Would you mind share the whole code in the project or share a simply project file directly so we could try to reproduce your issue.

    Besides, what's the code in the VBA and what's your purpose of calling the VBA code in Winform Application?

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Monday, February 19, 2018 12:44 PM
  • Hi, Terry, sorry for the late reply. I have made some progress and identified the problem.

    Essentially, the problem is I have to open the workbook of my Addin to access the macro in it. But after opening the workbook, I lost input focus on the actual workbook I am trying to work on.

    Before opening the addin workbook, my input cursor is the default "cross" of Excel. After opening the addin workbook, my cursor became the "arrow" of normal windows.

    Here is the code, is there any way I can send you a zip of my project?

    /********** Form1.cs ************/

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Diagnostics;
    using System.Drawing;
    using System.Linq;
    using System.Runtime.InteropServices;
    using System.Text;
    using System.Threading;
    using System.Timers;
    using System.Windows.Forms;
    using Microsoft.Office.Interop;
    using Microsoft.Office.Interop.Excel;
    using Application = Microsoft.Office.Interop.Excel.Application;
    using VBA = Microsoft.Vbe.Interop;

    namespace WindowsFormsApplication1
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }

            #region Imported Win32 functions, this is SOOO outdated
            [DllImport("user32.dll")]
            static extern int SetForegroundWindow(IntPtr hWnd);
            [DllImport("user32.dll", EntryPoint = "SetWindowPos")]
            static extern IntPtr SetWindowPos(IntPtr hWnd, int hWndInsertAfter, int x, int Y, int cx, int cy, int wFlags);
            [DllImport("user32.dll", CharSet = CharSet.Auto)]
            static extern IntPtr SetParent(IntPtr hWndChild, IntPtr hWndNewParent);
            [DllImport("user32.dll", SetLastError = true)]
            static extern bool MoveWindow(IntPtr hWnd, int X, int Y, int nWidth, int nHeight, bool bRepaint);
            [DllImport("user32.dll", SetLastError = true)]
            static extern bool SetWindowLong (IntPtr hWnd, int nIndex, uint dwNewLong);
            [DllImport("user32.dll", SetLastError = true)]
            static extern uint GetWindowLong(IntPtr hWnd, int nIndex);
            #endregion

            #region Private handles for Excel
            private IntPtr hWndOriginalParent;
            private IntPtr xlHwnd;
            private Application xlApp;
            private Workbook xlWorkbook;
            private Workbook dbWorkbook;
            #endregion

            private void dockIt()
            {
                xlApp = new Microsoft.Office.Interop.Excel.Application();
                xlApp.WindowState = XlWindowState.xlMinimized;

                xlApp.DisplayExcel4Menus = false;
                xlApp.DisplayFormulaBar = false;
                xlApp.ShowWindowsInTaskbar = false;
                xlApp.DisplayAlerts = false;
                xlApp.DisplayStatusBar = false;
                xlApp.Interactive = true;
                xlApp.EditDirectlyInCell = true;
                xlApp.ScreenUpdating = true;
                xlHwnd = (IntPtr)xlApp.Hwnd;
                xlApp.Visible = true;

                // This has to happend BEFORE SetParent()
                xlWorkbook = xlApp.Workbooks.Add();

                //Windows API call to change the parent of the target window.
                SetParent(xlHwnd, this.Handle);

                //Wire up the event to keep the window sized to match the control
                this.SizeChanged += new EventHandler(Panel1_Resize);
                Panel1_Resize(new object(), new EventArgs());

            }

            private AddIn GetDBAddin()
            {
                foreach (AddIn addIn in xlApp.AddIns)
                {
                    if (addIn.Name.ToLower().Contains("drawingbuilder"))
                    {
                        if (!addIn.Installed)
                            addIn.Installed = true;
                        return addIn;
                    }
                }
                return null;
            }

            private void undockIt()
            {
                //Restores the application to it's original parent.
                SetParent(xlHwnd, hWndOriginalParent);
            }

            private void Panel1_Resize(object sender, EventArgs e)
            {
                //Change the docked windows size to match its parent's size.
                MoveWindow(xlHwnd, this.ClientRectangle.X, this.ClientRectangle.Y + MainMenuStrip.Height,
                    this.ClientRectangle.Width, this.ClientRectangle.Height, true);
            }

            private void Form1_Load(object sender, EventArgs e)
            {
            }

            private void Form1_FormClosed(object sender, FormClosedEventArgs e)
            {
                if (xlWorkbook != null)
                    xlWorkbook.Close(false);

                if (dbWorkbook != null)
                    dbWorkbook.Close(false);

                if (xlApp != null)
                {
                    xlApp.Quit();

                    releaseObject(xlApp);
                }
            }

            private void releaseObject(object obj)
            {
                if (obj == null)
                    return;

                try
                {
                    Marshal.ReleaseComObject(obj);
                    obj = null;
                }
                catch (Exception ex)
                {
                    obj = null;
                    MessageBox.Show(@"Unable to release the Object " + ex.ToString());
                }
                finally
                {
                    GC.Collect();
                }
            }


            private void Form1_Shown(object sender, EventArgs e)
            {
            }

            private void Form1_Activated(object sender, EventArgs e)
            {
            }

            private void toolStripMenuItem1_Click(object sender, EventArgs e)
            {
                dockIt();
            }

            private void launchDBToolStripMenuItem_Click(object sender, EventArgs e)
            {
                var dbAddIn = GetDBAddin();
                if (dbAddIn == null)
                    return;
                if (dbWorkbook == null)
                {
                    dbWorkbook = xlApp.Workbooks.Open(dbAddIn.FullName);
                }
                xlWorkbook.Activate();
                SetForegroundWindow((IntPtr)xlApp.Hwnd);
            }

            private void addAPartToolStripMenuItem_Click(object sender, EventArgs e)
            {
                if (dbWorkbook != null)
                {
                    xlWorkbook.Application.Run("Icon_menu_starts.TJoint_start");
                    xlApp.ScreenUpdating = true;
                    xlApp.EnableEvents = true;
                }
            }

        }
    }

    Thursday, March 1, 2018 3:12 PM
  • Hello Shan Wang BHI,

    You could share the file via Cloud Storage, such as One Drive, and then put link address here.

    It seems that you are calling a macro in an add-in and an ActiveWorkbook fails to work, right?

    What's the add-in? Is it xlam add-in and what's the code in it?

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 2, 2018 7:07 AM