Run Script (.bat file) from VBA

joefrench

Active Member
Joined
Oct 4, 2006
Messages
357
I have some Script (a .bat file) that, when run in the directory that it exists in, will delete all "old versions" of files (i.e.- the program creating the files {ProEngineer}, on each save, creates a new "version" of the file with a .1,.2,.3,.4,etc... appended to the end of it).
The script in the file is as follows:
Code:
@echo off

Set MC = unset
if "%PROCESSOR%" == "INTEL_64" set MC=ia64_nt
if "%PROCESSOR%" == "INTEL_486" set MC=i486_nt
if "%PROCESSOR%" == "MIPS_R4000" set MC=r4000_nt
if "%PROCESSOR%" == "Alpha_AXP" set MC=alpha_nt
if "%PROCESSOR_ARCHITECTURE%" == "IA64" set MC=ia64_nt
if "%PROCESSOR_ARCHITECTURE%" == "x86" set MC=i486_nt
if "%PROCESSOR_ARCHITECTURE%" == "MIPS" set MC=r4000_nt
if "%PROCESSOR_ARCHITECTURE%" == "ALPHA" set MC=alpha_nt
if not "%winbootdir%" == "" set mc=i486_win95
if not "%MC%" == "unset" goto mc_done
echo ERROR Cannot detect what machine type you have.
echo Please make one of the following settings:
echo.
echo set PROCESSOR=Alpha_AXP     - For Windows NT Alpha based machines.
echo.
echo set PROCESSOR=MIPS_R4000    - For Windows NT MIPS based machines.
echo.
echo set PROCESSOR=INTEL_486     - For Windows NT Intel based machines.
echo.
echo set PROCESSOR=INTEL_64      - For Windows Intel Itanium based machines.
echo.
echo set winbootdir=<boot dir>   - For Windows 95/98 machines.
echo.
exit
: mc_done

if NOT "%mc%" == "i486_win95" goto postenv4096
  if "%env4096%" == "true" goto postenv4096
    Set env4096 = True
    command.com /E:4096 /C %0 %1 %2 %3 %4 %5 %6 %7 %8 %9
    set env4096=
    GoTo ptc_end
: postenv4096

set PRO_MACHINE_TYPE=%MC%

if "%PROOBJ_START_DIRECTORY%" == "" set PRO_DIRECTORY=
if NOT "%PRO_DIRECTORY%" == "" "%PRO_DIRECTORY%\bin\%MC%_ptc_setvars" %0 "purge" bat
if "%PRO_DIRECTORY%" == "" %MC%_ptc_setvars %0 "purge" bat
Call ptc_setvars.bat
del ptc_setvars.bat

if NOT "%mc%" == "i486_win95" set start_cmd=start ""
if "%mc%" == "i486_win95" set start_cmd=start

set PRO_DIRECTORY=%PRODIR%
if NOT "%PTCPATH%" == "" goto ptcpathset

if NOT "%mc%" == "i486_win95" goto not95
Set Path = "%PRO_DIRECTORY%\bin;%path%"
GoTo pathdone
: not95
set path=%PRO_DIRECTORY%\bin;%path%
: pathdone

Set PTCPATH = True
: ptcpathset


"%PRODIR%\%MC%\obj\purge.exe" %1 %2 %3 %4 %5
: ptc_end
Currently, I am doing this by manually copying the purge.bat file to the directory that I want purged and double clicking it. Or by using an added right click action in Windows Explorer "C:\WINDOWS\system32\cmd.exe /c cd %1 &"C:\Program Files\proeWildfire 3.0\bin\purge.bat" *"

I would like to run this .bat file (as well as perform many other actions) from Excel VBA to purge directories based off of a value in Excel. I have tried to use VBA to copy the existing purge.bat file into the directory and run it through the shell command but this does not seem to work.

Note: I do not know much about the script except for what it does and that it was installed with the program (ProE).

I had been advised in a previous post http://www.mrexcel.com/board2/viewtopic.php?t=262396 to code the BAT file to accept a parameter and use %1 in the BAT file to do something with the passed string such as a subfolder but I am not sure how this would be done.

Would it be possible to re-write the script through VBA code and execute it? :LOL:
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

joefrench

Active Member
Joined
Oct 4, 2006
Messages
357
A program that I use (ProEngineer- 3D solid modeling design software) creates "version" files of a file when saved. (i.e. - when save is selected, the program does not save the file one is working on itself but creates a new file at the current status of the "part" of the same name with a number appended to the end of the filename. This way, if one created something one didn't like and wanted to go back to the previous "version", it's simple to do.) What the batch file does is, when double clicked, it will delete all of the "old versions" of the "part" in the directory it is placed in. This is done to basically clean up the directory once a design is complete.

Hope this makes sense! :rolleyes:
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Can you give a sample of the file names you want to delete and the file names you want to keep and what directory the files are in?
 

joefrench

Active Member
Joined
Oct 4, 2006
Messages
357
Sure thing!

If I have the following files in a directory:

2076_b_plate.prt
2076_a_plate.prt.1
2076_a_plate.prt.2
2076_a_plate.prt.3
2076_a_plate.prt.4
2076_a_plate.prt.5
2076_a_plate.prt.6

2076_b_plate.prt
2076_b_plate.prt.1
2076_b_plate.prt.2
2076_b_plate.prt.3
2076_b_plate.prt.4
2076_b_plate.prt.5
2076_b_plate.prt.8

1000976_rev01.prt
1000976_rev01.prt.30
1000976_rev01.prt.31
1000976_rev01.prt.32
1000976_rev01.prt.33
1000976_rev01.prt.37
1000976_rev01.prt.39

2076.asm.9
2076.asm.10
2076.asm.11
2076.asm.12
2076.asm.13
2076.asm.14
2076.asm.15

2076.drw.17
2076.drw.18
2076.drw.19
2076.drw.20
2076.drw.21
2076.drw.22


I would like to have the following files remaining after the "purge":
2076_a_plate.prt.6
2076_b_plate.prt.8
1000976_rev01.prt.39
2076.asm.15
2076.drw.22


The naming convention is as follows
User defined name .constantfile extension.version #
2076_a_plate .prt.6

The file names (user defined) are in no way constant. The versioning of the files can go up to and beyond 500. Each version of the file is a timestamp of a single part model, assembly or drawing file.

I have considered using VBA to pull the file names into excell, parsing the version into another cell, sorting in descending order based on that cell then deleting all files that aren't in the top row :eek: .....but that seems a little bit extensive considering that I already have this batch file that does what I need it to do.

Also, the directory the files would be in is random. Somewhat random anyway. I was planning on this being dictated by the user for the time being.
 

Hermanito

Well-known Member
Joined
Apr 4, 2007
Messages
1,238
Hi there co-ProE user ;)

do you insist on doing it via Excel or can a 3rd party tool do the job?
I can recommend the Spekan Purge Tool: see this link
http://www.ptcuser.org/rugs/U53/tips/utilities.htm
There you can download version 2.3. There should also be already a version 3.3 around, but I couldn't find download links for that one...

I hope that helps...
 

joefrench

Active Member
Joined
Oct 4, 2006
Messages
357
Hello fellow ProE user! :LOL:

I have looked into the Spekan Purge Tool and like it.

The only problem is I'm looking to use excel to create a somewhat broader application. The way we currently do things is, after a design is done and released, we purge our ProE directory and compress the files into one zip file. However, we've been having problems with users deleting entire directories or creating a zip file without placing any of the files into it, then deleting the files that were supposed to be placed into the zip file. :devilish:

I was also planning on adding some Change Notice/Router functionality to it which excel lends itself kindly to....

I'm basically trying to create a miniature PDM system to use until I can convince the powers that be to purchase one. :)
 

Hermanito

Well-known Member
Joined
Apr 4, 2007
Messages
1,238
The best way to convince 'management' is to let those users mess up some more :LOL:
If you write an Excel application to avoid such errors, they will see no need to invest in a real PDM system...

I've worked in such a company, they refused to use a PDM-system... they're out of business now :eek:

Anyway, creating a PDM system (even a miniature one) in Excel is quite an ambitious goal... I wish you the very best with it...
 

joefrench

Active Member
Joined
Oct 4, 2006
Messages
357
That's a very good point but unfortunately, all it is doing right now is causing more work & headaches for myself and a few other people.

But alas, I suppose you are correct. Maybe I should redirect my focus for the time being. (At least until I can get this purge figured out! :wink:
)

Do you use a PDM system? If so, which one? Intralink? Windchill? DDM?

Thanks for sharing Hermanito
 

Forum statistics

Threads
1,181,649
Messages
5,931,215
Members
436,784
Latest member
amuljono

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top