|
Would you be kind enough to provide instructions on embedding the type library generated by regasm (I noticed you removed those instructions in July)?
Currently, as an automation add-in or a pure assembly, you can generate the type lib using the regasm /tlb. You cannot access the automation UDFs in cells directly without that tlb file in the same directory as the DLL file itself. This makes it annoying to have two files for redistribution (compared with just one in say ATL COM, where the tlb is embedded).
Is there anyway to do this with automation/.NET?
|
|
|
|
|
I developed a function add-in for excel that takes data from internet. When I try to refresh (in external data toolbar) the data in excel worksheet, no function is called. So, I have to refresh all the instances of that function selecting each cell and refreshing them by hand, that takes a lot of time.
Is there any way to refresh the whole excel worksheet when using .Net add-ins?
|
|
|
|
|
I can see this is an old question but the solution may be relevant for others.
There are in fact two solutions:
1) instead of pressing F9 / Shift+F9 you can force every cell to recalculate by pressing Ctrl+Alt+F9 or Shift+Ctrl+Alt+F9
2) append the following to your worksheet function: "+NOW()*0"
Hope that helps
|
|
|
|
|
Hello,
Has anyone tried this in C++/CLI. I created a class library and added the following code:
using namespace System;
using namespace System::Runtime::InteropServices;
namespace ExcelFunction2 {
[ClassInterface(ClassInterfaceType::AutoDual)]
public ref class Functions
{
public:
Functions(){}
double AddC(double v1, double v2)
{
return v1 + v2;
}
[ComRegisterFunctionAttribute]
static void RegisterFunction(Type t)
{
Microsoft::Win32::RegistryKey^ root = Microsoft::Win32::Registry::ClassesRoot;
root->CreateSubKey( "CLSID\\{" + t.GUID.ToString()->ToUpper() + "}\\Programmable");
}
[ComUnregisterFunctionAttribute]
static void UnregisterFunction(Type t)
{
Microsoft::Win32::RegistryKey^ root = Microsoft::Win32::Registry::ClassesRoot;
root->DeleteSubKey( "CLSID\\{" + t.GUID.ToString()->ToUpper() + "}\\Programmable");
}
};
}
but it doesn't work. I got a little bit further by adding the following to the assembly info:
[assembly:ComVisible(true)];
This makes ExcelFunction2.Functions appear in the list of automation objects in excel, but the function AddC is invisible.
Yours
David.
|
|
|
|
|
David,
I was wondering you you managed to workout the logic for this. If so would you please suggest how I might get around the problem. I am using Visual Studio 2005 and Excel 2003. After compiling I am able to add the DLL as an Automation Add-in. However, the Insert Function dialog does not list the function.
I would really appreciate some help.
Arnab
|
|
|
|
|
Hi Guys,
First of all, thanks for the great article!
The plugin works perfectly on the local machine, but when I relocate it to others it breaks down. I do register it with regasm, after that I do see it in the excel addin list, but when I am trying to reference it from spreadsheet - the functions are not in the list anymore.
Also, from VBA - I can see the namespace and classes, but when I try to reference them I get 'Automation Error'...
Your assistance is greately appreciated.
-igor
---------------------------
Igor R. Manassypov, M.Eng.,
imanassypov@ci.com
Network Architect
CI Investments
Toronto, Canada
---------------------------
|
|
|
|
|
OK I found the answer myself. Microsoft have a patch solution posted back in december that works for Office XP, but has not been included in the office xp update.
http://support.microsoft.com/kb/908002/en-us
I think the latest updates to office 2003 include it already so it is only needed for office XP only users using .net framework 2.0 add-ins.
Well it works for me anyway...what a pity clickonce deploy packages pre-requisite as a setup.exe and .msi which i don;t allow to download form my site...which was the whole point of choosing the .deploy/manifest solution check box in the click once options anyway..GO FIGURE.
Jeltz
|
|
|
|
|
I've just stumbled upon this solution myself and was about to post. Getting the latest office SP2 works just as well...
Have you tried achieving the same with vs03? I have the suspicion it will work on earlier patches.
---------------------------
Igor R. Manassypov, M.Eng.,
imanassypov@ci.com
Network Architect
CI Investments
Toronto, Canada
---------------------------
|
|
|
|
|
I don't know about you but it took me about 18 hours to find any reference to KB908002 anywhere; trouble was no error message and #NAME in excel is sooo generic. complicated by the fact my development machine is dual install office 10,11 and SP2 Office 11 automagically updates office 10 as well (go figure, thats why office XP clients will still need a manual patch!)
Even though supplied as VS2005 extra, in fact it is an office patch, so no reason you can;t distribute it from VS2003 (may have to do manually though, since the KB908002 download looks for VS2005 I think.
Much better just to advise Office 2003 users to update to SP2 from Microsoft office update, but alas NO alternative to redistrbute prerequisite yet for Office XP users as SP3 + latest extras show no sign of it.
Jeltz
|
|
|
|
|
Hi,
I had the same problem when developing the ExcelDna library that enables Excel add-ins and user-defined functions to be created using managed code.
The final version of .Net 2.0 added a registry entry the forces .Net 1.1 to be loaded into the Excel process, even when the first managed code is a .Net 2.0 assembly. The registry entry seems to suggest this only works for some versions of Excel, but I had problems in all versions I tried. I think they tried to add some backward compatibility for their VSTO or SiteServer stuff at the last minute, but it was a very, very bad idea. And it turns out to be a breaking change that is still (to my knowledge) completely undocumented.
The patch referred to above, or SP2, fixes it, or you can remove the registry entry, or you can create an excel.exe.config file that forces the .Net 2.0 runtime to be loaded. All these options are described in the ExcelDna 'Getting Started' at http://exceldna.typepad.com, together with a pointer to the newsgroup thread which told me all I know about the issue.
I have found deployment of the Automation Add-Ins to be pretty tricky, especially compared to ExcelDna Add-Ins (these work using the .xll glue).
Anyway, I hope this provides a bit more info.
Regards,
Govert
|
|
|
|
|
When i built the project and run excel i can't see the class there
|
|
|
|
|
indeed i have the same problem. it might be a new issue with .net 2.0.
i'm using visual studio 2005 express with .net 2.0.50727 on winxp.
since i'm a newbee i would appreciate any help solving the problem.
PS: while compiling i got the following messages in my output window:
warning MSB3391: ......NAddIn.dll" does not contain any types that can be unregistered for COM Interop.
warning MSB3214: ......NAddIn.dll" does not contain any types that can be registered for COM Interop.
|
|
|
|
|
i have found the problem. it's a simple option in preferences menu of the project.
on solution explorer sidebar right click the project and select properties, then choose 'Application', click the button "Assembly Information..." and check the box where it says "Make assembly COM-Visible".
recompile it and it works just fine.
|
|
|
|
|
Hello
The plugin works perfectly on the local machine, but when I relocate it to others it breaks down. I do register it with regasm, after that I do see it in the excel addin list, but when I am trying to reference it from spreadsheet - the functions are not in the list anymore.
Also, from VBA - I can see the namespace and classes, but when I try to reference them I get 'Automation Error'...
Do you know what I might be missing?
Your assistance is greately appreciated.
Thanks
-igor
---------------------------
Igor R. Manassypov, M.Eng.,
imanassypov@ci.com
Network Architect
CI Investments
Toronto, Canada
---------------------------
|
|
|
|
|
Ohhhh yes I have the same problem .. a pain in the....
No solution yet but my experience is (all win xp pro):
1) I am having this prob even with com visible (never works without it OF COURSE!)
2) It works fine and registers on any remote machine with VS2005 installed.
3) It works fine and registers on a non-development machine (No VS2005) but with office 2003 installed
4) It works fine in Excel XP on another development machine with dual install Office XP and office 2003 (also works Excel 2003 same machine)
5) BUT on a machine with only Office XP, Excel XP will register the addin fine in Tools->Add-ins->Automation, but the functions just do not show up in the function list (so #NAME? shows on formula entry).
All latest versions and latest updates to office XP, VS2005 and .Net Framework 2.0 runtime.
Am currently investigating if it is Office 2003 or VS2005 installed that allowed Excel XP to work on (4) above.
Here I was , excel XP and Excel 2003 project all tested, debugged and ready to go...and no go when it gets to the end user. What a crock.
Jeltz
|
|
|
|
|
It worked perfectly by activating "Make assembly COM-Visible".
Thanks a lot.
CSmood
|
|
|
|
|
OK I found the answer myself. Microsoft have a patch solution posted back in december that works for Office XP, but has not been included in the office xp update.
http://support.microsoft.com/kb/908002/en-us
I think the latest updates to office 2003 include it already so it is only needed for office XP only users using .net framework 2.0 add-ins.
Well it works for me anyway...what a pity clickonce deploy packages pre-requisite as a setup.exe and .msi which i don;t allow to download form my site...which was the whole point of choosing the .deploy/manifest solution check box in the click once options anyway..GO FIGURE.
Jeltz
|
|
|
|
|
I have been working on ExcelDna, an open-source project to integrate .NET into Excel. This gives another approach to creating user-defined functions and macros in .NET, using an integration library (.xll). User code can be in .dlls or text-based script files.
|
|
|
|
|
I would like to read a vector or matrix from excel..
Something like:
//public double GetSum(double[,] v1)
public double GetSum(double[] v1)
{
double val = v[1]+ v[2];
return val;
}
But It doesn't work. I tried also using Range Object. But not able to access the [r,c] the value in the range..
public double NumberOfCells(object Range)
{
Excel.Range r = Range as Excel.Range;
//double a = r[1,1].Value ??
//return a;
return r.Cells.Count;
}
Have you got any ideas?
Regards Germoz
|
|
|
|
|
Hi Germoz -- Eric Carter's write-up, linked in the 'Extended Article' message below, gives an example using a Range object. If you are able to use the .NET 2.0 Beta 2, and have some patience to test new stuff, please contact me at govert@icon.co.za - I might have a better answer ...
|
|
|
|
|
How can I return a struct like;
public struct info
{
public int no;
public string name;
public int _class;
}
and how can I show the results of the function on my worksheet?
I will be too glad with your replies!!!
|
|
|
|
|
a simple description of the function
input, output and what's optional and what's not
any idea?
|
|
|
|
|
I've been trynig to get the same thing for some time.
Here's what I've found (so far):
1) it can't be done
From http://support.microsoft.com/kb/285337/
Each Automation Add-in has its own category in the Excel Function Wizard. The category name is the ProgID for the Add-in; you cannot specify a different category name for Automation Add-in functions. Additionally, there is no way to specify function descriptions, argument descriptions, or help for Automation Add-in functions in the Function Wizard.
2) If it could work somehow, contextual help has to be in Winhelp format (see http://msdn.microsoft.com/library/.../deconcreatinghelpfiletousewithofficesolution.asp[^]).
3) Solution? Make custom help available through the Answer Wizard. This requires help in HTML format, and building an Answer Wizard file. Still not contextual, but at least the help will be there for the user, they just need to launch help on their own to get it (and search for it in the Answer Wizard or with keywords).
4) Another Solution? Use very long, very descriptive argument names (e.g Function Density(TemperatureInDegreeF, String_Water_or_Gasoline)) At least these show up in the Insert Function wizard. One caveat: Optional arguments will appear, but will not have any indication they are optional.
One other odd-ball thing: I have built HTML help using the MS Office style sheet (office.css) so my function help would look just like Excel's functions. While it does when launching the help file by itself, it displays with other fonts, and javascript doesn't work, when launched from the topics list (either in the Answer Wizard panel, or the Index panel in Excel help). Oddly, when jumping to my topics from my topics within Excel help, the topics display correctly.
Matt
Update: I solved the odd-ball thing.
in my HTML topics, I had referenced the stylesheet as HREF="office10.css". Changing that to HREF="ms-its:C:\Program
Files\Microsoft Office\Office10\1033\xlmain10.chm::/html/office10.css" fixed the problem. Also all the Javascript src's should be similarly modified. If you extract the individual help from XLMAIN10.CHM, you will have to modify them as they will have the shorter references.
Matt
|
|
|
|
|
I'm currently trying to rewrite a VB6 add-in with C#. I can get the add-in to work with the code above but I've come across some old code requiring Application.Volatile. How is this implemented in C#?
|
|
|
|
|
Hi,
I have created an automation add-in and it works just fine with normal functions excepts for the paramarray option...
I need a function that receives a unknown number of parameters and the paramarray was just the perfect solution and the only one i thing. The problem is that the automation add-in doesn't respond to the excel function call.
How can i do that?
Function example:
Public Function Add(ByVal ParamArray Nums()) As Object
'----------------------------------------------------------------------
'** Demonstrates a function with a variable number of arguments.
' Can be called with formulas like =Add(1), =Add(1,2,3,4),
' or =Add(A1,A2).
'----------------------------------------------------------------------
Dim Sum As Double, i As Integer
On Error GoTo Handler
For i = 0 To UBound(Nums)
Sum = Sum + CDbl(Nums(i))
Next
Add = "The sum is " & Sum
Exit Function
Handler:
Add = 'Error'
End Function
Luis Simões
WorldVision Consulting
|
|
|
|