MS Office VBscript to open excel file and run macro from PERSONAL.xlsb

Hauzz

New Member
Joined
Nov 6, 2018
Messages
6
Likes
0
#1
When you want to open an excel file from a certain location and run automatically a macro in PERSONAL.xlsb then this code might help you.

Code:
Const xlDelimited = 1
Set wShell=CreateObject("WScript.Shell")
Set oExec=wShell.Exec("mshta.exe ""about:<input type=file id=FILE><script>FILE.click();new ActiveXObject('Scripting.FileSystemObject').GetStandardStream(1).WriteLine(FILE.value);close();resizeTo(0,0);</script>""")

'Set oExcel = CreateObject("Excel.Application")
sFileSelected = oExec.StdOut.ReadLine

' Get input file name from command line parm
strInFile = sFileSelected

' Create file system object
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Make sure it exists
If Not objFSO.FileExists(strInFile) Then
  WScript.Echo "Input file does not exist."
  WScript.Quit
End If

' Start Excel, create a new worksheet
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

objExcel.Workbooks.OpenText objFSO.GetAbsolutePathName(strInFile),,,xlDelimited,,,,,,,True,"^"
objExcel.WindowState = &HFFFFEFD7
set objWorkBook = objExcel.Workbooks.Open("C:\Users\YOUR_USER_NAME\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.xlsb")
objExcel.Application.Run "PERSONAL.XLSB!YOUR_MACRO_NAME"
Hans
 
TEST
Top