Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Porting a macro to 500 users
I have written a macro that I need to give to 500+ other end users in my
company. The code would have to go in their Personal.xls workbook so they could run it for any Excel file they open. What's the best way to do this? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Porting a macro to 500 users
It doesn't have to go in their personal.xls workbook.
In fact, I wouldn't want you touching my file. After I make changes to my development version and update the real one, your macros will be gone. In fact, I protect my personal.xl* file (marked readonly) and I don't allow programs to access the project, either. Instead... I'd create a new workbook with just the code in it. Save it as BishopUtils.xls (or as an addin *.xla). Store it on a common network drive and tell the users to open the workbook when they need to have access to your macros. If the common network drive isn't available, then I'd tell the users to create a dedicated folder on their local drive: C:\ExcelUtilities and put the file there. This will make sharing workbooks that use user defined functions (in worksheet cells) easier to share between your users. (You may have problems with links pointing to the wrong folder if people put the file in a location that doesn't share that common name.) Personally, I'd save the file as an addin (*.xla or *.xlam). But then I'd have to give the user a way to run the macros. Saved from a previous post: For additions to the worksheet menu bar, I really like the way John Walkenbach does it in his menumaker workbook: http://j-walk.com/ss/excel/tips/tip53.htm Here's how I do it when I want a toolbar: http://www.contextures.com/xlToolbar02.html (from Debra Dalgleish's site) In xl2007, those toolbars and menu modifications will show up under the addins. And if you use xl2007: If you want to learn about modifying the ribbon, you can start at Ron de Bruin's site: http://www.rondebruin.nl/ribbon.htm http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an addin) or http://www.rondebruin.nl/2007addin.htm And Bob Phillips shows a way to use a wrapper so that it can work in both xl2003 and xl2007. http://msmvps.com/blogs/xldynamic/ar...me-simple.aspx Bishop wrote: I have written a macro that I need to give to 500+ other end users in my company. The code would have to go in their Personal.xls workbook so they could run it for any Excel file they open. What's the best way to do this? -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Porting a macro to 500 users
If it were me, I'd probably distribute it as an add-in. Can your IT group
distribute it to all of them with LanDesk or something similar? I've only distributed a couple of add-ins to individuals, not a group, so I'm not sure I'm the best to answer this. I'm sure others will weigh in on your question. -- HTH, Barb Reinhardt "Bishop" wrote: I have written a macro that I need to give to 500+ other end users in my company. The code would have to go in their Personal.xls workbook so they could run it for any Excel file they open. What's the best way to do this? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Porting a macro to 500 users
Dave
Thank you for your response. Really good information in here. I tried your suggested approach about storing the macro in a workbook and making the workbook available to users when they want to run the macro. However, the Big Boss said I had to "accommodate the lowest common denominator." Which is to say that some of our end users are not exactly Excel proficient. He felt that asking them to open a second workbook would be asking too much. So now what I have to do is make the following happen: //these first 4 instructions the end user can do. I'm Providing the steps leading up //to my task to make it easier to understand. An end user goes to XYZ website. Opens a file. Clicks "Export file to Excel." An Excel .xls workbook opens with the information. //Here's where I come in When the Excel workbook opens a toolbar must be added When the toolbar is added it must have a button When the button is pushed it must execute a macro The macro will format the spreadsheet to meet specifications When the workbook is closed the toolbar is unloaded So I have written the code to load the toolbar, button and the macro to format. It is in a workbook on my desktop right now. The reason I mentioned adding it to all the end users Personal.xls file is because that's the only way I know to accommodate a request like this. Big Boss is not budging so I have to make this work. I'm sure I'll have to write up a very detailed set of instructions for the end users to follow to add this macro to their Personal.xls file but I wanted to make sure I'm covering all my bases first. Do you have a better suggestion to implement what's being requested? "Dave Peterson" wrote: It doesn't have to go in their personal.xls workbook. In fact, I wouldn't want you touching my file. After I make changes to my development version and update the real one, your macros will be gone. In fact, I protect my personal.xl* file (marked readonly) and I don't allow programs to access the project, either. Instead... I'd create a new workbook with just the code in it. Save it as BishopUtils.xls (or as an addin *.xla). Store it on a common network drive and tell the users to open the workbook when they need to have access to your macros. If the common network drive isn't available, then I'd tell the users to create a dedicated folder on their local drive: C:\ExcelUtilities and put the file there. This will make sharing workbooks that use user defined functions (in worksheet cells) easier to share between your users. (You may have problems with links pointing to the wrong folder if people put the file in a location that doesn't share that common name.) Personally, I'd save the file as an addin (*.xla or *.xlam). But then I'd have to give the user a way to run the macros. Saved from a previous post: For additions to the worksheet menu bar, I really like the way John Walkenbach does it in his menumaker workbook: http://j-walk.com/ss/excel/tips/tip53.htm Here's how I do it when I want a toolbar: http://www.contextures.com/xlToolbar02.html (from Debra Dalgleish's site) In xl2007, those toolbars and menu modifications will show up under the addins. And if you use xl2007: If you want to learn about modifying the ribbon, you can start at Ron de Bruin's site: http://www.rondebruin.nl/ribbon.htm http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an addin) or http://www.rondebruin.nl/2007addin.htm And Bob Phillips shows a way to use a wrapper so that it can work in both xl2003 and xl2007. http://msmvps.com/blogs/xldynamic/ar...me-simple.aspx Bishop wrote: I have written a macro that I need to give to 500+ other end users in my company. The code would have to go in their Personal.xls workbook so they could run it for any Excel file they open. What's the best way to do this? -- Dave Peterson . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Porting a macro to 500 users
And I am using 2007 btw.
"Dave Peterson" wrote: It doesn't have to go in their personal.xls workbook. In fact, I wouldn't want you touching my file. After I make changes to my development version and update the real one, your macros will be gone. In fact, I protect my personal.xl* file (marked readonly) and I don't allow programs to access the project, either. Instead... I'd create a new workbook with just the code in it. Save it as BishopUtils.xls (or as an addin *.xla). Store it on a common network drive and tell the users to open the workbook when they need to have access to your macros. If the common network drive isn't available, then I'd tell the users to create a dedicated folder on their local drive: C:\ExcelUtilities and put the file there. This will make sharing workbooks that use user defined functions (in worksheet cells) easier to share between your users. (You may have problems with links pointing to the wrong folder if people put the file in a location that doesn't share that common name.) Personally, I'd save the file as an addin (*.xla or *.xlam). But then I'd have to give the user a way to run the macros. Saved from a previous post: For additions to the worksheet menu bar, I really like the way John Walkenbach does it in his menumaker workbook: http://j-walk.com/ss/excel/tips/tip53.htm Here's how I do it when I want a toolbar: http://www.contextures.com/xlToolbar02.html (from Debra Dalgleish's site) In xl2007, those toolbars and menu modifications will show up under the addins. And if you use xl2007: If you want to learn about modifying the ribbon, you can start at Ron de Bruin's site: http://www.rondebruin.nl/ribbon.htm http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an addin) or http://www.rondebruin.nl/2007addin.htm And Bob Phillips shows a way to use a wrapper so that it can work in both xl2003 and xl2007. http://msmvps.com/blogs/xldynamic/ar...me-simple.aspx Bishop wrote: I have written a macro that I need to give to 500+ other end users in my company. The code would have to go in their Personal.xls workbook so they could run it for any Excel file they open. What's the best way to do this? -- Dave Peterson . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Porting a macro to 500 users
It still applies.
Bishop wrote: And I am using 2007 btw. "Dave Peterson" wrote: It doesn't have to go in their personal.xls workbook. In fact, I wouldn't want you touching my file. After I make changes to my development version and update the real one, your macros will be gone. In fact, I protect my personal.xl* file (marked readonly) and I don't allow programs to access the project, either. Instead... I'd create a new workbook with just the code in it. Save it as BishopUtils.xls (or as an addin *.xla). Store it on a common network drive and tell the users to open the workbook when they need to have access to your macros. If the common network drive isn't available, then I'd tell the users to create a dedicated folder on their local drive: C:\ExcelUtilities and put the file there. This will make sharing workbooks that use user defined functions (in worksheet cells) easier to share between your users. (You may have problems with links pointing to the wrong folder if people put the file in a location that doesn't share that common name.) Personally, I'd save the file as an addin (*.xla or *.xlam). But then I'd have to give the user a way to run the macros. Saved from a previous post: For additions to the worksheet menu bar, I really like the way John Walkenbach does it in his menumaker workbook: http://j-walk.com/ss/excel/tips/tip53.htm Here's how I do it when I want a toolbar: http://www.contextures.com/xlToolbar02.html (from Debra Dalgleish's site) In xl2007, those toolbars and menu modifications will show up under the addins. And if you use xl2007: If you want to learn about modifying the ribbon, you can start at Ron de Bruin's site: http://www.rondebruin.nl/ribbon.htm http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an addin) or http://www.rondebruin.nl/2007addin.htm And Bob Phillips shows a way to use a wrapper so that it can work in both xl2003 and xl2007. http://msmvps.com/blogs/xldynamic/ar...me-simple.aspx Bishop wrote: I have written a macro that I need to give to 500+ other end users in my company. The code would have to go in their Personal.xls workbook so they could run it for any Excel file they open. What's the best way to do this? -- Dave Peterson . -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Porting Excel 2003 | Setting up and Configuration of Excel | |||
Porting Modules | Excel Programming | |||
VBA Script Porting | Excel Programming | |||
Porting VBA Libraries | Excel Programming | |||
Porting 16-bit to 32-bit | Excel Programming |