VBScript to run a file from desktop (any user)

Question

I've created a VBScript to run a macro from excel without opening the file.

Set objExcel = CreateObject("Excel.Application")  
objExcel.Application.Run "'C:\Users\MyUser\Desktop\RM.xlsm'!Module4.Email"  
objExcel.DisplayAlerts = False  
objExcel.Application.Quit  
Set objExcel = Nothing

I want to use this VBScript & XLSM file on different computers, so how can i change this script to work without editing the path every time?
(Maybe a code to run from current folder or a code to run from any user desktop)

Spangen Spangen · Accepted Answer · 2017-11-17T13:23:21

If the file will always be on the desktop of every user then you can use environment variables to determine the location.

Set wshShell = CreateObject( "WScript.Shell" )
userName = wshShell.ExpandEnvironmentStrings( "%UserName%" )

path = "'C:\Users\" + userName + "\Desktop\RM.xlsm'!Module4.Email"
Set objExcel = CreateObject("Excel.Application")  
objExcel.Application.Run path
objExcel.DisplayAlerts = False  
objExcel.Application.Quit  
Set objExcel = Nothing

(Untested code)

If the file is not on each user's desktop then you'll need to store it in a common location on the network and have each user access it from there, e.g. in .

\\YourFileSever\SharedFiles\RM.xlsm

In practical terms the latter is preferable as it means the workbook is in only one place and when it comes to releasing a new version you only have to update one copy

VBScript to run a file from desktop (any user)

1 Answers