I have a .cmd script that succesfully works directly from the DOS command line, or by simply clicking on it in Explorer. The script looks within a specific folder and randomly opens a jpg file from within it. I want to use this within the Office tools, a command button that when clicked runs the script.
This is what i have in Excel:
Private Sub CommandButton3_Click()
Dim RetVal
RetVal = shell(“D:\Mythos\rand.cmd”, 1)
End Sub
When I click on the command button, an explorer window opens. I’m expecting a jpg file to open in Office Picture Manager (or whatever jpg viewer I wish to use)
The script looks like this:
setlocal EnableDelayedExpansion
set count=0
for /f “delims=” %%a in (‘dir /b *.jpg’) do @(
set filename[!count!]=%%a
set /a count = count + 1
)
set /a choose = (%random% * 32768 + %random%) %% count
set chosen=!filename[%choose%]!
start “Opening %chosen%” “%chosen%”
Any ideas where I’m going wrong?
I now have a different solution.
In Excel I created the following macro:
Sub mythos()
Shell “CMD /C D:\Mine\Arkham_Horror\Mythos\rand.cmd”, vbNormalFocus
End Sub
In the rand.cmd file, I added two extra lines at the top:
d:
cd Mine\Arkham_Horror\Mythos
The lines change drive and then directory, then the script runs.
When I run the macro, a randomised jpg is opened – voila!
I can now run this marco via a command button.
Next steps:
Instead of simply opening the jpg, i want it instantiated into the spreadsheet (or visio drawing), and at a spcific location…..