MDSS
asked on
Access Shell Command on 64 Bit Computer
I am trying to run the following shell command in Access 2013 without success. (This works fine on a 32 bit machine)
I set the Sandbox setting to 0.
How do I do this?
Dim RetVal
RetVal = Shell("C:\FT2\FTPTransferD ata.bat", 0)
I set the Sandbox setting to 0.
How do I do this?
Dim RetVal
RetVal = Shell("C:\FT2\FTPTransferD
No reason Shell() should not work under 64 bit. Something else is going on.
What are you seeing?
Jim.
What are you seeing?
Jim.
ASKER
Nothing happens when I run it on a 64 bit machine ... no error, it just doesn't execute the bat file .... when I go to the Command Prompt and execute it outside of Access, the bat file executes fine. Also, when I execute the Shell command on a 32 bit machine, it executes fine from within Access.
64 bit Office?
64 bit Windows?
What I gave you works on 32/64 bit Windows with 32 bit Office.
64 bit Office and WinAPI -- which is what Shell ultimately is -- have quirks.
Please clarify '64 bit'
64 bit Windows?
What I gave you works on 32/64 bit Windows with 32 bit Office.
64 bit Office and WinAPI -- which is what Shell ultimately is -- have quirks.
Please clarify '64 bit'
Has to be security.
What version of Office 32 or 64 bit) and what OS?
Jim.
What version of Office 32 or 64 bit) and what OS?
Jim.
ASKER
It's a 64 bit Windows machine running 32 bit Office Access 2013 ( located in the Program Files (x86) section)
What version of Office are you running? Not that it should matter with Shell, but just checking.
ASKER
I'm using Office 2013 (Access 2013)
Well.
Does Shell fail or the batch file?
Let's try
Shell "c:\windows\system32\cmd.e xe"
Will that fire?
Does Shell fail or the batch file?
Let's try
Shell "c:\windows\system32\cmd.e
Will that fire?
And I suppose we could cheat up a batch
Dim WshShell as object
Set WshShell = WScript.CreateObject("WScr ipt.Shell" )
WshShell.Run "cmd /c C:\FT2\FTPTransferData.bat "
WshShell.AppActivate "c:\windows\system32\cmd.e xe"
Set WshShell = Nothing
Dim WshShell as object
Set WshShell = WScript.CreateObject("WScr
WshShell.Run "cmd /c C:\FT2\FTPTransferData.bat
WshShell.AppActivate "c:\windows\system32\cmd.e
Set WshShell = Nothing
You might want to try ShellExe, you can find the code here:
http://access.mvps.org/access/api/api0018.htm
I had to modify the declaration as below to work with 64 bit office:
http://access.mvps.org/access/api/api0018.htm
I had to modify the declaration as below to work with 64 bit office:
#If VBA7 Then
Private Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
(ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
#Else
Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
(ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
#End If
ASKER
When I execute the following, it opens the cmd window and begins to execute the bat file, but it doesn't complete the bat file and it leaves the cmd window open.
Shell "c:\windows\system32\cmd.e xe /k C:\FT2\Transferdata.bat"
Shell "c:\windows\system32\cmd.e
Switch /k is meant to do that (leave the window open)
Good!
Shell isn't busted.
Now, start throwing PAUSE statements into your batch file and see why it falls down on this machine when it is executed by the system rather than the user.
Good!
Shell isn't busted.
Now, start throwing PAUSE statements into your batch file and see why it falls down on this machine when it is executed by the system rather than the user.
ASKER
I need the switch of /c or /k in order for it to execute the bat file .... otherwise it simply opens the command window.
The bat file executes the following ftp script:
open ftp.test.net
0124959|testworld
testaccess2!
cd /capture
binary
get capture.csv c:\ft2\capture.csv
quit
The problem occurs just after the get command... when I run the bat file outside of Access I get this:
200 PORT command successful.
125 Data connection already open; Transfer starting.
226 Transfer complete.
ftp: 2150 bytes received in 0.08 seconds 27.56Kbytes/sec.
ftp: quit
221 Bye
From within Access I get this:
200 PORT command successful.
150 Opening BINARY mode data connection.
Then it just hangs there until I close the command window
The bat file executes the following ftp script:
open ftp.test.net
0124959|testworld
testaccess2!
cd /capture
binary
get capture.csv c:\ft2\capture.csv
quit
The problem occurs just after the get command... when I run the bat file outside of Access I get this:
200 PORT command successful.
125 Data connection already open; Transfer starting.
226 Transfer complete.
ftp: 2150 bytes received in 0.08 seconds 27.56Kbytes/sec.
ftp: quit
221 Bye
From within Access I get this:
200 PORT command successful.
150 Opening BINARY mode data connection.
Then it just hangs there until I close the command window
When I do that, I need two files, an FTP file and a batch file.
While you can specify the FTP file in the batch, it doesn't respect that from the command line
If have this function to use Ping
I check if the host is up
I then run
Shell "cmd /c x:\pressuretest\ProgramFil es\ftpGetL ist.bat x:\pressuretest\ProgramFil es\ftpGetL ist.txt"
Here's ftpGetList.bat
x:
cd \
cd PressureTest
ftp.exe -i -v -n -s:x:\pressuretest\program files\ftpG etList.txt
Look at the -s switch. It's there, but not respected
Here's the contents
open 10.0.1.10
user NAME PASSWORD
cd sdmmc
cd vrd
ls . list.csv
dir . dir.csv
bye
While you can specify the FTP file in the batch, it doesn't respect that from the command line
If have this function to use Ping
Public Function Ping(strHost)
Dim oPing, oRetStatus, bReturn
Set oPing = GetObject("winmgmts:{impersonationLevel=impersonate}").ExecQuery("select * from Win32_PingStatus where address='" & strHost & "'")
For Each oRetStatus In oPing
If IsNull(oRetStatus.StatusCode) Or oRetStatus.StatusCode <> 0 Then
bReturn = False
' WScript.Echo "Status code is " & oRetStatus.StatusCode
Else
bReturn = True
' Wscript.Echo "Bytes = " & vbTab & oRetStatus.BufferSize
' Wscript.Echo "Time (ms) = " & vbTab & oRetStatus.ResponseTime
' Wscript.Echo "TTL (s) = " & vbTab & oRetStatus.ResponseTimeToLive
End If
Set oRetStatus = Nothing
Next
Set oPing = Nothing
Ping = bReturn
End Function
I check if the host is up
If Ping("10.0.1.10") = False Then
MsgBox "The recorder is off, and the files cannot be refreshed"
Exit Sub
End If
I then run
Shell "cmd /c x:\pressuretest\ProgramFil
Here's ftpGetList.bat
x:
cd \
cd PressureTest
ftp.exe -i -v -n -s:x:\pressuretest\program
Look at the -s switch. It's there, but not respected
Here's the contents
open 10.0.1.10
user NAME PASSWORD
cd sdmmc
cd vrd
ls . list.csv
dir . dir.csv
bye
ASKER
Yes, I have two files....
The bat file (FTPTransferData.bat) looks like this:
@ECHO On
ftp -s:c:\ft2\FTPDownloadData. txt
The script file (FTPDownloadData.txt) looks like this:
open ftp.test.net
0124959|testworld
testaccess2!
cd /capture
binary
get capture.csv c:\ft2\capture.csv
quit
The bat file (FTPTransferData.bat) looks like this:
@ECHO On
ftp -s:c:\ft2\FTPDownloadData.
The script file (FTPDownloadData.txt) looks like this:
open ftp.test.net
0124959|testworld
testaccess2!
cd /capture
binary
get capture.csv c:\ft2\capture.csv
quit
It's not enough to Shell the batch file in automation
It wants the other file too
Shell "cmd.exe /c C:\FT2\Transferdata.bat c:\ft2\FTPDownloadData.txt "
or
Shell "cmd.exe /k C:\FT2\Transferdata.bat c:\ft2\FTPDownloadData.txt "
should get 'er dun if those are the correct paths
It wants the other file too
Shell "cmd.exe /c C:\FT2\Transferdata.bat c:\ft2\FTPDownloadData.txt
or
Shell "cmd.exe /k C:\FT2\Transferdata.bat c:\ft2\FTPDownloadData.txt
should get 'er dun if those are the correct paths
ASKER
Same result as before ....
It hangs up on
200 PORT command successful.
150 Opening BINARY mode data connection.
when running it manually it gives me:
200 PORT command successful.
125 Data connection already open; Transfer starting.
226 Transfer complete.
ftp: 2150 bytes received in 0.08 seconds 27.56Kbytes/sec.
ftp: quit
221 Bye
It hangs up on
200 PORT command successful.
150 Opening BINARY mode data connection.
when running it manually it gives me:
200 PORT command successful.
125 Data connection already open; Transfer starting.
226 Transfer complete.
ftp: 2150 bytes received in 0.08 seconds 27.56Kbytes/sec.
ftp: quit
221 Bye
Well, clearly your FTP file isn't doing the job
Note that mine has
user username password
This is very different
0124959|testworld
and what is this meant to do?
testaccess2!
But I think the grief is here
get capture.csv c:\ft2\capture.csv
These are mine
get "180314REES~DS.D05"
Throw some quotes in and see if it goes!
Note that mine has
user username password
This is very different
0124959|testworld
and what is this meant to do?
testaccess2!
But I think the grief is here
get capture.csv c:\ft2\capture.csv
These are mine
get "180314REES~DS.D05"
Throw some quotes in and see if it goes!
ASKER
But it works perfectly when executed outside of Access
ASKER
So this is what I know:
From the Run screen the following works correctly:
C:\RT2\FTPTransferData.bat
From the Run screen the following works correctly:
C:\Windows\System32\cmd.ex e /c C:\RT2\FTPTransferData.bat C:\RT2\FTPDownloadData.txt
From within Access the following gets hung up:
Shell "C:\Windows\System32\cmd.e xe /c C:\RT2\FTPTransferData.bat C:\RT2\FTPDownloadData.txt "
From the Run screen the following works correctly:
C:\RT2\FTPTransferData.bat
From the Run screen the following works correctly:
C:\Windows\System32\cmd.ex
From within Access the following gets hung up:
Shell "C:\Windows\System32\cmd.e
<<It's not enough to Shell the batch file in automation
It wants the other file too>>
You can do it either way and I do it the other way, specifying a script switch (-s) as part of the batch file and only call the batch file. Works fine.
What it is is the pipe delimiter (vertical bar) in the username.
A vertical pipe inside of a batch file is not a good idea. Change the user name.
And no points please.
Jim
It wants the other file too>>
You can do it either way and I do it the other way, specifying a script switch (-s) as part of the batch file and only call the batch file. Works fine.
What it is is the pipe delimiter (vertical bar) in the username.
A vertical pipe inside of a batch file is not a good idea. Change the user name.
And no points please.
Jim
Quotes around the username may get around the issue as well.
Jim.
Jim.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<<Turns out there was nothing wrong with the original bat or script file .... the standard Microsoft FTP client causes the error ... I replaced it with WinSCP and it works fine.>>
I would disagree. I have used the standard Microsoft, MoveIt, and WinSCP clients for many years and have never had a problem with any calling them from Access.
Something in the batch file is an issue for the Microsoft client.
Jim.
I would disagree. I have used the standard Microsoft, MoveIt, and WinSCP clients for many years and have never had a problem with any calling them from Access.
Something in the batch file is an issue for the Microsoft client.
Jim.
the standard Microsoft FTP client causes the error
Not really, since my posted examples are using the standard Windows FTP client and work fine.
How's about
'the syntax of my batch and ftp files doesn't play nicely with running it through Shell'
Glad you got it figured out, though
Not really, since my posted examples are using the standard Windows FTP client and work fine.
How's about
'the syntax of my batch and ftp files doesn't play nicely with running it through Shell'
Glad you got it figured out, though
ASKER
It was the only solution offered that worked to solve my problem.
Shell "cmd /c C:\FT2\FTPTransferData.bat
The returned value is of little use