Topic: ...not declared. It may be inaccessible due to its protection level.

[Matt__W]

I'm trying to format an Excel spreadsheet using VB.NET and I'm getting a lot of warnings regarding the Excel formatting code (this worked in VBA).

oExcel.Selection.Borders(xlDiagonalDown).LineStyle = xlNone

I've imported Excel (Imports Microsoft.Office.Interop.Excel) and all other functions of my app work except for the parts where it needs to format the cells.  Any thoughts?

[Jeff H]

This worked for me using a windows form application
 

Code: [Select]

Imports Microsoft.Office.Interop.Excel
Public Class Form1

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click

        Dim exl As New Microsoft.Office.Interop.Excel.Application()
        Dim wb As Microsoft.Office.Interop.Excel.Workbook = exl.Workbooks.Open("C:\Test\ExcelTest.xlsx")
        Dim ws As Microsoft.Office.Interop.Excel.Worksheet = TryCast(exl.ActiveSheet, Microsoft.Office.Interop.Excel.Worksheet)

        exl.Visible = True
 
        Dim rang As Range = ws.Cells(1, 1)
 
       rang.Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick
        rang.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlDashDotDot
 
        '''''wb.Close(True, Type.Missing, Type.Missing)
        ''''exl.Quit()

    End Sub
End Class
 


[Matt__W]

Ahhh.... okay.  I think I see where it's throwing a wobbly.  Thanks!

[David Hall]

This worked for me using a windows form application
 

Code: [Select]

        Dim rang As Range = ws.Cells(1, 1)


I cant seem to make this work

this is what I have done

Code: [Select]

try
            {
                xlApp.Visible = true;
                xlApp.UserControl = true;
                Excel.Range currentFind = null;
                Excel.Range firstFind = null;
               
                currentFind = (Excel.Range)ws.Columns["B",Type.Missing ];

                firstFind = currentFind.Find(strDwgNum, Type.Missing, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlWhole, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, 0, 0, Type.Missing);
                string foundAddress = firstFind.get_Address(Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing);
                firstFind.Activate();
                int rowNum = firstFind.Row;

                Excel.Range rowRange = (Excel.Range)ws.Cells("A",rowNum);
                rowRange.Value2 = strDwgRev;
            }


[Jeff H]

Code: [Select]

        [CommandMethod("OpenExcelEarlyBind")]
        public void OpenExcelEarlyBind()
        {
            Microsoft.Office.Interop.Excel.Application exl = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook wb = exl.Workbooks.Open(@"C:\Test\ExcelTest.xlsx");
            Microsoft.Office.Interop.Excel.Worksheet ws = exl.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;

            ws.Range["A1"].Value = "Whatever";
            ws.Cells[2, 2] = "YoMama";

           
            exl.Visible = true;
        }
 


[David Hall]

Thanks Jeff, Its always something simple!  I was trying too hard.

[David Hall]

Jeff, I got everything working, but Im trying to make a GetOrCreate method.  Have you ever done that?

[David Hall]

I'm trying to use this

Code: [Select]

static object GetInstance(string appName)
        {
            return Marshal.GetActiveObject(appName);
        }
        static object CreateInstance(string appName)
        {
            return Activator.CreateInstance(Type.GetTypeFromProgID(appName));
        }
        static object GetOrCreateInstance(string appName)
        {
            try { return GetInstance(appName); }
            catch { return CreateInstance(appName); }
        }

[Jeff H]

Tried it here and have done it the past, but kaefer seems to knows the 'gotchas' when using reflection.
 
 

Code: [Select]

        [CommandMethod("OpenExcelLateBind")]
        public void OpenExcelLateBind()
        {
            Type excelType = Type.GetTypeFromProgID("Excel.Application");
            object exl = Activator.CreateInstance(excelType);
            object wb = exl.GetType().InvokeMember("Workbooks", BindingFlags.GetProperty, null, exl, null);
            object[] param = new object[1];
            param[0] = @"C:\Test\ExcelTest.xlsx";
            wb.GetType().InvokeMember("Open", BindingFlags.InvokeMethod, null, wb, param);
            param = new Object[1];
            param[0] = true;
            exl.GetType().InvokeMember("Visible", BindingFlags.SetProperty, null, exl, param);       
        }
 


[David Hall]

Thanks for the reply Jeff.  Isn't that a Late bind example?  I was trying to early bind and grab Excel if its open, or open a new instance if it wasn't

[exmachina]

In any case, do not forget to use Marshal.ReleaseComObject():
http://msdn.microsoft.com/en-us/library/system.runtime.interopservices.marshal.releasecomobject.aspx

[exmachina]

Off topic:
COM events can also be used with late-binding, look at (System.Runtime...)IConnectionPointCointainer

[Jeff H]

Hey Bill,
Are you trying something like this?

Code: [Select]

  [CommandMethod("OpenExcelEarlyBind")]
        public void OpenExcelEarlyBind()
        {         
            ExcelApp.Application exl;
            try
            {
                exl = (ExcelApp.Application)Marshal.GetActiveObject("Excel.Application");
            }
            catch
            {
                exl = new Microsoft.Office.Interop.Excel.Application();
                exl.Workbooks.Open(@"C:\Test\ExcelTest.xlsx");
            }
            if (exl.Workbooks[exl.Workbooks.Count].Name != "ExcelTest.xlsx")
            {
                exl.Workbooks.Open(@"C:\Test\ExcelTest.xlsx");
            }
             
           
            ExcelApp.Worksheet ws = exl.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
            exl.Visible = true;

            ws.Range["A1"].Value = DateTime.Now.ToLongTimeString();
            Marshal.FinalReleaseComObject(exl);
        }

Thanks for info exmachina
 

[David Hall]

Hey Bill,
Are you trying something like this?

I am assuming you mean me.

Code: [Select]

  [CommandMethod("OpenExcelEarlyBind")]
        public void OpenExcelEarlyBind()
        {         
            ExcelApp.Application exl;
            try
            {
                exl = (ExcelApp.Application)Marshal.GetActiveObject("Excel.Application");
            }
            catch
            {
                exl = new Microsoft.Office.Interop.Excel.Application();
                exl.Workbooks.Open(@"C:\Test\ExcelTest.xlsx");
            }
            if (exl.Workbooks[exl.Workbooks.Count].Name != "ExcelTest.xlsx")
            {
                exl.Workbooks.Open(@"C:\Test\ExcelTest.xlsx");
            }
             
           
            ExcelApp.Worksheet ws = exl.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
            exl.Visible = true;

            ws.Range["A1"].Value = DateTime.Now.ToLongTimeString();
            Marshal.FinalReleaseComObject(exl);
        }


Thats exactly what I was trying to do, thanks

David

[Jeff H]

I am assuming you mean me.

da hell did I get Bill from?
Sorry David must have saw something with the name Bill right befor I posted it,
 glad it helped