Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Computing > Computer Information > Making Macros available to other users?

Reply
Thread Tools

Making Macros available to other users?

 
 
John
Guest
Posts: n/a
 
      03-06-2004
Hello.

I just have a few questions about sharing my macros with other users.

I have two macros in Excel that are saved in the personal macro
workbook. I have also put logos on my tool bar that I drew myself
that run the macros.

I just wondered, if I want other people to be able to use these macros
can I put the personal macro workbook on a drive accessible to all
users? I think maybe if it is possible to save it as an external
Microsoft Excel Add-in .xla it would be a good idea? Do you know of a
way to export macros so they become external add-ins for Excel?

It would be good if I could just instal an external add-in on all
these machines which would bring in the logo's I designed for the
macros as well. It would save a lot of messing around.

Anyway if you know how to do any of this I'd love to hear from you.

Thanks very much for any help you can offer.

John


 
Reply With Quote
 
 
 
 
Bob Phillips
Guest
Posts: n/a
 
      03-06-2004
John,

An addin is just an Excel workbook, but saved as type xla not xls. So put
your macros in a new workbook, and just save it as an xls (bottom option on
file types in the save dialog).

Then go and install it in Tools>Addins by clicking Browse, selecting the
file, and then checking the new entry.

The main thing to take care of is to ensure that you always specifically
refer to a workbook/worksheet, as normally non-referenced code works on the
active workbook/activesheet, but they will not be visible to addin code (an
addin has an activeworkbook and an activesheet, you just cannot see them).

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"John" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hello.
>
> I just have a few questions about sharing my macros with other users.
>
> I have two macros in Excel that are saved in the personal macro
> workbook. I have also put logos on my tool bar that I drew myself
> that run the macros.
>
> I just wondered, if I want other people to be able to use these macros
> can I put the personal macro workbook on a drive accessible to all
> users? I think maybe if it is possible to save it as an external
> Microsoft Excel Add-in .xla it would be a good idea? Do you know of a
> way to export macros so they become external add-ins for Excel?
>
> It would be good if I could just instal an external add-in on all
> these machines which would bring in the logo's I designed for the
> macros as well. It would save a lot of messing around.
>
> Anyway if you know how to do any of this I'd love to hear from you.
>
> Thanks very much for any help you can offer.
>
> John
>
>



 
Reply With Quote
 
 
 
 
John
Guest
Posts: n/a
 
      03-06-2004
On Sat, 6 Mar 2004 22:52:55 -0000, "Bob Phillips"
<(E-Mail Removed)> wrote:

>John,
>
>An addin is just an Excel workbook, but saved as type xla not xls. So put
>your macros in a new workbook, and just save it as an xls (bottom option on
>file types in the save dialog).
>
>Then go and install it in Tools>Addins by clicking Browse, selecting the
>file, and then checking the new entry.
>
>The main thing to take care of is to ensure that you always specifically
>refer to a workbook/worksheet, as normally non-referenced code works on the
>active workbook/activesheet, but they will not be visible to addin code (an
>addin has an activeworkbook and an activesheet, you just cannot see them).
>
>--
>
>HTH
>
>Bob Phillips
> ... looking out across Poole Harbour to the Purbecks
>(remove nothere from the email address if mailing direct)


Cheers Bob, this helps a lot.

As far as the referencing of the code goes...

If I have a macro to replace certain data within a spreadsheet with
alternate data, if it is possible for it to cross-reference within
another spreadsheet instead of being within the macro itself, how to I
go about it?

For example I might have the following macro:

Sub MacroA()
'
' Macro A
' Macro recorded 10/12/03
'
' Keyborad Shortcut Ctrl+k
'
Columns(1).Replace What:="1111", Replacement:="AAAA",
LookAt;=xlWhole,_ SearchOrder:=xlByRows, MatchCase:=False
Columns(1).Replace What:="2222", Replacement:="BBBB",
LookAt;=xlWhole,_ SearchOrder:=xlByRows, MatchCase:=False
Columns(1).Replace What:="3333", Replacement:="CCCC",
LookAt;=xlWhole,_ SearchOrder:=xlByRows, MatchCase:=False

End Sub

This macro replaces 1111 with aaaa etc etc.

If I put the replacements within a spreadsheet instead of the macro,
and for example I enter the 1111 in cell 1 of column a, and the aaaa
in cell 1 of column b, and so on.

It would be more organised this way, and it would enable me to just
update the list within the spreadsheet itself, instead of having to go
to the macro each time to edit. And I would also be able to put the
spreadsheet on a drive accessible to all users, so it would be the
same location.

If it's possible to do this, I just wondered what the formula would
then become, say for example the spreadsheet with the replacement list
was located on drive F and called replacements.xls, and it was within
Sheet1?

Columns(1).Replace What:="1111", Replacement:="AAAA",
LookAt;=xlWhole,_ SearchOrder:=xlByRows, MatchCase:=False

What would the 1111 and the aaaa above change to? Would it be
something along the lines of:

Columns(1).Replace What:="F:/replacements.xls'Sheet1'!A1",
Replacement:="F:/replacements.xls'Sheet1'!B1", LookAt;=xlWhole,_
SearchOrder:=xlByRows, MatchCase:=False

Thanks very much for any info on this. I always get confused with the
exact way to enter the file location plus the sheet and cell
reference.

John


 
Reply With Quote
 
Bob Phillips
Guest
Posts: n/a
 
      03-07-2004
John,

You would still need to open the file with the replacement criteria, you
cannot access a closed workbook (well actually you can, but let's not
confuse this with that complexity).

So you would have some code along the lines of

Dim oWbTarget As Workbook
Dim oWbCriteria As Workbook
Dim sReplace As String
Dim sTo As String

Set oWbTarget = Workbooks.Open
Filename:="C:\SomeDir\SomeSUbDir\SomeFile.xls"
'this sets a pointer to the workbook to be worked upon
Set oWbCriteria = Workbooks.Open Filename:="F:\myDrive\replacements.xls"
'you now have a pointer to this workbook that can be used throughout the
code
sReplace = oWBCriteria.Worksheets("Sheet1").Range("A1")
sTo = oWBCriteria.Worksheets("Sheet1").Range("B1")

'now replace it
With oWbTarget
.Columns(1).Replace What:=sReplace, _
Replacement:=sTo, _
LookAt;=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False
End With

Note that my point about the activesheet on an addin is covered here. In
your code, Columns assumed the activesheet, whereas this code always points
explicitly at a sheet and workbook.

Get the idea?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"John" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Sat, 6 Mar 2004 22:52:55 -0000, "Bob Phillips"
> <(E-Mail Removed)> wrote:
>
> Cheers Bob, this helps a lot.
>
> As far as the referencing of the code goes...
>
> If I have a macro to replace certain data within a spreadsheet with
> alternate data, if it is possible for it to cross-reference within
> another spreadsheet instead of being within the macro itself, how to I
> go about it?
>
> For example I might have the following macro:
>
> Sub MacroA()
> '
> ' Macro A
> ' Macro recorded 10/12/03
> '
> ' Keyborad Shortcut Ctrl+k
> '
> Columns(1).Replace What:="1111", Replacement:="AAAA",
> LookAt;=xlWhole,_ SearchOrder:=xlByRows, MatchCase:=False
> Columns(1).Replace What:="2222", Replacement:="BBBB",
> LookAt;=xlWhole,_ SearchOrder:=xlByRows, MatchCase:=False
> Columns(1).Replace What:="3333", Replacement:="CCCC",
> LookAt;=xlWhole,_ SearchOrder:=xlByRows, MatchCase:=False
>
> End Sub
>
> This macro replaces 1111 with aaaa etc etc.
>
> If I put the replacements within a spreadsheet instead of the macro,
> and for example I enter the 1111 in cell 1 of column a, and the aaaa
> in cell 1 of column b, and so on.
>
> It would be more organised this way, and it would enable me to just
> update the list within the spreadsheet itself, instead of having to go
> to the macro each time to edit. And I would also be able to put the
> spreadsheet on a drive accessible to all users, so it would be the
> same location.
>
> If it's possible to do this, I just wondered what the formula would
> then become, say for example the spreadsheet with the replacement list
> was located on drive F and called replacements.xls, and it was within
> Sheet1?
>
> Columns(1).Replace What:="1111", Replacement:="AAAA",
> LookAt;=xlWhole,_ SearchOrder:=xlByRows, MatchCase:=False
>
> What would the 1111 and the aaaa above change to? Would it be
> something along the lines of:
>
> Columns(1).Replace What:="F:/replacements.xls'Sheet1'!A1",
> Replacement:="F:/replacements.xls'Sheet1'!B1", LookAt;=xlWhole,_
> SearchOrder:=xlByRows, MatchCase:=False
>
> Thanks very much for any info on this. I always get confused with the
> exact way to enter the file location plus the sheet and cell
> reference.
>
> John
>
>



 
Reply With Quote
 
John
Guest
Posts: n/a
 
      03-07-2004
On Sun, 7 Mar 2004 00:01:23 -0000, "Bob Phillips"
<(E-Mail Removed)> wrote:

>John,
>
>You would still need to open the file with the replacement criteria, you
>cannot access a closed workbook (well actually you can, but let's not
>confuse this with that complexity).
>
>So you would have some code along the lines of
>
>Dim oWbTarget As Workbook
>Dim oWbCriteria As Workbook
>Dim sReplace As String
>Dim sTo As String
>
> Set oWbTarget = Workbooks.Open
>Filename:="C:\SomeDir\SomeSUbDir\SomeFile.xls"
> 'this sets a pointer to the workbook to be worked upon
> Set oWbCriteria = Workbooks.Open Filename:="F:\myDrive\replacements.xls"
> 'you now have a pointer to this workbook that can be used throughout the
>code
> sReplace = oWBCriteria.Worksheets("Sheet1").Range("A1")
> sTo = oWBCriteria.Worksheets("Sheet1").Range("B1")
>
> 'now replace it
> With oWbTarget
> .Columns(1).Replace What:=sReplace, _
> Replacement:=sTo, _
> LookAt;=xlWhole, _
> SearchOrder:=xlByRows, _
> MatchCase:=False
> End With
>
>Note that my point about the activesheet on an addin is covered here. In
>your code, Columns assumed the activesheet, whereas this code always points
>explicitly at a sheet and workbook.
>
>Get the idea?



Yep. I think it's a bit complex but I just about get it.

I just have one final question as far as the opening of the workbook
goes. Would it cause any problems if lots of different users are
trying to open the same workbook, or would it just open as read-only
anyway and still be able to work?

Thanks for your help

John


 
Reply With Quote
 
Bob Phillips
Guest
Posts: n/a
 
      03-07-2004
John,

I assume that you are referring to the workbook with replacement criteria? I
don't think that this would be a problem, it would just open as read only,
which would be fine as I assume you would only want certain user to update
that file. In that respect it might be best to restrict the update
permissions on the file, and/or make it read-only.
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"John" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Sun, 7 Mar 2004 00:01:23 -0000, "Bob Phillips"
> <(E-Mail Removed)> wrote:
>
> >John,
> >
> >You would still need to open the file with the replacement criteria, you
> >cannot access a closed workbook (well actually you can, but let's not
> >confuse this with that complexity).
> >
> >So you would have some code along the lines of
> >
> >Dim oWbTarget As Workbook
> >Dim oWbCriteria As Workbook
> >Dim sReplace As String
> >Dim sTo As String
> >
> > Set oWbTarget = Workbooks.Open
> >Filename:="C:\SomeDir\SomeSUbDir\SomeFile.xls"
> > 'this sets a pointer to the workbook to be worked upon
> > Set oWbCriteria = Workbooks.Open

Filename:="F:\myDrive\replacements.xls"
> > 'you now have a pointer to this workbook that can be used throughout

the
> >code
> > sReplace = oWBCriteria.Worksheets("Sheet1").Range("A1")
> > sTo = oWBCriteria.Worksheets("Sheet1").Range("B1")
> >
> > 'now replace it
> > With oWbTarget
> > .Columns(1).Replace What:=sReplace, _
> > Replacement:=sTo, _
> > LookAt;=xlWhole, _
> > SearchOrder:=xlByRows, _
> > MatchCase:=False
> > End With
> >
> >Note that my point about the activesheet on an addin is covered here. In
> >your code, Columns assumed the activesheet, whereas this code always

points
> >explicitly at a sheet and workbook.
> >
> >Get the idea?

>
>
> Yep. I think it's a bit complex but I just about get it.
>
> I just have one final question as far as the opening of the workbook
> goes. Would it cause any problems if lots of different users are
> trying to open the same workbook, or would it just open as read-only
> anyway and still be able to work?
>
> Thanks for your help
>
> John
>
>



 
Reply With Quote
 
John
Guest
Posts: n/a
 
      03-07-2004
On Sun, 7 Mar 2004 14:33:29 -0000, "Bob Phillips"
<(E-Mail Removed)> wrote:

>John,
>
>I assume that you are referring to the workbook with replacement criteria? I
>don't think that this would be a problem, it would just open as read only,
>which would be fine as I assume you would only want certain user to update
>that file. In that respect it might be best to restrict the update
>permissions on the file, and/or make it read-only.


Thanks very much for your help

John


 
Reply With Quote
 
Jordan
Guest
Posts: n/a
 
      04-13-2004
I guess John I dont understand your question. When you save the document
with macro's in Vba dosent it automatically save the macros along with it?

-Jordan

"John" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hello.
>
> I just have a few questions about sharing my macros with other users.
>
> I have two macros in Excel that are saved in the personal macro
> workbook. I have also put logos on my tool bar that I drew myself
> that run the macros.
>
> I just wondered, if I want other people to be able to use these macros
> can I put the personal macro workbook on a drive accessible to all
> users? I think maybe if it is possible to save it as an external
> Microsoft Excel Add-in .xla it would be a good idea? Do you know of a
> way to export macros so they become external add-ins for Excel?
>
> It would be good if I could just instal an external add-in on all
> these machines which would bring in the logo's I designed for the
> macros as well. It would save a lot of messing around.
>
> Anyway if you know how to do any of this I'd love to hear from you.
>
> Thanks very much for any help you can offer.
>
> John
>
>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
macros-loop? calling macros X times? Andrew Arro C Programming 2 07-24-2004 09:52 AM
Explanation of macros; Haskell macros mike420@ziplip.com Python 80 11-07-2003 02:22 AM
Re: Explanation of macros; Haskell macros Michael T. Babcock Python 0 11-03-2003 01:54 PM
Re: Explanation of macros; Haskell macros mike420@ziplip.com Python 5 11-01-2003 01:09 AM
Re: Explanation of macros; Haskell macros mike420@ziplip.com Python 1 10-07-2003 04:07 PM



Advertisments