none
Exception from HRESULT: 0x800A03EC. at at Microsoft.Office.Interop.Excel.WorkbookClass.get_VBProject()

    Question

  • Hi,

    I am trying to create a excel macro programatically from VS .Net 2003 (C#). I followed the MSDN KB article at http://support.microsoft.com/?kbid=303872. However everytime the program executes the following line of code, it behaves strangely.

    Code Line:

    objModule = objNewBook.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule);

    Everytime the code tries to execute this line of code, first it gives an error "Programmatic access to Visual Basic Project is not trusted". However I have already allowed the VB access to the macro from Excel Tools|Options|Security|Macro Security|Trusted Publishers. But everytime the programs fails first time for this reason, and when I step into and debug and take the debug cursor back to the program line and press F10, it fails for a different error "Exception from HRESULT: 0x800A03EC."

    I need to have this code running by the end of today. please help! I am attaching the code module here, the function uses many other functions and libraries which are not needed here. So I am not including them.

    private void DisplayExcel()
      {
       ExcelApp.Application objApp = null;
       ExcelApp._Workbook objBook = null;
       ExcelApp._Workbook objNewBook = null;
       ExcelApp._Worksheet objSheet = null;
       ExcelApp._Worksheet objNewSheet = null;
       VBIDE.VBComponent objModule = null;

       Object objMissing = System.Reflection.Missing.Value;

       DBConnect objDBCon = null;
       DataSet objData = null;
       try
       {
        string strFileName = mstrDB.Split('.')[0].ToString() + "-LLG.xls";
        strFileName = GetFilePathContext() + Common.FILE_PATH_CONTEXT.TEMPLATE_CONTEXT + "\\" + strFileName;

        KillZombieExcel();
        objApp = new ExcelApp.Application();

        if(File.Exists(strFileName))
        {
         string[] arrPDPs = new string[16];
         if(radIsPDP.SelectedValue == "1")
         {
          int intPDPCount = Common.BLANKS.BLANK_NUMBER;
          for(int intIndex=lstHealthPlan.SelectedIndex; intIndex<lstHealthPlan.Items.Count; intIndex++)
          {
           if(lstHealthPlan.Items[intIndex].Value.Substring(0, 5) == lstHealthPlan.SelectedValue.Substring(0, 5))
            arrPDPs[intPDPCount++] = lstHealthPlan.Items[intIndex].Value;
           else
            break;
          }
         }
         else
          arrPDPs[0] = lstHealthPlan.SelectedValue;
         objApp.Visible = true;
         objBook = objApp.Workbooks._Open(strFileName, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing);
         objNewBook = objApp.Workbooks.Add(objMissing);
         for(int intIndex=0; intIndex<arrPDPs.Length; intIndex++)
         {
          if(arrPDPs[intIndex] == null)
           break;

          objSheet = (ExcelApp._Worksheet)objBook.Worksheets["Plan Selection"];
          objSheet.Activate();

          objSheet.Cells[3,5] = arrPDPs[intIndex];
          RunMacro(objApp, new Object[]{"CreateGridWithFilter"});

          objSheet = (ExcelApp._Worksheet)objBook.Worksheets["Output Grid"];
          objSheet.Activate();
          
          objSheet.get_Range("A1", "S216").Copy(objMissing);
          if(intIndex <=2)
           objNewSheet = ((ExcelApp._Worksheet)objNewBook.Worksheets[intIndex+1]);
          else
           objNewSheet = ((ExcelApp._Worksheet)objNewBook.Worksheets.Add(objMissing, objMissing, objMissing, objMissing));
          objNewSheet.Name = arrPDPs[intIndex];
          objNewSheet.get_Range("A1", "S216").PasteSpecial(ExcelApp.XlPasteType.xlPasteColumnWidths, ExcelApp.XlPasteSpecialOperation.xlPasteSpecialOperationNone, objMissing, objMissing);
          objNewSheet.get_Range("A1", "S216").PasteSpecial(ExcelApp.XlPasteType.xlPasteAll, ExcelApp.XlPasteSpecialOperation.xlPasteSpecialOperationNone, objMissing, objMissing);
          
          objModule = objNewBook.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule);
          objModule.CodeModule.AddFromFile(GetFilePathContext() + Common.FILE_PATH_CONTEXT.TEMPLATE_CONTEXT + "\\" +  "FormatGrid.txt");
          RunMacro(objApp, new Object[]{"FormatGrid"});
          objApp.ActiveWindow.Zoom = 89;
         }
         strFileName = strFileName.Split('.')[0].ToString() + "-" + DateTime.Now.ToString().Replace("/",Common.BLANKS.BLANK_STRING).Replace(":", "-") + ".xls";
         objNewBook.SaveAs(strFileName, objMissing, objMissing, objMissing, objMissing, objMissing, ExcelApp.XlSaveAsAccessMode.xlNoChange, objMissing, objMissing, objMissing, objMissing, objMissing);
         objNewBook.Close(objMissing, objMissing, objMissing);
         
         string[] strSplit = strFileName.Split('\\');
         strFileName = "\\BenefitComplianceUI\\" + Common.FILE_PATH_CONTEXT.TEMPLATE_CONTEXT + "\\" + strSplit[strSplit.Length-1];
         Response.Redirect(strFileName, true);
        }
        else
        {
         strFileName = GetFilePathContext() + Common.FILE_PATH_CONTEXT.TEMPLATE_CONTEXT + "\\" + "PBP-LLG-Template.xls";
         objBook = objApp.Workbooks._Open(strFileName, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing);
         strFileName = GetFilePathContext() + Common.FILE_PATH_CONTEXT.TEMPLATE_CONTEXT + "\\" + mstrDB.Split('.')[0].ToString() + "-LLG.xls";
         objBook.SaveAs(strFileName, objMissing, objMissing, objMissing, objMissing, objMissing, ExcelApp.XlSaveAsAccessMode.xlNoChange, objMissing, objMissing, objMissing, objMissing, objMissing);
         objBook.Close(objMissing, objMissing, objMissing);
          
         string[] strTable = new string[]{"PBP", "PBPC", "PBPC_OON", "PBPD", "PBPD_OPT", "PBPMRX", "PBPMRX_G", "PBPS1", "PBPS2", "PBPS3", "PBPS4", "PBPS5", "PBPS6", "PBPS7", "PBPS8", "4a 4b 10a Waived"};
         string[] strSQL = new string[strTable.Length];

         for(int intIndex=0; intIndex<strTable.Length-1; intIndex++)
         {
          strSQL[intIndex] = "SELECT * FROM " + strTable[intIndex];
         }
         strSQL[strTable.Length-1] = CustomQuery();
         objDBCon = new DBConnect(mstrDB);
         objData = objDBCon.ExecuteQuery(strSQL, strTable);
         
         if(objData != null)
         {
          objApp.Visible = true;
          objNewBook = objApp.Workbooks._Open(strFileName, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing);
          for(int intIndex=0; intIndex<objData.Tables.Count; intIndex++)
          { 
           System.Data.DataTable objTable = objData.Tables[intIndex];
           objSheet = (ExcelApp._Worksheet)objNewBook.Worksheets[objTable.TableName];
           objSheet.Activate();
           
           string strStartIndex = Common.BLANKS.BLANK_STRING;
           string strEndIndex = Common.BLANKS.BLANK_STRING;
           int intOffset = 0;
           GetBoundariesForSheet(intIndex, out strStartIndex, out strEndIndex, out intOffset);
           
           objSheet.get_Range(strStartIndex, strEndIndex + (objTable.Rows.Count + intOffset -1).ToString()).CopyFromRecordset(Common.ConvertToRecordset(objTable), objMissing, objMissing); 
          }
          objNewBook.Save();
          objNewBook.Close(objMissing, objMissing, objMissing);
          objApp.Workbooks.Close();
          objApp.Quit();
          DisplayExcel();
         }
        }
       }
       catch(ThreadAbortException exp)
       {
       }
       catch(Exception exp)
       {
        Common.Log(exp, Common.PAGE.LOW_LEVEL_GRID + ".ShowReport()", EventLogEntryType.Error, User.Identity.Name);
        Response.Write(exp.StackTrace);
        Response.End();
       }
       finally
       {
        if(objApp != null) System.Runtime.InteropServices.Marshal.ReleaseComObject (objApp);
        if(objSheet != null) System.Runtime.InteropServices.Marshal.ReleaseComObject (objSheet);
        if(objBook != null)
         System.Runtime.InteropServices.Marshal.ReleaseComObject (objBook);
        if(objNewBook != null)
         System.Runtime.InteropServices.Marshal.ReleaseComObject (objNewBook);
        objSheet=null;
        objBook=null;
        objNewBook=null;
        objApp = null;
        GC.Collect();
        KillZombieExcel();
        if(objDBCon != null)
         objDBCon.Dispose();
        if(objData != null)
         objData.Dispose();
       }
      }

      private void RunMacro(object oApp, object[] oRunArgs)
      {
       oApp.GetType().InvokeMember("Run",
        System.Reflection.BindingFlags.Default |
        System.Reflection.BindingFlags.InvokeMethod,
        null, oApp, oRunArgs);
      }

    Thanks

    -Siddhartha


    Tuesday, April 11, 2006 1:56 PM

Answers

All replies

  • Tuesday, April 11, 2006 8:13 PM
  • Hello,

     

    I know it passed a long time since the post was created but take a look at this : http://support.microsoft.com/kb/282830/ 

     

    I had the same error but it prooved to be this KB the real solution after tring several other workarrounds which in fact did not work at all.

     

     

    Have fun coding,

    HomeDream

    • Proposed as answer by Luis Custodio Thursday, May 20, 2010 2:26 PM
    Thursday, October 04, 2007 8:20 AM
  • Hi,

     

    i have a problem. don't know whether this is same. i have a web application where i am doing excel automation. i developed this application in vista machine. i am able to generate the excel file. but when i publish this site in IIS 7. i am getting the below error.

     

    Exception from HRESULT: 0x800A03EC

     

    i am not able to find out the reason. from the code it is working because it uses the local server not the IIS. And the published site uses IIS. i gave the full permissions to the Microsoft Excel Application object for the NETWORK SERVICE account. do i have to configure anything else. can some body help me with this.

     

    thanks,

    Srik

    Thursday, September 18, 2008 10:00 AM
  • I had the same problem. Since the exception is generated in a COM object, it gets complicated to understand the source of the error. I tracked the source of the error in my own code by using the printStackTrace member of the thrown exception.

    In my case, the error was due to a column number out of range.

    So, to put it simpler:

    Catch the exception
    try
    {
         ... your failing COM code ....
    }catch(Exception e)
    {
       MessageBox.Show("Exception " + e.Message + " Stack Trace: " +  e.printStackTrace());
    }

    Thursday, February 12, 2009 6:08 PM
  • Thanks Victor.. It was because of my incorrect excel template which i was trying to populate.

    Nahid u seem to be time waster instead..


    Rishi
    Friday, March 18, 2011 9:05 AM
  • I was getting the same exact error and found that some times the solution is so simple that it makes it harder to find. ;)

    I was getting this error when trying to use a ExportAsFixedFormat command to save an Excel job as a pdf. Most of the help I found pointed to either permissions or to many lines in the spread sheet. Neither of these were problems for me.

    After poking around for a while, I found that the partition I was trying to write to was maxed out. I moved a bunch of un-needed files to an archive to clear up some room and the problem was resolved.

    Wednesday, November 07, 2012 3:57 PM