Author Topic: ...not declared. It may be inaccessible due to its protection level.  (Read 20775 times)

0 Members and 1 Guest are viewing this topic.

Matt__W

  • Seagull
  • Posts: 12955
  • I like my water diluted.
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).

Quote
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?
Autodesk Expert Elite
Revit Subject Matter Expert (SME)
Owner/FAA sUAS Pilot @ http://skyviz.io

Jeff H

  • Needs a day job
  • Posts: 6149
Re: ...not declared. It may be inaccessible due to its protection level.
« Reply #1 on: September 21, 2011, 05:29:43 PM »
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
 
« Last Edit: September 21, 2011, 05:34:16 PM by Jeff H »

Matt__W

  • Seagull
  • Posts: 12955
  • I like my water diluted.
Re: ...not declared. It may be inaccessible due to its protection level.
« Reply #2 on: September 22, 2011, 08:14:16 AM »
Ahhh.... okay.  I think I see where it's throwing a wobbly.  Thanks!
Autodesk Expert Elite
Revit Subject Matter Expert (SME)
Owner/FAA sUAS Pilot @ http://skyviz.io

David Hall

  • Automatic Duh Generator
  • King Gator
  • Posts: 4075
Re: ...not declared. It may be inaccessible due to its protection level.
« Reply #3 on: October 28, 2011, 02:18:33 PM »
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;
            }
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second.
Sometimes the question is more important than the answer. (Thanks Kerry for reminding me)

Jeff H

  • Needs a day job
  • Posts: 6149
Re: ...not declared. It may be inaccessible due to its protection level.
« Reply #4 on: October 28, 2011, 02:48:49 PM »
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

  • Automatic Duh Generator
  • King Gator
  • Posts: 4075
Re: ...not declared. It may be inaccessible due to its protection level.
« Reply #5 on: October 28, 2011, 03:11:39 PM »
Thanks Jeff, Its always something simple!  I was trying too hard.
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second.
Sometimes the question is more important than the answer. (Thanks Kerry for reminding me)

David Hall

  • Automatic Duh Generator
  • King Gator
  • Posts: 4075
Re: ...not declared. It may be inaccessible due to its protection level.
« Reply #6 on: October 28, 2011, 05:29:10 PM »
Jeff, I got everything working, but Im trying to make a GetOrCreate method.  Have you ever done that?
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second.
Sometimes the question is more important than the answer. (Thanks Kerry for reminding me)

David Hall

  • Automatic Duh Generator
  • King Gator
  • Posts: 4075
Re: ...not declared. It may be inaccessible due to its protection level.
« Reply #7 on: October 28, 2011, 05:29:57 PM »
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); }
        }
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second.
Sometimes the question is more important than the answer. (Thanks Kerry for reminding me)

Jeff H

  • Needs a day job
  • Posts: 6149
Re: ...not declared. It may be inaccessible due to its protection level.
« Reply #8 on: October 28, 2011, 05:50:13 PM »
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

  • Automatic Duh Generator
  • King Gator
  • Posts: 4075
Re: ...not declared. It may be inaccessible due to its protection level.
« Reply #9 on: October 28, 2011, 06:18:36 PM »
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
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second.
Sometimes the question is more important than the answer. (Thanks Kerry for reminding me)

exmachina

  • Guest

exmachina

  • Guest
Re: ...not declared. It may be inaccessible due to its protection level.
« Reply #11 on: October 28, 2011, 07:43:24 PM »
Off topic:
COM events can also be used with late-binding, look at (System.Runtime...)IConnectionPointCointainer

Jeff H

  • Needs a day job
  • Posts: 6149
Re: ...not declared. It may be inaccessible due to its protection level.
« Reply #12 on: October 28, 2011, 07:58:50 PM »
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

  • Automatic Duh Generator
  • King Gator
  • Posts: 4075
Re: ...not declared. It may be inaccessible due to its protection level.
« Reply #13 on: October 31, 2011, 02:33:41 PM »
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
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second.
Sometimes the question is more important than the answer. (Thanks Kerry for reminding me)

Jeff H

  • Needs a day job
  • Posts: 6149
Re: ...not declared. It may be inaccessible due to its protection level.
« Reply #14 on: October 31, 2011, 02:45:08 PM »
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