Expert: Srini Nagarajan - 7/27/2005

Question
Here Iam instantiating the excel application object and by using the automation Iam trying to access the excel macros by VBcomponent module.
It works fine in windows application.  But giving the above error when I tried in web application. The Error line is
"module = wb.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule);”
Server Error in '/ExcelApp' Application.

Programmatic access to Visual Basic Project is not trusted
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Runtime.InteropServices.COMException: Programmatic access to Visual Basic Project is not trusted

Source Error:
Line 74:                
Line 75:
Line 76:          module = wb.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule);
Line 77:          module.CodeModule.AddFromString(GetMacro());
Line 78:          

Source File: c:\inetpub\wwwroot\excelapp\webform1.aspx.cs    Line: 76

Stack Trace:
[COMException (0x800a03ec): Programmatic access to Visual Basic Project is not trusted
]
  Microsoft.Office.Interop.Excel.WorkbookClass.get_VBProject() +0
  ExcelApp.WebForm1.Button1_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\excelapp\webform1.aspx.cs:76
  System.Web.UI.WebControls.Button.OnClick(EventArgs e)
  System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
  System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
  System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
  System.Web.UI.Page.ProcessRequestMain()


This is my code for that.  It works fine in windows application.  But giving the above error when I tried in web application.
private void Button1_Click(object sender, System.EventArgs e)
{
string outURL = "documents/" + DateTime.Now.ToString("dd_MM_yy_hh_mm_ss");
string path = Server.MapPath(outURL);
Response.Write("<i-frame src='" + outURL + ".xls' style='display:none'></i-frame>");
System.IO.StreamWriter sw = new System.IO.StreamWriter(path + ".htm");
HtmlTextWriter htw = new HtmlTextWriter(sw);
sw.Write("blah blah blah<br>blah");
htw.Close();
sw.Close();
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
excelApp.Visible = true;
Microsoft.Office.Interop.Excel.Workbook wb = excelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); //this is the error line
wb = excelApp.Workbooks.Open(path + ".htm",0,false,5,"","",false,Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,"",true,false,0,true,false,false);
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet) wb.ActiveSheet;
sheet.get_Range("A1","GE1").EntireColumn.AutoFit();
sheet.Cells.Font.Size = 8;
VBIDE.VBComponent module=null;
module = wb.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule);
module.CodeModule.AddFromString(GetMacro());
wb.SaveAs(path+".xls",43,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);
wb.Close(false,Type.Missing,Type.Missing);


}
private static string GetMacro()
{
string retVal = "";
retVal+=("Sub FormatSheet()" + "\n");
retVal+=(" Range(\"A6:J13\").Select " + "\n");
retVal+=(" Selection.Font.ColorIndex = 3" + "\n");
retVal+=("End Sub");
return retVal;
}
In windows application I put some sample .htm file in folder and I opened that file as excel work book and run the macro i that work book.  It works fine.
For the above error I tried
1. I went to excel-macro-security-set it to low
2. DCOMCNFG settings. I gave full access
3. Adjusted the trust level for the project dll in assembly(.Net framework configuration)  

Answer
Hi

Did you give the permission to IUSR and IWAM?

I think any one of the line failing...

new Microsoft.Office.Interop.Excel.ApplicationClass
or
wb.SaveAs

make sure you have the office components got necessary permission on your web server.

Happy Programming!!

-srini