0

My Excel can download data from a data provider via an AddIn. From the help pages of the provider, I found out (and tested) that it is possible to "Refresh" the data by calling the following command in VBA:

Application.COMAddIns("PowerlinkCOMAddIn.COMAddIn").Object.RefreshWorkbook

After spending a couple of hours searching on the internet I couldn't find an answer on how I can call exactly the same method by using C#.

As I have many of these spreadsheets that need to be updated regularly, I am trying to automate the process.

  1. One way to probably achieve this would be to inject a new macro every time I open the spreadsheets with the above command and call it, but I would prefer calling it directly with C# if possible.
  2. A second solution would be to use Python or another language to download the data in CSV format and link my spreadsheets to the CSVs but this process also seems an overkill.

Many thanks in advance.

stratar
  • 119
  • 7
  • 1
    dynamic obj = Application...Object; obj.Whatever(); – Hans Passant Apr 13 '17 at 19:19
  • Thank you for that. I have now changed my code according to your example. dynamic obj = Application.COMAddIns("PowerlinkCOMAddIn.COMAddIn").Object but when I try to run obj.RefreshWorkbook() it says that the method does not exist. Any idea? – stratar Apr 13 '17 at 19:49
  • I ended up with a work around: inserting (injecting) a Sub procedure in a VBA module programmatically via C# which then calls the code above. The problem I faced by using this solution is that sometimes when trying to run the macro I was getting an error 438. I managed to get rid of it by using `System.Threading.Thread.Sleep(10000);` after opening Excel, which means that I was getting the error because probably the COM Addin was not loaded when I wanted to run the VBA code. So, the question now becomes: "How can I make sure that all Addins in Excel are loaded" before I try to access them? – stratar Apr 27 '17 at 10:26
  • Hmm, odd, heck of a bug. I suppose you could hack it by sniffing the object on the first try. Preferably a simple do-nothing property, in a loop, catching the exception and delaying for a hundred milliseconds and increasing the delay after every attempt. Don't loop forever. – Hans Passant Apr 27 '17 at 11:08
  • Bug on which side? Excel or the COM Addin? What do you mean by _"sniffing the object on the first try."_? could you please elaborate a bit more? I greatly appreciate any help. – stratar Apr 27 '17 at 11:28
  • "sniffing" == trying to use a method or property of the add-in. With an exception telling you that it isn't done initializing yet. I'm just guessing at the best approach, given that we don't know anything about the add-in. Talking to the programmer is always best, use a telephone. – Hans Passant Apr 27 '17 at 11:45
  • I see what you mean. I tried that and was not very successful. I am in the process of getting technical support from the company that has created the addin. But going through the different levels of technical support and finding the right person has proved to be tricky! – stratar Apr 27 '17 at 12:20
  • And asking complete strangers on the Internet is not. Funny how that works. – Hans Passant Apr 27 '17 at 12:35

1 Answers1

0

You can use the late-biding technology (Reflection in .net) for calling methods and properties without casting an object to the actual type, see Type.InvokeMember for more information. 

 Application.COMAddIns("PowerlinkCOMAddIn.COMAddIn").Object.GetType().InvokeMember("RefreshWorkbook" ...

Also you may find the HowTo: Communicate with a COM add-in from a standalone application article helpful. A sample code in C# is included.

Eugene Astafiev
  • 47,483
  • 3
  • 24
  • 45
  • I am unable to obtain our custom COM interface from our addin's Object, but I can call individual methods via reflection. – R.J. Dunnill Mar 25 '21 at 17:06