By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,131 Members | 1,749 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,131 IT Pros & Developers. It's quick & easy.

Run a batch file from Microsoft Access

P: n/a
What's the trick to running a batch file from VBA? I'm trying to call
it using the Shell function but it's not working for me. If I
double-click the batch file or run it from the command line it works.

I've tried:

RetVal = Shell("cmd /c clean.bat", vbNormalFocus)
RetVal = Shell("clean.bat", vbNormalFocus)
RetVal = Shell("<full path>clean.bat", vbNormalFocus)
Call Shell...

Any help would be greatlyl appreciated!

Feb 3 '06 #1
Share this Question
Share on Google+
26 Replies


P: n/a
Shell "cmd /k c:\first.bat", vbNormalFocus

*****
c:\first.bat is:
@echo off
rem this is the first batch file, first.bat
echo Calling second
call c:\second.bat
echo Returned from second
*****
*****
c:\second.bat is:
@echo off
rem this is second.bat
echo Running second batch file
*****

Feb 4 '06 #2

P: n/a
Thanks mike & Lyle! I went with Lyle's suggestion. One other question.
If the path to the batch file contains spaces in the directory names,
how do I avoid the error: 'C:\Documents' is not recognized as an
internal or external command, operable program or batch file.

i.e. C:\Documents and Settings\csmith\Desktop\Extracts\clean.bat

It works if the path has no spaces e.g. c:\clean.bat, but not if the
path has spaces.

Feb 6 '06 #3

P: n/a
Shell "cmd /k ""C:\Documents and Settings\Lyle Fairfield\My
Documents\Access\first.bat""", vbNormalFocus

Feb 6 '06 #4

P: n/a
Thanks, Lyle. I tried that but I still get the same error:

Shell "cmd /k ""C:\Documents and
Settings\t833299\Desktop\Extracts\clean.bat""", vbNormalFocus

Feb 6 '06 #5

P: n/a
It must be all one line.
Did you remove the line feed introduced by the news client?

Feb 6 '06 #6

P: n/a
Yes, it's on one line (I did remove the line feed). It's exactly like
yours except the path is C:\Documents and
Settings\t833299\Desktop\Extracts\clean.bat. I'm looking at them
side-by-side. Any ideas? I still get 'C:\Documents' is not recognized
as an internal or external command, operable program or batch file.

Feb 6 '06 #7

P: n/a
No idea ... another way you could try is:

Dim s As String
s = "cmd /k " & Chr(34) & "C:\Documents and Settings\Lyle Fairfield\My
Documents\Access\first.bat" & Chr(34)
Shell s

Feb 6 '06 #8

P: n/a
Still have the same problem. Very weird.

Feb 6 '06 #9

P: n/a
Is there a way to accomplish the same thing using FileSystemObject?

Feb 6 '06 #10

P: n/a
The cmd part is actually redundant.

This should work.

Shell "C:\Documents and Settings\Lyle Fairfield\My
Documents\Access\first.bat"

Feb 6 '06 #11

P: n/a
You could convert the long path to a short one

Paste the following to a module

'***************************
Option Explicit

Private Declare Function GetShortPathName _
Lib "kernel32" Alias "GetShortPathNameA" ( _
ByVal lpszLongPath As String, _
ByVal lpszShortPath As String, _
ByVal cchBuffer As Long _
) As Long

Function ShortPath(RHS As String) As String
Dim lpszLongPath As String
Dim lpszShortPath As String
Dim cchBuffer As Long
Dim lngRet As Long

lpszLongPath = RHS
cchBuffer = 255
lpszShortPath = Space(cchBuffer)

lngRet = GetShortPathName(lpszLongPath, lpszShortPath, cchBuffer)

ShortPath = Left(lpszShortPath, lngRet)
End Function
'***************************

Then

Shell "cmd /k " & _
ShortPath("C:\Documents and
Settings\t833299\Desktop\Extracts\clean.bat"), _
vbNormalFocus
--

Terry Kreft
"mvdkwong" <mv******@gmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...
Thanks, Lyle. I tried that but I still get the same error:

Shell "cmd /k ""C:\Documents and
Settings\t833299\Desktop\Extracts\clean.bat""", vbNormalFocus

Feb 7 '06 #12

P: n/a
Lyle, I tried that and it says "File not found".

Terry, there's an area for the following block:

Private Declare Function GetShortPathName _
Lib "kernel32" Alias "GetShortPathNameA" ( _
ByVal lpszLongPath As String, _
ByVal lpszShortPath As String, _
ByVal cchBuffer As Long _
) As Long

The error is "Compile error: Only comments may appear after End Sub,
End Function, or End Property".

Please advise. Thanks very much for your help.

Feb 7 '06 #13

P: n/a
I guess that you have made an error in the path.

Feb 7 '06 #14

P: n/a
That bit needs to go in the declarations section right at the top of the
module.

I'm guessing you pasted into a module where you already had some code.

Select the block
Cut it to the clipboard
Move to the top of the module (but below any lines which start with the word
Option)
Paste it back in.

--

Terry Kreft
"mvdkwong" <mv******@gmail.com> wrote in message
news:11*********************@g47g2000cwa.googlegro ups.com...
Lyle, I tried that and it says "File not found".

Terry, there's an area for the following block:

Private Declare Function GetShortPathName _
Lib "kernel32" Alias "GetShortPathNameA" ( _
ByVal lpszLongPath As String, _
ByVal lpszShortPath As String, _
ByVal cchBuffer As Long _
) As Long

The error is "Compile error: Only comments may appear after End Sub,
End Function, or End Property".

Please advise. Thanks very much for your help.

Feb 8 '06 #15

P: n/a
Thanks, Terry. I think I'm close but it's not quite there. Here's the
code. I have a button that calls mcr_Send_Extract_Files(). Thanks for
your patience.

'************************************************* **
Option Explicit

Private Declare Function GetShortPathName _
Lib "kernel32" Alias "GetShortPathNameA" ( _
ByVal lpszLongPath As String, _
ByVal lpszShortPath As String, _
ByVal cchBuffer As Long _
) As Long

Function ShortPath(RHS As String) As String
Dim lpszLongPath As String
Dim lpszShortPath As String
Dim cchBuffer As Long
Dim lngRet As Long
lpszLongPath = RHS
cchBuffer = 255
lpszShortPath = Space(cchBuffer)
lngRet = GetShortPathName(lpszLongPath, lpszShortPath, cchBuffer)
ShortPath = Left(lpszShortPath, lngRet)
End Function

'------------------------------------------------------------
' mcr_Send_Extract_Files
'
'------------------------------------------------------------

Function mcr_Send_Extract_Files()
On Error GoTo mcr_Send_Extract_Files_Err

'Call Reflection script to ASCII FTP files to EDW2
'<CODE HERE>

'Delete the extract files from the Extracts folder to prepare for
next export
Shell "cmd /k " & ShortPath("C:\Documents and
Settings\t833299\Desktop\Extracts\clean.bat"), vbNormalFocus

mcr_Send_Extract_Files_Exit:
MsgBox "Success"
Exit Function
mcr_Send_Extract_Files_Err:
MsgBox Error$
Resume mcr_Send_Extract_Files_Exit

End Function

Feb 8 '06 #16

P: n/a
Well that looks like it should work, why do you say you are "close but it's
not quite there"?

--

Terry Kreft
"mvdkwong" <mv******@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
Thanks, Terry. I think I'm close but it's not quite there. Here's the
code. I have a button that calls mcr_Send_Extract_Files(). Thanks for
your patience.

'************************************************* **
Option Explicit

Private Declare Function GetShortPathName _
Lib "kernel32" Alias "GetShortPathNameA" ( _
ByVal lpszLongPath As String, _
ByVal lpszShortPath As String, _
ByVal cchBuffer As Long _
) As Long

Function ShortPath(RHS As String) As String
Dim lpszLongPath As String
Dim lpszShortPath As String
Dim cchBuffer As Long
Dim lngRet As Long
lpszLongPath = RHS
cchBuffer = 255
lpszShortPath = Space(cchBuffer)
lngRet = GetShortPathName(lpszLongPath, lpszShortPath, cchBuffer)
ShortPath = Left(lpszShortPath, lngRet)
End Function

'------------------------------------------------------------
' mcr_Send_Extract_Files
'
'------------------------------------------------------------

Function mcr_Send_Extract_Files()
On Error GoTo mcr_Send_Extract_Files_Err

'Call Reflection script to ASCII FTP files to EDW2
'<CODE HERE>

'Delete the extract files from the Extracts folder to prepare for
next export
Shell "cmd /k " & ShortPath("C:\Documents and
Settings\t833299\Desktop\Extracts\clean.bat"), vbNormalFocus

mcr_Send_Extract_Files_Exit:
MsgBox "Success"
Exit Function
mcr_Send_Extract_Files_Err:
MsgBox Error$
Resume mcr_Send_Extract_Files_Exit

End Function

Feb 9 '06 #17

P: n/a
Try this to execute a batch file in a module

Dim RetVal
RetVal = Shell("C:\Documents and
Settings\t833299\Desktop\Extracts\clean.bat", 1)

Feb 9 '06 #18

P: n/a
Terry, by "close", I mean it calls mcr_Send_Extract_Files() without
any error, but all that shows is a command prompt window showing "C:\My
Documents> ".

Bruce, I tried that and it gives the error "File not found". But I know
the file exists in that path.

I'm really not sure why this isn't working; it seems so straightforward.

Feb 9 '06 #19

P: n/a
OK try this, change the mcr_Send_Extract_Files function in the same way as
shown below.

This should then clearly tell you whether the file is in the location your
command line expects it to be.

The Debug.print statements will give you a command which you can test at the
Run prompt.
'************************************************* **
'------------------------------------------------------------
' mcr_Send_Extract_Files
'
'------------------------------------------------------------

Function mcr_Send_Extract_Files()
On Error GoTo mcr_Send_Extract_Files_Err
Dim strPath as string
Dim strCommand as string
'Call Reflection script to ASCII FTP files to EDW2
'<CODE HERE>

'Delete the extract files from the Extracts folder to prepare for
next export
strPath = ShortPath("C:\Documents and
Settings\t833299\Desktop\Extracts\clean.bat")
strCommand = "cmd /k " & strPath

debug.print strPath
debug.print Dir(strPath)
debug.print strCommand

If Len(Dir(StrPath)) > 0 then
Shell strCommand, vbNormalFocus
Else
Msgbox "Can't find file to execute"
End if

mcr_Send_Extract_Files_Exit:
MsgBox "Success"
Exit Function
mcr_Send_Extract_Files_Err:
MsgBox Error$
Resume mcr_Send_Extract_Files_Exit

End Function
'************************************************* **
--

Terry Kreft
"mvdkwong" <mv******@gmail.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...
Terry, by "close", I mean it calls mcr_Send_Extract_Files() without
any error, but all that shows is a command prompt window showing "C:\My
Documents> ".

Bruce, I tried that and it gives the error "File not found". But I know
the file exists in that path.

I'm really not sure why this isn't working; it seems so straightforward.

Feb 10 '06 #20

P: n/a
Hi Terry, I pasted in the code with the debug.print statements and
here's what I get:

1. A command prompt window with the "C:\My Documents>" prompt
2. A msgbox "Success"

But the batch file is not run.

Feb 10 '06 #21

P: n/a
Random Thoughts:
Do you have full rights to the batch file's folder? Can you run the
batch file by opening its folder and double clicking on it?
How do we know the batch file is not run? Is there an Echo command in
the batch file, say at the end, that might say, "All Done"?.
Will the batch file run thorugh a shell call when placed in another
folder, say, "c:\"?
Can you cut, paste and post the batch file's script?
Can you cut, paste and post the procedure in which you are calling the
shell?
Is t833299 an administrator?

Feb 10 '06 #22

P: n/a
When I run:

Shell "cmd /k " & Chr(34) & "C:\Documents and Settings\Lyle
Fairfield\Desktop\New Folder\first.bat" & Chr(34), vbNormalFocus
it works.

Shell "cmd /k " & Chr(34) & "C:\Documents and
Settings\t833299\Desktop\Extracts\clean.bat" & Chr(34), vbNormalFocus
I get the same message you describe:
'C Documents ' is not recognized ....

The first file and its folders exist.
The second file and its folders do not exist.

Feb 10 '06 #23

P: n/a
Hi Lyle, answers to your questions:

Do you have full rights to the batch file's folder? Yes

Can you run the batch file by opening its folder and double clicking on
it? Yes

How do we know the batch file is not run? Is there an Echo command in
the batch file, say at the end, that might say, "All Done"? The batch
file deletes all the files in a folder in the same path. I know it runs
properly when the folder contents are empty.

Will the batch file run thorugh a shell call when placed in another
folder, say, "c:\"? Yes, it does. In the Shell call, if I have
"c:\clean.bat" in the path instead it runs fine.

Can you cut, paste and post the batch file's script?

Here it is:
cd C:\Documents and Settings\t833299\Desktop\
del extracts /q
exit

Can you cut, paste and post the procedure in which you are calling the
shell?

I've tried running:
Shell "cmd /k " & ShortPath("C:\Documents and
Settings\t833299\Desktop\Extracts\clean.bat"), vbNormalFocus
Shell "cmd /k " & Chr(34) & "C:\Documents and
Settings\t833299\Desktop\Extracts\clean.bat" & Chr(34), vbNormalFocus
Shell "C:\Documents and Settings\t833299\Desktop\Extracts\clean.bat"
etc....

Is t833299 an administrator? Yes

Feb 10 '06 #24

P: n/a
Could you be talked into

On Error Resume Next
Kill "C:\Documents and Settings\t833299\Desktop\Extracts\*.*"
On Error Goto 0 'or somewher else

which should, I believe do the work of your batch file?

Feb 11 '06 #25

P: n/a
What values were printed in the debug window?

Press Ctrl-G on the keyboard and see what i printed in the window that is
revealed.

--

Terry Kreft
"mvdkwong" <mv******@gmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...
Hi Terry, I pasted in the code with the debug.print statements and
here's what I get:

1. A command prompt window with the "C:\My Documents>" prompt
2. A msgbox "Success"

But the batch file is not run.

Feb 11 '06 #26

P: n/a
Hi Terry & Lyle,

Thanks so much for your help on this matter. The simplest answer
worked, the "Kill" command that was suggested, which is all I was
needing all along --- didn't even know there was such a command.

Feb 13 '06 #27

This discussion thread is closed

Replies have been disabled for this discussion.