Reference to XLA functions
I have developed a "function library" that involves call to a DLL i've written.
The xla is distributed trough a setup routine that also register the DLL.
Based on the XLA libary, I have developed som stadard XLS sheets that uses some of the functions in the XLA.
So far so good...
The XLS -XLA are sold separatly.
When the customer gets the XLS all the function references goes to where I, on my machine have installed the XLA. He then have to use a search-replace to get it to point where he has installed the xla.
My customer are not the most computer experienced people and I get a lot of calls on how to it, It has to be a better way.....
The XLA are installed, so somehow I want all this to go automaticly.
Any Ideas
perove
The xla is distributed trough a setup routine that also register the DLL.
Based on the XLA libary, I have developed som stadard XLS sheets that uses some of the functions in the XLA.
So far so good...
The XLS -XLA are sold separatly.
When the customer gets the XLS all the function references goes to where I, on my machine have installed the XLA. He then have to use a search-replace to get it to point where he has installed the xla.
My customer are not the most computer experienced people and I get a lot of calls on how to it, It has to be a better way.....
The XLA are installed, so somehow I want all this to go automaticly.
Any Ideas
perove
ASKER
mixa,
I'm rejceting you (for now-you will be rewarded) but I'll like to get som more info on the subj.
perove
I'm rejceting you (for now-you will be rewarded) but I'll like to get som more info on the subj.
perove
Do not understand: Do you mean Tools/Add-ins does not allow to register your xla ?
XL97: How to Create an Add-in File in Microsoft Excel 97
http://support.microsoft.com/support/kb/articles/Q156/9/42.ASP
Consider packing all the code into the DLL i.e. skip the xla and use the xls as front end. Should it be necessary that the calls can be inserted easily by your _users_ the best option is to buy the SDK (Software Developer Kit) in order to make DLL's which appear in the function dialog, if wanted with help.
VBA: Programming Resources for Visual Basic for Applications
http://support.microsoft.com/support/kb/articles/Q163/4/35.asp
XL97: How to Create an Add-in File in Microsoft Excel 97
http://support.microsoft.com/support/kb/articles/Q156/9/42.ASP
Consider packing all the code into the DLL i.e. skip the xla and use the xls as front end. Should it be necessary that the calls can be inserted easily by your _users_ the best option is to buy the SDK (Software Developer Kit) in order to make DLL's which appear in the function dialog, if wanted with help.
VBA: Programming Resources for Visual Basic for Applications
http://support.microsoft.com/support/kb/articles/Q163/4/35.asp
Hi perove,
In addition more on the subject :O)
http://www.microsoft.com/OfficeDev/Articles/addins97.htm
HTH
:O)Bruintje
In addition more on the subject :O)
http://www.microsoft.com/OfficeDev/Articles/addins97.htm
HTH
:O)Bruintje
Hi perove
It sounds like what you are after is the "Auto_Add" macro.
all you need to do would be place a procedure in your add-inn called Auto_Add.
sub Auto_Add()
<Your code>
End sub
Then whenever your add-inn is istalled the procedure will run.
Let me know if I'm on the right track
antrat
It sounds like what you are after is the "Auto_Add" macro.
all you need to do would be place a procedure in your add-inn called Auto_Add.
sub Auto_Add()
<Your code>
End sub
Then whenever your add-inn is istalled the procedure will run.
Let me know if I'm on the right track
antrat
ASKER
Think I explain myself badly..sorry,
The xla I've bulid is a "function library" - FUNC_LIB.XLA that collect information from a accounting system. Ex. I have the function INVOICE_SUM(inv_number) that gives back the sum of a certian invoice.
I also sell a sheet: KEY_FIGURES.XLS that is a pre-defined sheet, that uses (some of ) the functions in FUNC_LIB.XLA
The problem is when a user that uses the XLS have the XLA installed in a different place then where the XLA was installed when the XLS was build. The functions are -as you know- stored with the full path ie. c:\mylib\func_lib.xla!invi
This is the problem, I cannot trust my users to manually do a search-replace to fix it, I want the link to automatic be replaced/fixed to where the FUNC_LIB.XLA are installed.
mixa is on the right track, but i'll need som more detalis,; how to find the path to where the xla is on the current installation, how to test that the links are "true"/valid and finally how to change them.
With this info on place I can add som code to the auto_open and do this elegant and in-visible for the user.
cri:
<<Do not understand: Do you mean Tools/Add-ins does not allow to register your xla ? >>
No, the xla is installed and work OK, but if I take a XLS sheet made on a different computer the "pointer" to the XLA may not be valid.
Skip the XLA and just use the DLL directly - to time consuming, but I'll keep it in muind to next version.
Hope my problem is clearer now, any Q -> let me know
perove
The xla I've bulid is a "function library" - FUNC_LIB.XLA that collect information from a accounting system. Ex. I have the function INVOICE_SUM(inv_number) that gives back the sum of a certian invoice.
I also sell a sheet: KEY_FIGURES.XLS that is a pre-defined sheet, that uses (some of ) the functions in FUNC_LIB.XLA
The problem is when a user that uses the XLS have the XLA installed in a different place then where the XLA was installed when the XLS was build. The functions are -as you know- stored with the full path ie. c:\mylib\func_lib.xla!invi
This is the problem, I cannot trust my users to manually do a search-replace to fix it, I want the link to automatic be replaced/fixed to where the FUNC_LIB.XLA are installed.
mixa is on the right track, but i'll need som more detalis,; how to find the path to where the xla is on the current installation, how to test that the links are "true"/valid and finally how to change them.
With this info on place I can add som code to the auto_open and do this elegant and in-visible for the user.
cri:
<<Do not understand: Do you mean Tools/Add-ins does not allow to register your xla ? >>
No, the xla is installed and work OK, but if I take a XLS sheet made on a different computer the "pointer" to the XLA may not be valid.
Skip the XLA and just use the DLL directly - to time consuming, but I'll keep it in muind to next version.
Hope my problem is clearer now, any Q -> let me know
perove
Hi perove
Is this along the right lines
Sub Auto_Add()
ActiveWorkbook.ChangeLink Name:="C:\MyDocuments/BILL
End Sub
Or this:
Maintaining References to add-inn files
To access subroutines and functions in another add-in file. You must establish a reference to the add-in by choosing the references command from the tools menu in the VBE and then locating the add-in file using the browse button. Certain problems arise when you save a VBA application with a reference to a add-inn file-Namely, that Excel hard codes the path of the add-inn file when the reference is established and the file that contains the reference is saved. When you open an Excel file that has saved add-in in reference (eg the working folder). If Excel finds the reference add-inn in either of these locations,Excel successfuly re-establishes the reference. Therefore it is imperative the relevant add-in file must be saved in the same folder as the Excel file that contains the reference-otherwise ,Excel fails to find the add-in file, and the reference is not established on the users machine.
An alternative to establishing references to add-in is to use the Run Method of the Application object to call routines in the add-in. The Run Method will run the requested routine without a reference assumming the file containing the routine is in memory. Using this approach, you could open the add-in file through code from whatever location it is in, then call the Run Method. This Method takes the name of the routine or function to run as a string argument, and then takes any arguments to the routine being called as additional optional string argumets. For example:
Application.Run "MyRoutine", "Argument1", "Argument2"
The drawbacks of this approach are the arguments must be converted to text and that pass by reference arguments cannot return values(since arguments are converted to pass by value automatically).
I hope the above is of some use to you.
antrat
Is this along the right lines
Sub Auto_Add()
ActiveWorkbook.ChangeLink Name:="C:\MyDocuments/BILL
End Sub
Or this:
Maintaining References to add-inn files
To access subroutines and functions in another add-in file. You must establish a reference to the add-in by choosing the references command from the tools menu in the VBE and then locating the add-in file using the browse button. Certain problems arise when you save a VBA application with a reference to a add-inn file-Namely, that Excel hard codes the path of the add-inn file when the reference is established and the file that contains the reference is saved. When you open an Excel file that has saved add-in in reference (eg the working folder). If Excel finds the reference add-inn in either of these locations,Excel successfuly re-establishes the reference. Therefore it is imperative the relevant add-in file must be saved in the same folder as the Excel file that contains the reference-otherwise ,Excel fails to find the add-in file, and the reference is not established on the users machine.
An alternative to establishing references to add-in is to use the Run Method of the Application object to call routines in the add-in. The Run Method will run the requested routine without a reference assumming the file containing the routine is in memory. Using this approach, you could open the add-in file through code from whatever location it is in, then call the Run Method. This Method takes the name of the routine or function to run as a string argument, and then takes any arguments to the routine being called as additional optional string argumets. For example:
Application.Run "MyRoutine", "Argument1", "Argument2"
The drawbacks of this approach are the arguments must be converted to text and that pass by reference arguments cannot return values(since arguments are converted to pass by value automatically).
I hope the above is of some use to you.
antrat
ASKER
antrat,
Thanks for the input. Nothing new for me actually, I've discoverd that the functions run approx 10% faster when the xla is installed locally. So It is imperative for me that is is the matter
<<Therefore it is imperative the relevant add-in file must be saved in the same folder as the Excel file that contains the reference-otherwise ,Excel fails to find the add-in file, and the reference is not established on the users machine.>>
I cannot presuppose that this is the situation the user want to share the XLS , this is the problem in a nutshell. What I had in mind is that on the open of the xls i need to.
1.Check if the link is valid
if no
-find the link to my xla
-replace all the function with the correct link
if yes
open the sheet
Thanks for your input, it is most welcome, I'm more a VB delveloper and deploying VBA and XL is a bit of a new world to me.
regards from perove in norway
Thanks for the input. Nothing new for me actually, I've discoverd that the functions run approx 10% faster when the xla is installed locally. So It is imperative for me that is is the matter
<<Therefore it is imperative the relevant add-in file must be saved in the same folder as the Excel file that contains the reference-otherwise ,Excel fails to find the add-in file, and the reference is not established on the users machine.>>
I cannot presuppose that this is the situation the user want to share the XLS , this is the problem in a nutshell. What I had in mind is that on the open of the xls i need to.
1.Check if the link is valid
if no
-find the link to my xla
-replace all the function with the correct link
if yes
open the sheet
Thanks for your input, it is most welcome, I'm more a VB delveloper and deploying VBA and XL is a bit of a new world to me.
regards from perove in norway
ASKER
antrat,
Think I will solve it with your way if I can get the path to the xla currently installed. So a function.
GetLinkPath(libName as string) as string
'returns full path to the library libName
end function
would do the trick for me
ex
GetLinkPath("solver add-In")
should return
C:\Program Files\Microsoft Office\Office\Library\Solv
Then I could use your excel(ent)example like this:
Sub Auto_Add()
ActiveWorkbook.ChangeLink Name:="C:\MyDocuments/BILL
, Type:=xlExcelLinks
End Sub
Take the point, we are really close here..
perove
Think I will solve it with your way if I can get the path to the xla currently installed. So a function.
GetLinkPath(libName as string) as string
'returns full path to the library libName
end function
would do the trick for me
ex
GetLinkPath("solver add-In")
should return
C:\Program Files\Microsoft Office\Office\Library\Solv
Then I could use your excel(ent)example like this:
Sub Auto_Add()
ActiveWorkbook.ChangeLink Name:="C:\MyDocuments/BILL
, Type:=xlExcelLinks
End Sub
Take the point, we are really close here..
perove
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
mixa..
The KEY_FIGURES.XLS (that uses function from FUNC_LIB.XLA) use them not in VB but they are maked my Choosing the Fx button and then user defined functions wher I can locate my function invioce_sum()
So there are no VBA involved in the XLS just plain "cell referencing to functions"
I can still use your suggestion, please verify
perove
The KEY_FIGURES.XLS (that uses function from FUNC_LIB.XLA) use them not in VB but they are maked my Choosing the Fx button and then user defined functions wher I can locate my function invioce_sum()
So there are no VBA involved in the XLS just plain "cell referencing to functions"
I can still use your suggestion, please verify
perove
Write a VB installation program!
There ain't no easy way, as you can see from the comments your getting.
I find it best to just write your installation and make the things that need to happen.
Argmyster...
There ain't no easy way, as you can see from the comments your getting.
I find it best to just write your installation and make the things that need to happen.
Argmyster...
ActiveWorkbook.ChangeLink Name:="C:\TMP\test.xla", NewName:="C:\NewLocation\t
After installing you may open special xls file that will open the data file without resolving link (see workboos.Open method, updatelinks parameter), then perform that statement for it.
In general, XL has special algorithm for changin links for the standard xla (they may be different on the PCs) but I don't know the exact way. May be someone has the better answer - let me know.
mixa@nbd.kis.ru