Open macro saved on shared network drive

[emoandy05]

Hello all!

I have a workbook that users will be downloading/exporting from an external source. Once downloaded, I have two hotkeys that run two macros. I would like the location of the macro to be saved on the shared drive, so when a user hits the hotkeys, excel will still know to look for the macro on the shared drive. But in the case of the personal file saved under xlstart, I would like that to remain there so if the user created their own macro for something else, they will still have their personal macros on their local drive.

Is there a way to specify the location/address of a particular macro? This way, if there is ever an update made to it, I can just update the macro on the shared drive and will not have to sent out updates versions to every user.

Thank you in advance for your help and please let me know if there is anything I can clarify.

 

[perimidt]

Hi

Can't you just put a shortcut to the file on the shared drive in the xlstart-folder?

 

[emoandy05]

I could do that, but that would still require each user to take that initial step of creating the shortcut. Great idea though. I just want this to be foolproof so the user just has to run the macro using the hotkeys.

 

[perimidt]

Hi

It turns out that creating a shortcut is not all that complicated, take a look at this page:
Creating File Shortcuts (VBA: Office XP) | Windows Secrets Lounge

With the info from that page I made put the two following macros in a workbook.

Sub CreateAShortcut _(FileName As String, _
Target As String, _
Optional WorkingDirectory As String, _
Optional WindowStyle As Integer = vbNormalFocus, _
Optional Description As String)


With CreateObject("WScript.Shell").CreateShortcut(FileName)
    .TargetPath = Target
    .WindowStyle = WindowStyle
    .Description = Description
    .WorkingDirectory = WorkingDirectory
    .Save
End With
End Sub

Sub StartShortCut()    
Dim path As String
path = Application.StartupPath
    
CreateAShortcut path & "\Shortcut.lnk", "D:\Testmappe\Filename.xlsx", , vbMaximizedFocus
End Sub

I suppose you could put the last one in the startup-event of your workbook so that when your users open the file the first time the shortcut is created.
Obviously you need to change "D:\Testmappe\Filename.xlsx" to whatever your path is.

I have uploaded a sample workbook here:
https://dl.dropboxusercontent.com/u/12632/Open macro saved on shared network drive.xlsm

I cannot take credit for any of this code, but hopefully it is of some use.