Author Topic: ...not declared. It may be inaccessible due to its protection level.  (Read 20774 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
 
 

David Hall

  • Automatic Duh Generator
  • King Gator
  • Posts: 4075
Re: ...not declared. It may be inaccessible due to its protection level.
« Reply #15 on: October 31, 2011, 03:12:27 PM »
Thanks Jeff,  another question for you, when you did the test for open Excel, I got that perfectly.  Is there a test to see if the xlsx is open?  It appears to work perfectly if its open or not, I just want to make this rock solid. this is what I'm doing
Code: [Select]
Excel.Application xlApp;
            bool xLopen = false;
            try
            {
                xlApp = (Excel.Application)Marshal.GetActiveObject("Excel.Application");
                xLopen = true;
            }
            catch
            {
                xlApp = new Microsoft.Office.Interop.Excel.Application();
            }
            Workbook wb = xlApp.Workbooks.Open(strMyFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            Worksheet ws = wb.Sheets["Sheet1"] as Worksheet;
            xlApp.Visible = true;
            xlApp.UserControl = true;
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 #16 on: October 31, 2011, 03:13:43 PM »
BTW your example was perfect.  I had never used Marshal before, nor had I ever linked to another application.  I had only done it through VBA (ack)
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 #17 on: October 31, 2011, 03:24:26 PM »
Here is the finished product at this point
Code: [Select]
[CommandMethod("PLog")]
        public static void ProjectLog()
        {
            Document doc = acApp.DocumentManager.MdiActiveDocument;
            Database db = doc.Database;
            Editor ed = doc.Editor;
            string DwgPath = Path.GetDirectoryName(acApp.DocumentManager.MdiActiveDocument.Name);
            string ProjNum = DwgPath.Substring(DwgPath.LastIndexOf("\\") + 1);
            PromptSelectionOptions pso = new PromptSelectionOptions();
            TypedValue[] filter = new TypedValue[2] { new TypedValue((int)DxfCode.Start, "INSERT"), new TypedValue((int)DxfCode.BlockName, "VTEP-INFO,TEP-INFO,TEP-CIP,VTEP-CIP,VUES-INFO,UES-INFO,UES-CIP,VUES-CIP") };
            SelectionFilter selFilter = new SelectionFilter(filter);
            PromptSelectionResult psr = ed.SelectAll(selFilter);
            if (psr.Status != PromptStatus.OK)
            {
                acApp.ShowAlertDialog("Invalid Titleblock in drawing!");
                return;
            }
            string strDwgLine1 = GetAttributeValueFromBlock(db, psr.Value.GetObjectIds()[0], "TITLE1");
            string strDwgLine2 = GetAttributeValueFromBlock(db, psr.Value.GetObjectIds()[0], "TITLE2");
            string strDwgNum = GetAttributeValueFromBlock(db, psr.Value.GetObjectIds()[0], "NUM");
            string strDwgVendor = GetAttributeValueFromBlock(db, psr.Value.GetObjectIds()[0], "VENDORNAME");
            string strDwgVendorNumber = GetAttributeValueFromBlock(db, psr.Value.GetObjectIds()[0], "VENDORNUMBER");
            string strUserPath = GetUser(System.Environment.UserName);
            string strMyFile = string.Concat(strUserPath, "\\", ProjNum, "\\", ProjNum, ".xlsx");
            string strFilename = string.Concat(ProjNum + ".xlsx");

            Excel.Application xlApp;
            bool xLopen = false;
            try
            {
                xlApp = (Excel.Application)Marshal.GetActiveObject("Excel.Application");
                xLopen = true;
            }
            catch
            {
                xlApp = new Microsoft.Office.Interop.Excel.Application();
            }

            Workbook wb = xlApp.Workbooks.Open(strMyFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            Worksheet ws = wb.Sheets["Sheet1"] as Worksheet;
            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;

            if (!string.IsNullOrEmpty(strDwgVendor))
            {
                ws.Cells[rowNum, 5] = strDwgLine1 + "," + strDwgLine2 + "/" + strDwgVendor + "," + strDwgVendorNumber;
            }
            else
            {
                ws.Cells[rowNum, 5] = strDwgLine1 + "," + strDwgLine2;
            }
            ws.Cells[rowNum, 1] = GetAttributeValueFromBlock(db, psr.Value.GetObjectIds()[0], "SHT");
            ws.Cells[rowNum, 3] = GetAttributeValueFromBlock(db, psr.Value.GetObjectIds()[0], "REV");
            ws.Cells[rowNum, 4] = GetAttributeValueFromBlock(db, psr.Value.GetObjectIds()[0], "CODE");
            if (xLopen)
            {
                wb.Save();
            }
            else
            {
                wb.Close(true, Type.Missing, Type.Missing);
                xlApp.Quit();
            }
            Marshal.FinalReleaseComObject(xlApp);
        }

and the helper methods
Code: [Select]
public static string GetAttributeValueFromBlock(Database db, ObjectId blockRefId, string attName)
{
  string textString = null;
  using (Transaction tr = db.TransactionManager.StartTransaction())
  {
    BlockReference blockRef = tr.GetObject(blockRefId, OpenMode.ForRead) as BlockReference;
    if (blockRef != null)
    {
      foreach (ObjectId id in blockRef.AttributeCollection)
      {
        AttributeReference attRef = tr.GetObject(id, OpenMode.ForRead) as AttributeReference;
        if ((attRef != null) && (attRef.Tag.ToUpper() == attName))
         {
            textString = attRef.TextString;
            break;
         }
        }
      }
    tr.Commit();
    }
  return textString;
}
private static string GetUser(string User)
{
switch (User.ToUpper())
{
case "UA02038":
return @"\\tuslpna01\autocad\Reproductions_Area\000-Hall";
default:
return @"No Path Defined";
}
« Last Edit: October 31, 2011, 03:29:31 PM by CmdrDuh »
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 #18 on: October 31, 2011, 04:00:48 PM »
Is there a test to see if the xlsx is open? 
Not sure if you mean excel(using a method instead try block?) or the file.
I forgot to add logic if it is not the Active workbook.
Code: [Select]
        [CommandMethod("OpenExcelEarlyBind")]
        public void OpenExcelEarlyBind()
        {         
            ExcelApp.Application exl;
            if (IsExcelRunning())
            {
                exl = (ExcelApp.Application)Marshal.GetActiveObject("Excel.Application");               
            }
            else
            {
                exl = new Microsoft.Office.Interop.Excel.Application();
               
            }
            exl.Visible = true;

            if (exl.Workbooks.Count < 1 )
            {
                exl.Workbooks.Open(@"C:\Test\ExcelTest.xlsx");
            }
           
           ///////Will crash if you check and no workbooks are open and reason for precvious staement
            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;     

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

        bool IsExcelRunning()
        {
            Process[] pros = Process.GetProcesses();
            foreach (var process in pros)
            {
                if (process.ProcessName == "EXCEL")
                {
                    return true;
                }
            }
            return false;
        }

 
 
 

David Hall

  • Automatic Duh Generator
  • King Gator
  • Posts: 4075
Re: ...not declared. It may be inaccessible due to its protection level.
« Reply #19 on: October 31, 2011, 04:20:47 PM »
Well, at first, I meant the file, but now that I think about it, both.  I was able to make your code work perfectly, so today has been a success.

That being said, I was trying to make this work, but haven't quite gotten it figured out.
Code: [Select]
  xlApp = (Excel.Application )GetOrCreateInstance("Excel.Application");
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); }
        }
« Last Edit: October 31, 2011, 04:50:05 PM by CmdrDuh »
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 #20 on: October 31, 2011, 04:50:45 PM »
I got the above working, now to get my bool to work.
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 #21 on: October 31, 2011, 05:52:41 PM »
I just cant seem to make this work.  The else Activate is not working
 

Code: [Select]
if (!xLopen)
            {
                wb = xlApp.Workbooks.Open(strMyFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            }
            else
            {
                wb = Microsoft.Office.Interop.Excel.Workbooks[strFilename + "xlsx"].Activate();
            }
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 #22 on: October 31, 2011, 06:37:31 PM »
Activate()  does not return a Workbook it is marked as void
 
Code: [Select]
            wb = xlApp.Workbooks[strFilename + "xlsx"];
            wb.Activate();

 
 

kdub_nz

  • Mesozoic keyThumper
  • SuperMod
  • Water Moccasin
  • Posts: 2132
  • class keyThumper<T>:ILazy<T>
Re: ...not declared. It may be inaccessible due to its protection level.
« Reply #23 on: October 31, 2011, 08:36:03 PM »

Good thread guys !
Called Kerry in my other life
Retired; but they dragged me back in !

I live at UTC + 13.00

---
some people complain about loading the dishwasher.
Sometimes the question is more important than the answer.

David Hall

  • Automatic Duh Generator
  • King Gator
  • Posts: 4075
Re: ...not declared. It may be inaccessible due to its protection level.
« Reply #24 on: November 01, 2011, 08:48:12 AM »
Well that makes sense.  Funny thing is I must have been using old or outdated code from MS b/c I was coping from MSDN I thought.  Off to make those changes.
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 #25 on: November 01, 2011, 11:20:58 AM »
This reminded me that I have been meaning to write something that made dealing with attributes easier.
Sometimes it makes life easier to wrap up complexity into small chunks so you do not have to think about too much at one time.
 
This is not a good one as I have not spent much time thinking about it and threw it together quickly,
 and would need help from all you guys anyway to have one that was worth using, but maybe this will spark ideas.
My first stab was getting the textstring values like a indexer method using 'AttributedTextInfo' class.
 
Code: [Select]
        [CommandMethod("TitleBlock")]
        public void TitleBlock()
        {
            Document doc = Application.DocumentManager.MdiActiveDocument;
            Database db = doc.Database;
            Editor ed = doc.Editor;
            using (Transaction trx = db.TransactionManager.StartTransaction())
            {
                ObjectId brefId = ed.GetEntity("\nSelect Block").ObjectId;
                AttributedTextInfo atts = new AttributedTextInfo(brefId);
                string val = atts["Test"];
                Application.ShowAlertDialog(val);
                string val2 = atts["Test2"];
                Application.ShowAlertDialog(val2);
               
                trx.Commit();
            }                   
        }

Code that once it is designed correctly you should not think about it, have to look it, don't care about it, it just does what it suppose to etc.....
 
Code: [Select]
public class AttributedTextInfo : IEnumerable
    {
        private ObjectId _objectId;
        private Dictionary<string, string> _attributes;       
        public AttributedTextInfo(ObjectId brefId)
        {
            _objectId = brefId;
            _attributes = new Dictionary<string, string>();
            getAttributes();
        }
 
        private void getAttributes()
        {
            using (Transaction tr = _objectId.Database.TransactionManager.StartTransaction())
            {
                BlockReference blockRef = tr.GetObject(_objectId, OpenMode.ForRead) as BlockReference;
                if (blockRef != null)
                {
                    foreach (ObjectId id in blockRef.AttributeCollection)
                    {
                        AttributeReference attRef = tr.GetObject(id, OpenMode.ForRead) as AttributeReference;
                        if ((attRef != null))
                        {
                            _attributes.Add(attRef.Tag, attRef.TextString);
     
                        }
                    }
                }
                tr.Commit();
            }
        }
        public IEnumerator GetEnumerator()
        {
            return _attributes.GetEnumerator();
        }
        public string this[string tag]
        {
            get { return _attributes[tag.ToUpper()]; }
        }

    }

Pitfalls--no upadating values, could add logic for getting the val from definition to have a bool IsDefaultText, etc..........
 
 

David Hall

  • Automatic Duh Generator
  • King Gator
  • Posts: 4075
Re: ...not declared. It may be inaccessible due to its protection level.
« Reply #26 on: May 23, 2012, 11:54:50 AM »
OK I know this is really old thread, but it does apply.  Jeff, I have been using your code, and it works great, but I can't seem to get the computer to let go of the EXCEL.EXE process.
Im using this: Marshal.FinalReleaseComObject(xlApp)


I step through the code, and watch that line get processed, but task manager still shows the process running.


I used this from you as well
Code: [Select]
public static bool IsExcelRunning()
        {
            Process[] pros = Process.GetProcesses();
            foreach (var process in pros)
            {
                if (process.ProcessName == "EXCEL")
                {
                    return true;
                }
            }
            return false;
        }




update - i was watching the task manager, and it did not let go of the process til I closed the dwg
« Last Edit: May 23, 2012, 12:04:29 PM by CmdrDuh »
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 #27 on: May 23, 2012, 12:36:35 PM »
That great to hear to its been working okay as it was thrown together quickly and for example and already seeing better ways to implement it.
 
When scrolling down quickly my first thought was the variable at the class level holding the excel process might have something to do with it, but

Quote from: MSDN

The FinalReleaseComObject method releases the managed reference to a COM object. Calling this method is equivalent to calling the ReleaseComObject method in a loop until it returns 0 (zero).
When the reference count on the COM object becomes 0, the COM object is usually freed, although this depends on the COM object's implementation and is beyond the control of the runtime. However, the RCW can still exist, waiting to be garbage-collected.
LINK
 
When the drawing was closed it might have caused a GarbageCollection.
 
Just for testing and REMOVE after testing add
GC.Collect(2);
2 or 3 times and see if it ends.
 
Again Remove it.
 
« Last Edit: May 23, 2012, 12:40:53 PM by Jeff H »

David Hall

  • Automatic Duh Generator
  • King Gator
  • Posts: 4075
Re: ...not declared. It may be inaccessible due to its protection level.
« Reply #28 on: May 23, 2012, 12:52:33 PM »
Well I added it 3 times like you suggested but it didn't go away til the dwg closed. argh, this is a pain.  The real pain is when I run a script to access many dwg files, the cpu runs too fast to let excel go away before it gets to the next dwg and creates excel again.
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 #29 on: May 23, 2012, 01:03:49 PM »
Are you calling Quit first?
Code - C#: [Select]
  1.  
  2. xlApp.Quit()
  3. Marshal.FinalReleaseComObject(xlApp)
  4.  

 

David Hall

  • Automatic Duh Generator
  • King Gator
  • Posts: 4075
Re: ...not declared. It may be inaccessible due to its protection level.
« Reply #30 on: May 23, 2012, 01:05:35 PM »
yea, thats what is so frustrating.  Here is more of the code
Code: [Select]
if (xLopen)
            {
                acApp.ShowAlertDialog("Excel will now close!");
            }
           
                wb.Close(true, Type.Missing, Type.Missing);
                xlApp.Quit();
                Marshal.FinalReleaseComObject(xlApp);
                //GC.Collect(2);
                //GC.Collect(2);
                //GC.Collect(2);
        }
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 #31 on: May 23, 2012, 01:06:31 PM »
Entire thing for reference
Code: [Select]
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;
using System.Diagnostics;
using Autodesk.AutoCAD.ApplicationServices;
using Autodesk.AutoCAD.DatabaseServices;
using Autodesk.AutoCAD.EditorInput;
using Autodesk.AutoCAD.Runtime;
using System.IO;
using System.Reflection;
using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;
using acApp = Autodesk.AutoCAD.ApplicationServices.Application;

namespace ProjLog
{
    public class Plog2012
    {
        [CommandMethod("PLog")]
        public static void ProjectLog()
        {
            Document doc = acApp.DocumentManager.MdiActiveDocument;
            Database db = doc.Database;
            Editor ed = doc.Editor;
            string DwgPath = Path.GetDirectoryName(acApp.DocumentManager.MdiActiveDocument.Name);
            string ProjNum = DwgPath.Substring(DwgPath.LastIndexOf("\\") + 1);
            PromptSelectionOptions pso = new PromptSelectionOptions();
            TypedValue[] filter = new TypedValue[2] { new TypedValue((int)DxfCode.Start, "INSERT"), new TypedValue((int)DxfCode.BlockName, "VTEP-INFO,TEP-INFO,TEP-CIP,VTEP-CIP,VUES-INFO,UES-INFO,UES-CIP,VUES-CIP") };
            SelectionFilter selFilter = new SelectionFilter(filter);
            PromptSelectionResult psr = ed.SelectAll(selFilter);
            if (psr.Status != PromptStatus.OK)
            {
                acApp.ShowAlertDialog("Invalid Titleblock in drawing!");
                return;
            }
            string strDwgLine1 = GetAttributeValueFromBlock(db, psr.Value.GetObjectIds()[0], "TITLE1");
            string strDwgLine2 = GetAttributeValueFromBlock(db, psr.Value.GetObjectIds()[0], "TITLE2");
            string strDwgNum = GetAttributeValueFromBlock(db, psr.Value.GetObjectIds()[0], "NUM");
            string strDwgVendor = GetAttributeValueFromBlock(db, psr.Value.GetObjectIds()[0], "VENDORNAME");
            string strDwgVendorNumber = GetAttributeValueFromBlock(db, psr.Value.GetObjectIds()[0], "VENDORNUMBER");
            string strUserPath = GetUser(System.Environment.UserName);
            string strMyFile = string.Concat(strUserPath, "\\", ProjNum, "\\", ProjNum, ".xlsx");
            string strFilename = string.Concat(ProjNum + ".xlsx");

            Excel.Application xlApp;
            bool xLopen = IsExcelRunning();
            xlApp = (Excel.Application)GetOrCreateInstance("Excel.Application");
            Workbook wb;
            if (xlApp.Workbooks.Count < 1)
            {
                wb = xlApp.Workbooks.Open(strMyFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            }
            else
            {
                wb = xlApp.Workbooks[strFilename];
            }
            Worksheet ws = wb.Sheets["Sheet1"] as Worksheet;
            xlApp.Visible = true;
            xlApp.UserControl = false ;

            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;

            if (!string.IsNullOrEmpty(strDwgVendor))
            {
                ws.Cells[rowNum, 5] = strDwgLine1 + "," + strDwgLine2 + "/" + strDwgVendor + "," + strDwgVendorNumber;
            }
            else
            {
                ws.Cells[rowNum, 5] = strDwgLine1 + "," + strDwgLine2;
            }
            ws.Cells[rowNum, 1] = GetAttributeValueFromBlock(db, psr.Value.GetObjectIds()[0], "SHT");
            ws.Cells[rowNum, 3] = GetAttributeValueFromBlock(db, psr.Value.GetObjectIds()[0], "REV");
            ws.Cells[rowNum, 4] = GetAttributeValueFromBlock(db, psr.Value.GetObjectIds()[0], "CODE");
            if (xLopen)
            {
                acApp.ShowAlertDialog("Excel will now close!");
            }
           
                wb.Close(true, Type.Missing, Type.Missing);
                xlApp.Quit();
                Marshal.FinalReleaseComObject(xlApp);
                //GC.Collect(2);
                //GC.Collect(2);
                //GC.Collect(2);
        }

        public static bool IsExcelRunning()
        {
            Process[] pros = Process.GetProcesses();
            foreach (var process in pros)
            {
                if (process.ProcessName == "EXCEL")
                {
                    return true;
                }
            }
            return false;
        }



        static object GetOrCreateInstance(string appName)
        {
            try { return GetInstance(appName); }
            catch { return CreateInstance(appName); }
        }
        static object GetInstance(string appName)
        {

            return Marshal.GetActiveObject(appName);

        }
        static object CreateInstance(string appName)
        {

            return Activator.CreateInstance(Type.GetTypeFromProgID(appName));

        }
       
        public static string GetAttributeValueFromBlock(Database db, ObjectId blockRefId, string attName)
        {
            string textString = null;
            using (Transaction tr = db.TransactionManager.StartTransaction())
            {
                BlockReference blockRef = tr.GetObject(blockRefId, OpenMode.ForRead) as BlockReference;
                if (blockRef != null)
                {
                    foreach (ObjectId id in blockRef.AttributeCollection)
                    {
                        AttributeReference attRef = tr.GetObject(id, OpenMode.ForRead) as AttributeReference;
                        if ((attRef != null) && (attRef.Tag.ToUpper() == attName))
                        {
                            textString = attRef.TextString;
                            break;
                        }
                    }
                }
                tr.Commit();
            }
            return textString;
        }
        private static string GetUser(string User)
        {
            switch (User.ToUpper())
            {
                case "UA02038":
                    return @"\\tuslpna01\autocad\Reproductions_Area\000-Hall";
                case "UA50151":
                    return @"\\tuslpna01\autocad\Reproductions_Area\000-Richardson";
                case "UA00648":
                    return @"\\tuslpna01\autocad\Reproductions_Area\000-Livingstone";
                case "UA50270":
                    return @"\\tuslpna01\autocad\Reproductions_Area\000-Dominguez";
                case "UA00071":
                    return @"\\tuslpna01\autocad\Reproductions_Area\000-Taylor";
                case "UA50050":
                    return @"\\tuslpna01\autocad\Reproductions_Area\000-Lopez";
                case "UA00664":
                    return @"\\tuslpna01\autocad\Reproductions_Area\000-Olivas";
                case "UA50463":
                    return @"\\tuslpna01\autocad\Reproductions_Area\000-Padilla";
                case "UA50271":
                    return @"\\tuslpna01\autocad\Reproductions_Area\000-ALVIRA";
                case "UA51273":
                    return @"\\tuslpna01\autocad\Reproductions_Area\000-Heritage";
                default:
                    return @"No Path Defined";
            }
        }
    }
}
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)

MexicanCustard

  • Swamp Rat
  • Posts: 705
Re: ...not declared. It may be inaccessible due to its protection level.
« Reply #32 on: May 24, 2012, 07:44:33 AM »
I can't resist so I'll trow this out there. Anytime I'm trying to write/read excel files from AutoCAD I use http://closedxml.codeplex.com/.  It's based off of MS OpenXML.  It's so much easier than trying to use COM, it's syntax makes more sense to me, and MS Excel doesn't have to be installed on the machine.
Revit 2019, AMEP 2019 64bit Win 10