PDA

View Full Version : Edit Personal.xls via Macro



richardbooth
12-09-2009, 02:00 AM
I have saved a simple macro saved in the personal.xls worksheet of several Excel users where I work. I now need to edit that macro and wondered if there was a way of automating this to save me manually going to everyone's PC and typing in the code, i.e. perhaps by sending an excel file to everyone that they open and then click a button which updates their macro? Is that possible?

Thanks

Bob Phillips
12-09-2009, 02:54 AM
The simplest way is to update your personal.xls, export the file, and have a workbook that has an auto-open macro or workbook_open event code that imports that file into the hot's personal .xls.

If you tell them to load both the module file and your update file into the same directory, this code, which goes in ThisWorkbook code module, will import it



Private Sub Workbook_Open()
Dim ModuleFile As String

ModuleFile = ThisWorkbook.Path & "\ModuleName.bas" '<<<< change filename to suit
Workbooks("Personal.xls").VBProject.VBComponents.Import (ModuleFile)
End Sub

richardbooth
12-09-2009, 02:58 AM
Thanks - how do you mean 'export the file'? I'm not very clued up on this kind of thing.

Does the variable ModuleFile hold the name of the module that has the new code i was to send people...?

Bob Phillips
12-09-2009, 03:37 AM
In the VBIDE, any module can be exported as a stand-alone file, it is is File>Export menu option.8

Yes, ModuleFile will be the filename. IN the code I tell you where to chnage it to your particular module name. In the VBIDE you will see the module name, it just won't have .bas appended to it.

richardbooth
12-09-2009, 03:47 AM
Sorry, I worked that out just after I posted my reply. So:

I have a macro/sub called OpenST that everyone has saved in their Personal.xls;

I export that to a module saved as OpenST.bas say;

Create a workbook and embed the code you suggested, send this around and users run it;

What happens then? Ideally I would need it to go into personal.xls and overwrite the code in the OpenST sub with the new stuff.

Thanks for your assistance

Bob Phillips
12-09-2009, 04:36 AM
I gave you the code to do that. It needs a small tweak though



Private Sub Workbook_Open()
Dim ModuleFile As String

ModuleFile = "ModuleName" '<<<< change filename to suit
With Workbooks("Personal.xls").VBProject

.VBComponents.Remove .VBComponents(ModuleFile)
.VBComponents.Import (ThisWorkbook.Path & "\" & ModuleFile & ".bas")
End With
End Sub

richardbooth
12-10-2009, 01:14 AM
Ok, thanks again. I take it i'll have to send them the module file with the workbook containing the code you provided? and they all have to save it in the same place..

Simon Lloyd
12-10-2009, 02:02 AM
Bob, wouldn't the users have to trust access to the Visual Basic Project in their macro security settings or set a reference?

Bob Phillips
12-10-2009, 02:54 AM
They would need to trus access as you say.

richardbooth
12-10-2009, 04:44 AM
So, in summary. I create the workbook with that macro which runs when the workbook is open. Send them the .bas file at the same time, they save it to the location saved as the ModuleFile location and, assuming their security settings are ok - it shoudl update the module in their personal.xls with the new code i sent them in the .bas file?

Bob Phillips
12-10-2009, 04:59 AM
No, they save it to the same place as they save the workbook that you send.

And you need to tell them to ensure that trusted access is granted (Tools>Macro>Security...>Trusted Publishers).

richardbooth
12-10-2009, 05:21 AM
Ah ok - modulefile is the name of the module in vb that they already have in their personal.xls.. thanks for your help

lucas
12-10-2009, 10:45 AM
Just think, if you had created an addin, this would be easy.

richardbooth
12-11-2009, 02:04 AM
Just think, if I knew what that was, I wouldn't have to come on these boards and talk to the likes of you

Jan Karel Pieterse
12-11-2009, 03:07 AM
Have a look at my articles on exactly this subject:

www.jkp-ads.com/Articles/DistributeMacro00.asp (http://www.jkp-ads.com/Articles/DistributeMacro00.asp)[/URL]

On updating an addin automagically:

[URL="http://www.jkp-ads.com/articles/updateanaddin.asp"]www.jkp-ads.com/articles/updateanaddin.asp (http://www.jkp-ads.com/articles/distributemacro.asp)

On how addins are handled in the registry (and some code deidcated to a setup program):

www.jkp-ads.com/Articles/AddinsAndSetupFactory.asp (http://www.jkp-ads.com/Articles/AddinsAndSetupFactory.asp)

On fixing link problems to addins with UDF's:

http://www.jkp-ads.com/Articles/FixLinks2UDF.asp

richardbooth
12-11-2009, 04:07 AM
Thank you.

lucas
12-11-2009, 11:46 AM
Hey, I was just suggesting a way to make your life easier.

It's never to late to change approaches.