CODEWITHSUNDEEP
vbscriptpath
George OST
George OST

Reputation: 51

VBScript to run a file from desktop (any user)

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)

Upvotes: 2

Views: 1048

Answers (1)

Spangen
Spangen

Reputation: 4720

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

Upvotes: 5

Related Questions