I needed to access a MS Access database to extract a unit cost that matches
a part number that was in Excel. I created a function, saved it as an XLA,
and used it as an Add-in. I figured that I could copy the XLA file to the
users profile (c:\documents and settings\user\application
data\microsoft\addins), and then when another user opened the file, the
function would work. As it turns out, it looks like when another user opens
the file, it is trying to access my profile for the function. So, I figured
that I would put the XLA file on a shared network folder. Browsed to the
addin (did not copy to local HD), and then resaved the spreadsheet. I
opened the file on a different machine (that has access to the network
folder), but it still wasn't working. Is there a way to get this to work?
Or do I have to save the function in the actual file?

Thanks,
Brian

Hi Brian,

If you want to use a custom function in another workbook you need to
reference the workbook containing that function (in this case your AddIn) in
the workbook that will be using the function. Shame ain't it!

Post back if you don't know how to do this.
HTH,
Gareth

"Brian K. Sheperd" wrote in message
...
I needed to access a MS Access database to extract a unit cost that
matches
a part number that was in Excel. I created a function, saved it as an
XLA,
and used it as an Add-in. I figured that I could copy the XLA file to the
users profile (c:\documents and settings\user\application
data\microsoft\addins), and then when another user opened the file, the
function would work. As it turns out, it looks like when another user
opens
the file, it is trying to access my profile for the function. So, I
figured
that I would put the XLA file on a shared network folder. Browsed to the
addin (did not copy to local HD), and then resaved the spreadsheet. I
opened the file on a different machine (that has access to the network
folder), but it still wasn't working. Is there a way to get this to work?
Or do I have to save the function in the actual file?

Thanks,
Brian

Gareth,
Yes... Please explain how to accomplish this.
Thanks,
Brian


"Gareth Roberts" wrote in message
...
Hi Brian,

If you want to use a custom function in another workbook you need to
reference the workbook containing that function (in this case your AddIn)
in
the workbook that will be using the function. Shame ain't it!

Post back if you don't know how to do this.
HTH,
Gareth

"Brian K. Sheperd" wrote in message
...
I needed to access a MS Access database to extract a unit cost that
matches
a part number that was in Excel. I created a function, saved it as an
XLA,
and used it as an Add-in. I figured that I could copy the XLA file to
the
users profile (c:\documents and settings\user\application
data\microsoft\addins), and then when another user opened the file, the
function would work. As it turns out, it looks like when another user
opens
the file, it is trying to access my profile for the function. So, I
figured
that I would put the XLA file on a shared network folder. Browsed to
the
addin (did not copy to local HD), and then resaved the spreadsheet. I
opened the file on a different machine (that has access to the network
folder), but it still wasn't working. Is there a way to get this to
work?
Or do I have to save the function in the actual file?

Thanks,
Brian

Hi Brian,

Open your AddIn and the workbook that needs to reference it.
Open the VBE (SHIFT F11)
Select Tools \ References from the Menu Bar.
Your AddIn should be listed there (that's why we opened it before -
otherwise you might have to browse for it)
Tick the checkbox for your AddIn and click OK.
Save your workbook (for good measure).
You should now be able to access the function.

Note that if you didn't give your AddIn's VBA project a specific name it
will just be called VBAProject in the References list. You may like to
change it: Select Tools \ VBAProject Properties

Note further that, whilst Excel is smart at working out which project is
referenced even if you mess around with paths and things you can get into
bit of trouble should people have different mapped drives. Even referencing
by browsing through network neighourhood to make sure you get the UNC path
can be problematic if one person saves the referencing workbook on a mapped
drive - it somehow changes the references... but on the wholeit should be
fine.

HTH,
Gareth

----- Original Message -----
From: "Brian K. Sheperd"
Newsgroups: microsoft.public.excel.programming
Sent: Tuesday, March 15, 2005 6:00 PM
Subject: sharing an add-in function for multiple users


Gareth,
Yes... Please explain how to accomplish this.
Thanks,
Brian


"Gareth Roberts" wrote in message
...
Hi Brian,

If you want to use a custom function in another workbook you need to
reference the workbook containing that function (in this case your AddIn)
in
the workbook that will be using the function. Shame ain't it!

Post back if you don't know how to do this.
HTH,
Gareth

"Brian K. Sheperd" wrote in message
...
I needed to access a MS Access database to extract a unit cost that
matches
a part number that was in Excel. I created a function, saved it as an
XLA,
and used it as an Add-in. I figured that I could copy the XLA file to
the
users profile (c:\documents and settings\user\application
data\microsoft\addins), and then when another user opened the file, the
function would work. As it turns out, it looks like when another user
opens
the file, it is trying to access my profile for the function. So, I
figured
that I would put the XLA file on a shared network folder. Browsed to
the
addin (did not copy to local HD), and then resaved the spreadsheet. I
opened the file on a different machine (that has access to the network
folder), but it still wasn't working. Is there a way to get this to
work?
Or do I have to save the function in the actual file?

Thanks,
Brian






"Brian K. Sheperd" wrote in message
...
Gareth,
Yes... Please explain how to accomplish this.
Thanks,
Brian


"Gareth Roberts" wrote in message
...
Hi Brian,

If you want to use a custom function in another workbook you need to
reference the workbook containing that function (in this case your
AddIn)
in
the workbook that will be using the function. Shame ain't it!

Post back if you don't know how to do this.
HTH,
Gareth

"Brian K. Sheperd" wrote in message
...
I needed to access a MS Access database to extract a unit cost that
matches
a part number that was in Excel. I created a function, saved it as an
XLA,
and used it as an Add-in. I figured that I could copy the XLA file to
the
users profile (c:\documents and settings\user\application
data\microsoft\addins), and then when another user opened the file,
the
function would work. As it turns out, it looks like when another user
opens
the file, it is trying to access my profile for the function. So, I
figured
that I would put the XLA file on a shared network folder. Browsed to
the
addin (did not copy to local HD), and then resaved the spreadsheet. I
opened the file on a different machine (that has access to the network
folder), but it still wasn't working. Is there a way to get this to
work?
Or do I have to save the function in the actual file?

Thanks,
Brian

Gareth,

Thank you very much for the info.

Just as a side thought. The file that I am working with is going to be a
template file. We have a thousand or so files that in an old revision. I
am going to write a subroutine to open each excel file, copy out specific
information, and copy it to the template. What if I just wrote a function
in VBA for the template file, then it would be there since all the other
files are going to be based from that template? Or would that be about the
same concept?

Thanks again,
Brian


"Gareth Roberts" wrote in message
...
Hi Brian,

Open your AddIn and the workbook that needs to reference it.
Open the VBE (SHIFT F11)
Select Tools \ References from the Menu Bar.
Your AddIn should be listed there (that's why we opened it before -
otherwise you might have to browse for it)
Tick the checkbox for your AddIn and click OK.
Save your workbook (for good measure).
You should now be able to access the function.

Note that if you didn't give your AddIn's VBA project a specific name it
will just be called VBAProject in the References list. You may like to
change it: Select Tools \ VBAProject Properties

Note further that, whilst Excel is smart at working out which project is
referenced even if you mess around with paths and things you can get into
bit of trouble should people have different mapped drives. Even
referencing
by browsing through network neighourhood to make sure you get the UNC path
can be problematic if one person saves the referencing workbook on a
mapped
drive - it somehow changes the references... but on the wholeit should be
fine.

HTH,
Gareth

----- Original Message -----
From: "Brian K. Sheperd"
Newsgroups: microsoft.public.excel.programming
Sent: Tuesday, March 15, 2005 6:00 PM
Subject: sharing an add-in function for multiple users


Gareth,
Yes... Please explain how to accomplish this.
Thanks,
Brian


"Gareth Roberts" wrote in message
...
Hi Brian,

If you want to use a custom function in another workbook you need to
reference the workbook containing that function (in this case your
AddIn)
in
the workbook that will be using the function. Shame ain't it!

Post back if you don't know how to do this.
HTH,
Gareth

"Brian K. Sheperd" wrote in message
...
I needed to access a MS Access database to extract a unit cost that
matches
a part number that was in Excel. I created a function, saved it as an
XLA,
and used it as an Add-in. I figured that I could copy the XLA file to
the
users profile (c:\documents and settings\user\application
data\microsoft\addins), and then when another user opened the file,
the
function would work. As it turns out, it looks like when another user
opens
the file, it is trying to access my profile for the function. So, I
figured
that I would put the XLA file on a shared network folder. Browsed to
the
addin (did not copy to local HD), and then resaved the spreadsheet. I
opened the file on a different machine (that has access to the network
folder), but it still wasn't working. Is there a way to get this to
work?
Or do I have to save the function in the actual file?

Thanks,
Brian






"Brian K. Sheperd" wrote in message
...
Gareth,
Yes... Please explain how to accomplish this.
Thanks,
Brian


"Gareth Roberts" wrote in message
...
Hi Brian,

If you want to use a custom function in another workbook you need to
reference the workbook containing that function (in this case your
AddIn)
in
the workbook that will be using the function. Shame ain't it!

Post back if you don't know how to do this.
HTH,
Gareth

"Brian K. Sheperd" wrote in message
...
I needed to access a MS Access database to extract a unit cost that
matches
a part number that was in Excel. I created a function, saved it as
an
XLA,
and used it as an Add-in. I figured that I could copy the XLA file
to
the
users profile (c:\documents and settings\user\application
data\microsoft\addins), and then when another user opened the file,
the
function would work. As it turns out, it looks like when another
user
opens
the file, it is trying to access my profile for the function. So, I
figured
that I would put the XLA file on a shared network folder. Browsed
to
the
addin (did not copy to local HD), and then resaved the spreadsheet.
I
opened the file on a different machine (that has access to the
network
folder), but it still wasn't working. Is there a way to get this to
work?
Or do I have to save the function in the actual file?

Thanks,
Brian

Gareth,
I will have to run it by a few people to see which path will be better.
Thank you again for all of your help.
Brian



"Gareth Roberts" wrote in message
...
Hi Brian,

I think that's a much better idea. Keeping the code in the workbook itself
would avoid all the referencing problems, letting you email them etc. Of
course.... if you ever wanted to change or add functions to them in the
future that would be awkward. In that case you would have been better
referencing.... I think it depends on your circumstances, how your
workbooks
are deployed, likelihood of changes etc.

It is possible to programmatically change the VBA code in workbooks so if
you has to change it for all 1000 files you could do it automatically....
but YIKES!

Cya
Gareth

"Brian K. Sheperd" wrote in message
...
Gareth,

Thank you very much for the info.

Just as a side thought. The file that I am working with is going to be
a
template file. We have a thousand or so files that in an old revision.
I
am going to write a subroutine to open each excel file, copy out
specific
information, and copy it to the template. What if I just wrote a
function
in VBA for the template file, then it would be there since all the other
files are going to be based from that template? Or would that be about
the
same concept?

Thanks again,
Brian


"Gareth Roberts" wrote in message
...
Hi Brian,

Open your AddIn and the workbook that needs to reference it.
Open the VBE (SHIFT F11)
Select Tools \ References from the Menu Bar.
Your AddIn should be listed there (that's why we opened it before -
otherwise you might have to browse for it)
Tick the checkbox for your AddIn and click OK.
Save your workbook (for good measure).
You should now be able to access the function.

Note that if you didn't give your AddIn's VBA project a specific name
it
will just be called VBAProject in the References list. You may like to
change it: Select Tools \ VBAProject Properties

Note further that, whilst Excel is smart at working out which project
is
referenced even if you mess around with paths and things you can get
into
bit of trouble should people have different mapped drives. Even
referencing
by browsing through network neighourhood to make sure you get the UNC
path
can be problematic if one person saves the referencing workbook on a
mapped
drive - it somehow changes the references... but on the wholeit should
be
fine.

HTH,
Gareth

----- Original Message -----
From: "Brian K. Sheperd"
Newsgroups: microsoft.public.excel.programming
Sent: Tuesday, March 15, 2005 6:00 PM
Subject: sharing an add-in function for multiple users


Gareth,
Yes... Please explain how to accomplish this.
Thanks,
Brian


"Gareth Roberts" wrote in message
...
Hi Brian,

If you want to use a custom function in another workbook you need to
reference the workbook containing that function (in this case your
AddIn)
in
the workbook that will be using the function. Shame ain't it!

Post back if you don't know how to do this.
HTH,
Gareth

"Brian K. Sheperd" wrote in
message
...
I needed to access a MS Access database to extract a unit cost
that
matches
a part number that was in Excel. I created a function, saved it
as
an
XLA,
and used it as an Add-in. I figured that I could copy the XLA
file
to
the
users profile (c:\documents and settings\user\application
data\microsoft\addins), and then when another user opened the
file,
the
function would work. As it turns out, it looks like when another
user
opens
the file, it is trying to access my profile for the function. So,
I
figured
that I would put the XLA file on a shared network folder. Browsed
to
the
addin (did not copy to local HD), and then resaved the
spreadsheet.
I
opened the file on a different machine (that has access to the
network
folder), but it still wasn't working. Is there a way to get this
to
work?
Or do I have to save the function in the actual file?

Thanks,
Brian






"Brian K. Sheperd" wrote in message
...
Gareth,
Yes... Please explain how to accomplish this.
Thanks,
Brian


"Gareth Roberts" wrote in message
...
Hi Brian,

If you want to use a custom function in another workbook you need
to
reference the workbook containing that function (in this case your
AddIn)
in
the workbook that will be using the function. Shame ain't it!

Post back if you don't know how to do this.
HTH,
Gareth

"Brian K. Sheperd" wrote in
message
...
I needed to access a MS Access database to extract a unit cost
that
matches
a part number that was in Excel. I created a function, saved it
as
an
XLA,
and used it as an Add-in. I figured that I could copy the XLA
file
to
the
users profile (c:\documents and settings\user\application
data\microsoft\addins), and then when another user opened the
file,
the
function would work. As it turns out, it looks like when
another
user
opens
the file, it is trying to access my profile for the function.
So,
I
figured
that I would put the XLA file on a shared network folder.
Browsed
to
the
addin (did not copy to local HD), and then resaved the
spreadsheet.
I
opened the file on a different machine (that has access to the
network
folder), but it still wasn't working. Is there a way to get
this
to
work?
Or do I have to save the function in the actual file?

Thanks,
Brian

Hi Brian,

I think that's a much better idea. Keeping the code in the workbook itself
would avoid all the referencing problems, letting you email them etc. Of
course.... if you ever wanted to change or add functions to them in the
future that would be awkward. In that case you would have been better
referencing.... I think it depends on your circumstances, how your workbooks
are deployed, likelihood of changes etc.

It is possible to programmatically change the VBA code in workbooks so if
you has to change it for all 1000 files you could do it automatically....
but YIKES!

Cya
Gareth

"Brian K. Sheperd" wrote in message
...
Gareth,

Thank you very much for the info.

Just as a side thought. The file that I am working with is going to be a
template file. We have a thousand or so files that in an old revision. I
am going to write a subroutine to open each excel file, copy out specific
information, and copy it to the template. What if I just wrote a function
in VBA for the template file, then it would be there since all the other
files are going to be based from that template? Or would that be about
the
same concept?

Thanks again,
Brian


"Gareth Roberts" wrote in message
...
Hi Brian,

Open your AddIn and the workbook that needs to reference it.
Open the VBE (SHIFT F11)
Select Tools \ References from the Menu Bar.
Your AddIn should be listed there (that's why we opened it before -
otherwise you might have to browse for it)
Tick the checkbox for your AddIn and click OK.
Save your workbook (for good measure).
You should now be able to access the function.

Note that if you didn't give your AddIn's VBA project a specific name it
will just be called VBAProject in the References list. You may like to
change it: Select Tools \ VBAProject Properties

Note further that, whilst Excel is smart at working out which project is
referenced even if you mess around with paths and things you can get
into
bit of trouble should people have different mapped drives. Even
referencing
by browsing through network neighourhood to make sure you get the UNC
path
can be problematic if one person saves the referencing workbook on a
mapped
drive - it somehow changes the references... but on the wholeit should
be
fine.

HTH,
Gareth

----- Original Message -----
From: "Brian K. Sheperd"
Newsgroups: microsoft.public.excel.programming
Sent: Tuesday, March 15, 2005 6:00 PM
Subject: sharing an add-in function for multiple users


Gareth,
Yes... Please explain how to accomplish this.
Thanks,
Brian


"Gareth Roberts" wrote in message
...
Hi Brian,

If you want to use a custom function in another workbook you need to
reference the workbook containing that function (in this case your
AddIn)
in
the workbook that will be using the function. Shame ain't it!

Post back if you don't know how to do this.
HTH,
Gareth

"Brian K. Sheperd" wrote in message
...
I needed to access a MS Access database to extract a unit cost that
matches
a part number that was in Excel. I created a function, saved it as
an
XLA,
and used it as an Add-in. I figured that I could copy the XLA file
to
the
users profile (c:\documents and settings\user\application
data\microsoft\addins), and then when another user opened the file,
the
function would work. As it turns out, it looks like when another
user
opens
the file, it is trying to access my profile for the function. So, I
figured
that I would put the XLA file on a shared network folder. Browsed
to
the
addin (did not copy to local HD), and then resaved the spreadsheet.
I
opened the file on a different machine (that has access to the
network
folder), but it still wasn't working. Is there a way to get this to
work?
Or do I have to save the function in the actual file?

Thanks,
Brian






"Brian K. Sheperd" wrote in message
...
Gareth,
Yes... Please explain how to accomplish this.
Thanks,
Brian


"Gareth Roberts" wrote in message
...
Hi Brian,

If you want to use a custom function in another workbook you need to
reference the workbook containing that function (in this case your
AddIn)
in
the workbook that will be using the function. Shame ain't it!

Post back if you don't know how to do this.
HTH,
Gareth

"Brian K. Sheperd" wrote in
message
...
I needed to access a MS Access database to extract a unit cost
that
matches
a part number that was in Excel. I created a function, saved it
as
an
XLA,
and used it as an Add-in. I figured that I could copy the XLA
file
to
the
users profile (c:\documents and settings\user\application
data\microsoft\addins), and then when another user opened the
file,
the
function would work. As it turns out, it looks like when another
user
opens
the file, it is trying to access my profile for the function. So,
I
figured
that I would put the XLA file on a shared network folder. Browsed
to
the
addin (did not copy to local HD), and then resaved the
spreadsheet.
I
opened the file on a different machine (that has access to the
network
folder), but it still wasn't working. Is there a way to get this
to
work?
Or do I have to save the function in the actual file?

Thanks,
Brian