473,322 Members | 1,379 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Run a batch file from Microsoft Access

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
26 34435
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
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
Shell "cmd /k ""C:\Documents and Settings\Lyle Fairfield\My
Documents\Access\first.bat""", vbNormalFocus

Feb 6 '06 #4
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
It must be all one line.
Did you remove the line feed introduced by the news client?

Feb 6 '06 #6
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
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
Still have the same problem. Very weird.

Feb 6 '06 #9
Is there a way to accomplish the same thing using FileSystemObject?

Feb 6 '06 #10
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
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
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
I guess that you have made an error in the path.

Feb 7 '06 #14
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
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
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
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
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
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
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Jon Maz | last post by:
Hi All, To allow myself to make development changes directly on a remote server *without* having to compile on my local dev machine and then upload the dll's to remote, I have created a...
14
by: Mark C. | last post by:
I'm trying to call a batch file that I've built using the FileSystemObject and CreateObject("Wscript.Shell"), oShell.Run... in an asp script. Naturally, I can get the script to work from a command...
1
by: Rob | last post by:
I'm running a batch file using the Shell function. When I manually launch the batch file, the window remains open, since I use the 'pause' statement. But when I launch the batch file within...
4
by: Bill | last post by:
I need help closing a CMD window when it is executed from Access. 1) The batch file is called from Access. 2) Access closes, 3) the batch runs a copy of the access database (creating a backup)...
4
by: ScoobyDoo | last post by:
Anyone know how I can create and edit batch files from MS Access 2002?
6
by: Charles Neitzel | last post by:
I'm trying to write a windows application in C# (Using Microsoft Visual C# 2005 Express) that is nothing more than a simple UI with buttons on it. The buttons do various things like running...
4
by: CliffKing | last post by:
I am trying to use Windows Task Scheduler to run a batch file for an already open MS Access database. Below is the syntax to the batch file: Batch file: DailySalesExport.bat REM This runs the...
12
by: Selva Chinnasamy | last post by:
Hi I am using batch commands against Ms-Access and getting an error Message "Characters found after end of SQL statement." String Here is my sql Dim str_screens As String = "Select * from Screens...
1
by: mvdkwong | last post by:
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...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.