View Full Version : Using Share Drive/Point to access Macros
PianoMan5
07-10-2012, 10:25 AM
Excuse my ignorance but I have searched IE and this forum but haven't found an answer. Is there a way save macros I have created to a sharedrive and have any user access that macro?
If I make any updates to the macro, the users will automatically see the update the next time he/she runs it.
CodeNinja
07-10-2012, 11:45 AM
In order to assure if you make changes to a macro they will get the latest version, I think you need to load it every time on load of the application... You can do this with extensibility... here is how:
First, load the reference "Microsoft Visual Basic for Applications Extensibility 5.3 into your references. You can do this in the Visual Basic editor by clicking tools / references and scrolling down to find the reference. Check it, and click ok.
Second, in this workbook, go to workbook_open() sub and enter the following code:
Be sure to change the path and macro name to suit your needs.
'requires 'Microsoft Visual Basic for Applications Extensibility 5.3' reference
Dim vbProj As VBIDE.VBProject
Dim vbcomp As VBIDE.VBComponent
Dim i As Integer
Dim sMacroName As String
Dim sMacroPath As String
Set vbProj = ThisWorkbook.VBProject
sMacroName = "" 'put your macro name here...
sMacroPath = "" 'Put your macro path here...
' check to see it is not already loaded... if it is, delete loaded macro
For i = 1 To vbProj.VBComponents.Count
If vbProj.VBComponents(i).Name = sMacroName Then
ActiveWorkbook.VBProject.VBComponents.Remove ActiveWorkbook.VBProject.VBComponents(sMacroName)
End If
Next i
' Now load the most up to date macro
vbProj.VBComponents.Import (sMacroPath & sMacroName & ".bas")
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.