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! 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
*****
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.
Shell "cmd /k ""C:\Documents and Settings\Lyle Fairfield\My
Documents\Access\first.bat""", vbNormalFocus
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
It must be all one line.
Did you remove the line feed introduced by the news client?
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.
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
Still have the same problem. Very weird.
Is there a way to accomplish the same thing using FileSystemObject?
The cmd part is actually redundant.
This should work.
Shell "C:\Documents and Settings\Lyle Fairfield\My
Documents\Access\first.bat"
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
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.
I guess that you have made an error in the path.
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.
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
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
Try this to execute a batch file in a module
Dim RetVal
RetVal = Shell("C:\Documents and
Settings\t833299\Desktop\Extracts\clean.bat", 1)
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.
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.
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.
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?
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.
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
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?
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.
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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)...
|
by: ScoobyDoo |
last post by:
Anyone know how I can create and edit batch files from MS Access 2002?
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
|
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...
| |