Edit Personal.xls via Macro

[richardbooth]

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]

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

[vba]

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
[/vba]

[richardbooth]

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]

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]

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]

I gave you the code to do that. It needs a small tweak though

[vba]

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
[/vba]

[richardbooth]

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]

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]

They would need to trus access as you say.

[richardbooth]

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]

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]

Ah ok - modulefile is the name of the module in vb that they already have in their personal.xls.. thanks for your help

[lucas]

Just think, if you had created an addin, this would be easy.

[richardbooth]

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]

Have a look at my articles on exactly this subject:

www.jkp-ads.com/Articles/DistributeMacro00.asp

On updating an addin automagically:

www.jkp-ads.com/articles/updateanaddin.asp

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

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]

Thank you.

[lucas]

Hey, I was just suggesting a way to make your life easier.

It's never to late to change approaches.