Solved Run .cmd file from a command button in Excel

May 17, 2012 at 08:50:08
Specs: Windows XP
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?


See More: Run .cmd file from a command button in Excel

Report •

✔ Best Answer
May 18, 2012 at 04:19:26
Thanks again,

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.....



#1
May 17, 2012 at 12:03:26
I can't help you with your script file,
but am curious why you don't just use a =HYPERLINK() command
within Excel to open the .jpg?

You can have the PATH in one cell and the Pix name in another, like:

A1 = C:\Users\Whitlos\Pictures

B1 = DSC_0001.JPG

C1 = =HYPERLINK(A1&"\"&B1)

Click on Cell C1 and your picture pops up.

MIKE

http://www.skeptic.com/


Report •

#2
May 18, 2012 at 00:14:30
Thanks for your reply.

This solution would be ok if i only wanted DSC_0001.jpg everytime I clicked on C1.

My script chooses a random jpg from a folder containing 56 files. I want a random picture to appear on each click, and preferably not a repeated one, but that will be later in development.


Report •

#3
May 18, 2012 at 03:51:51
You could use either the RAND() or the RANDBETEEN() functions along with the INDIRECT() function to get your random picture.

Your complete Path in A1 = C:\Users\Whitlos\Pictures\Vacations\2011

A list of all your Vacation Pix in Column B, starting at B1 and ending at B56

B1 = DSC_001.jpg
B2 = DSC_002.jpg
etc. etc.

In C1 enter the formula:

=HYPERLINK(A1&"\"&INDIRECT("B"&RANDBETWEEN(1,56)))

Now every time you click on cell C1 you get a Random Picture.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
May 18, 2012 at 04:19:26
✔ Best Answer
Thanks again,

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.....


Report •

Ask Question