Topic: Early vs Late and the GAC

[kaefer]

Code: [Select]

            object rng = exl.GetType().GetProperty("Range", BindingFlags.SetProperty, null, null );


Cmdr,
try BindingFlags.GetProperty and also try passing in the parameters that a Range property  takes, e.g. two Cells.
Here's a link, again.

[David Hall]

Thanks kaefer.

I am still struggling with how to pass those values.  I tried using the link you provided, but couldn't make that work either.

[Jeff H]

Range is a property of the worksheet.
You need to get the property passing in the string for location then set it.
 
Maybe this will help
Will open a blank excel sheet and put current date and time in 'A1'.

Code: [Select]

   [CommandMethod("OpenExcelLateBinding")]
        public void OpenExcelLateBinding()
        {
            Type excelType = Type.GetTypeFromProgID("Excel.Application");
            object exl = Activator.CreateInstance(excelType);
            object wb = exl.GetType().InvokeMember("Workbooks", BindingFlags.GetProperty, null, exl, null);
            wb.GetType().InvokeMember("Add", BindingFlags.InvokeMethod, null, wb, null);

            object[] param = new Object[1];
            param[0] = true;

            exl.GetType().InvokeMember("Visible", BindingFlags.SetProperty, null, exl, param);
            object ws = exl.GetType().InvokeMember("ActiveSheet", BindingFlags.GetProperty, null, exl, null);

            param[0] = "A1";
            object rng = ws.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, ws, param);

            param[0] =  DateTime.Now.ToLongTimeString();
            rng.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, rng, param);
           
        }

 

[kaefer]

I am still struggling with how to pass those values.  I tried using the link you provided, but couldn't make that work either.

Now I had a closer look:

Code: [Select]

            object ws = exl.GetType().InvokeMember("Sheets", BindingFlags.GetProperty, null, exl, param);
            //          ^ WorkBook?
            object rng = exl.GetType().GetProperty("Range", BindingFlags.SetProperty, null, null );
            //           ^ WorkSheet? Why not InvokeMember? Do you mean UsedRange?

I still don't get why you don't use the Get/Set/Invoke functions we're touting all the time. They're much more pretty, take this example in F#:

Code: [Select]

let usedRange =
    getInstance "Excel.Application"
    |> get "ActiveWorkbook" null
    |> get "ActiveSheet" null
    |> get "UsedRange" null


[David Hall]

Well, thats easily explained.  I'm clueless.  I dont get to code enough, for a long enough time, to remember how this all works.  Plus I know just enough to be dangerous.

[David Hall]

Thanks Jeff, that helps.

[David Hall]

This piece of early bind code is what Im trying to recreate in late bind

Code: [Select]

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;

[kaefer]

This piece of early bind code is what Im trying to recreate in late bind

Now you're talking. Besides, I find your honesty refreshing. This isn't a way I would want to tread (I would prefer to do any lookup in .NET instead), but if you're stuck with an existing code base it may be a usable alternative.

Code: [Select]

    class Program
    {
        static void Main(string[] args)
        {
            using (App app = new App("Excel.Application", false))
            {
                object ws = app.Instance.Get("ActiveWorkbook").Get("Sheets").Get("Item", "Sheet1");
                object rng = ws.Get("Columns", "B");
                object find = rng.Invoke("Find",
                    "Foo",           //                          [In] object What,
                    Missing.Value,   //                          [In, Optional] object After,
                    -4163,           // XlFindLookIn.xlValues    [In, Optional] object LookIn,
                    1,               // XlLookAt.xlWhole         [In, Optional] object LookAt,
                    1,               // XlSearchOrder.xlByRows   [In, Optional] object SearchOrder,
                    1                // XlSearchDirection.xlNext [In, Optional] XlSearchDirection SearchDirection,
                                     //                          [In, Optional] object MatchCase,
                                     //                          [In, Optional] object MatchByte,
                                     //                          [In, Optional] object SearchFormat
                );
                object address = find.Get("Address",
                    Missing.Value,   //                          [In, Optional] object RowAbsolute,
                    Missing.Value,   //                          [In, Optional] object ColumnAbsolute,
                    1                // XlReferenceStyle.xlA1    [In, Optional] XlReferenceStyle ReferenceStyle,
                                     //                          [In, Optional] object External,
                                     //                          [In, Optional] object RelativeTo
                );
                Console.WriteLine(String.Format("Address of found value is {0}", address));
            }
        }
    }

I thought it helpful to wrap the outer COM instance in a class while implementing IDisposable.

Code: [Select]

    public class App : IDisposable
    {
        public App(string appName, bool create)
        {
            try { Instance = Marshal.GetActiveObject(appName); }
            catch
            {
                if (create)
                    Instance = Activator.CreateInstance(Type.GetTypeFromProgID(appName));
                else
                    throw (new SystemException(String.Format("Application not running: {0}", appName)));
            }
        }
        public object Instance { get; private set; }
        public void Dispose()
        {
            Marshal.ReleaseComObject(Instance);
        }
    }

And here are the aforementioned helpers again:

Code: [Select]

    static class ComExtensions
    {
        // Get, set and invoke for all objects
        public static object Get(this object o, string name, params object[] args)
        {
            return o.GetType().InvokeMember(name, BindingFlags.GetProperty, null, o, args);
        }
        public static void Set(this object o, string name, params object[] args)
        {
            o.GetType().InvokeMember(name, BindingFlags.SetProperty, null, o, args);
        }
        public static object Invoke(this object o, string name, params object[] args)
        {
            return o.GetType().InvokeMember(name, BindingFlags.InvokeMethod, null, o, args);
        }
    }