• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 799
  • Last Modified:

Execute bat file From Access 2007 vba

I am sure this has been answered many different ways, but I haven't had success in what results I have found and tried. But is there a way to execute a *.bat file thru access 2007 vba. Everything I have tried either quickly open and closes the cmd window with no results, does nothing, or just opens the cmd window and also does nothing. I have tried the Shell command and run app command.

The *.bat file runs successfully with scheduled tasks or manually executing it.

What the *.bat file does is automatically extracts a zip file to a specific folder.

Thank you in advance for all your help.
0
vdornan
Asked:
vdornan
  • 5
  • 4
  • 3
1 Solution
 
cfEngineersCommented:
Try
call Shell ( Environ$ ( "COMSPEC" ) & " /c  c:\test.bat", vbNormalFocus)  

but replace /c which will auto close the with /k which will keep the dos box open

0
 
vdornanAuthor Commented:
I did try that and the window does stay open and gives the following message. 'C:\Program' is not recognized as an internal or external command, operable program or batch file. Then it displays a directory path.

Now the bat file is located in the Program Files path directory. Not sure if they has anything to do with it.
0
 
GRayLCommented:
If you batch file is named 'getzip.bat' is in the same directory as the Access mdb you are using, press Alt+f11 to get to the Immediate pane and type this line:

shell "getzip.bat", vbNormal

It might be useful to type 'pause' as the first line your batch file.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
cfEngineersCommented:
That is because of the dos file names Program Files becomes progra~1

Please paste your entire string.

0
 
vdornanAuthor Commented:
GRayL, I did try your idea but have noticed that the bat file needs to be in the same directory as the WinZip program... the location of the bat file is C:\Program Files\WinZip\Extract.bat
0
 
GRayLCommented:
Can you post the paths to  the mdb, winzip program, and zip files.  I misread 'extract' as 'move'.
0
 
vdornanAuthor Commented:
The function I am calling thru a runapp macro is the following.

Function batch()
    Call Shell(Environ$("COMSPEC") & " /k  C:\Program Files\WinZip\Extract.bat", vbNormalFocus)
End Function

Thank you again for all your help
0
 
vdornanAuthor Commented:
The path to the database that is running this is
C:\Data\Projects\Service\Ad-Hoc Reports\Tools\Daily RYG Run.accdb

The Zip Program path is
C:\Program Files\WinZip\

The bat file path is
C:\Program Files\WinZip\Extract.bat

0
 
GRayLCommented:
Can you show us what you have in Extract.bat?
0
 
cfEngineersCommented:
Function batch()
    Call Shell(Environ$("COMSPEC") & " /k  C:\Progra~1\WinZip\Extract.bat", vbNormalFocus)
End Function

This should work for you.
0
 
vdornanAuthor Commented:
Worked perfectly.  Thank you both so much for all your help in resolving this....
0
 
cfEngineersCommented:
Any Time
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 5
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now