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(ob jMissing, objMissing,
objMissing, objMissing));
objNewSheet.Name = arrPDPs[intIndex];
objNewSheet.get_Range("A1",
"S216").PasteSpecial(ExcelApp.XlPasteType.xlPasteC olumnWidths,
ExcelApp.XlPasteSpecialOperation.xlPasteSpecialOpe rationNone,
objMissing, objMissing);
objNewSheet.get_Range("A1",
"S216").PasteSpecial(ExcelApp.XlPasteType.xlPasteA ll,
ExcelApp.XlPasteSpecialOperation.xlPasteSpecialOpe rationNone,
objMissing, objMissing);

objModule =
objNewBook.VBProject.VBComponents.Add(VBIDE.vbext_ ComponentType.vbext_ct_StdModule);
objModule.CodeModule.AddFromFile(GetFilePathContex t() +
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.ConvertToR ecordset(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.ReleaseComO bject (objApp);
if(objSheet != null)
System.Runtime.InteropServices.Marshal.ReleaseComO bject (objSheet);
if(objBook != null)
System.Runtime.InteropServices.Marshal.ReleaseComO bject (objBook);
if(objNewBook != null)
System.Runtime.InteropServices.Marshal.ReleaseComO bject
(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