Advanced: Shelling to DOS without using "Shell" command

ManningFan

Due to the way our IT group has set up our servers, we are not allowed
to use the Shell() command.

We are already doing a similar action with an FTP command. Our code
is thus:

sExe = Environ$("COMSPEC")
sExe = Left(sExe, Len(sExe) - Len(Dir(sExe)))
sExe2 = sExe & "ftp.exe -s:" & Q & sScrFile & Q

ShellWait sExe2, vbNormalFocus

In the above example, Q = """" (don't ask me why...) and sScrFile is
the name of a text file which contains a list of commands.

Once we do this, we need to move a file from one directory to
another. We can't use the DOS "Move" command while in the FTP shell,
so we have to open a new instance in the CMD shell.

My code is thus:

sExe3 = sExe & "cmd.exe -s:" & sScrFile2

ShellWait sExe3, vbNormalFocus

All it does is open a CMD window and sit there, it's not processing
the text file I'm sending it like the FTP shell does.

Anyone know why? Can you fix my code? Any help is appreciated.

Jun 15 '07 #1

Pachydermitis

Usually when I need to move a file I use the kill and filecopy
commands.
So first make sure you have a local file to copy, then delete the
destination file, then copy the local file to the destination, then
verify the dest file, then delete the local file.
I hope this helps.

Here is a snippet from some of my code that does some of this:

Dim bCopyFile As Boolean
Dim sNewImagePath As String
bCopyFile = True
sNewImagePath = sDefaultImagePath & "\" & sFileName
If UncDir(sNewImagePath) <"" Then 'see if the file is
already there
bCopyFile = False
If MsgBox("The file " & sNewImagePath & " already
exists." & vbCrLf & _
"Would you like to overwrite it?", vbYesNo,
"Overwrite File") = vbYes Then
bCopyFile = DeleteFile(sNewImagePath)
End If
End If
If bCopyFile Then
FileCopy sImagePath, sNewImagePath
UpdateImage sNewImagePath
End If
There are two caveats.
1) the kill (or delete file) command will error if there is no file
present to delete
2) the dir command in vb doesn't seem to like network unc paths (\
\whatever\whereever) very much so I made my own
Function DeleteFile(ByVal strPathAndFile As String) As Boolean
'************************************************* **********************************
' Author Daniel Tweddell
' Revision Date 04/14/03
'
' Deletes a file
'************************************************* **********************************
On Error GoTo Err_Function
DeleteFile = True 'default to true
If UncDir(strPathAndFile) <"" Then 'make sure the file is
there
Kill strPathAndFile 'delete a file
End If
Exit Function
Err_Function:
ErrHandler Err.Number, Err.Description, "DeleteFile()", bSilent
DeleteFile = False 'if there is a problem, false
End Function
Function UncDir(ByVal strFilename As String) As String
'************************************************* ***************
' Author Daniel Tweddell
' Revision Date 12/13/03
'
' The Dir cmd Errors with unc network paths that do not exist
' so this seems to work.
'************************************************* ***************
On Error GoTo Err_Function
Dim FileData As WIN32_FIND_DATA, apiFileHandle As Long
Dim lngLastError As Long, FileDateTime As FILETIME, SystemDateTime
As SYSTEMTIME
apiFileHandle = FindFirstFile(strFilename, FileData)
If apiFileHandle <INVALID_HANDLE_VALUE Then UncDir = strFilename
Call FindClose(apiFileHandle)
Exit Function
Err_Function:
ErrHandler Err.Number, Err.Description, "UncDir()", bSilent
End Function

uncdir requires:
Private Type FILETIME ' 8 Bytes
dwLowDateTime As Long
dwHighDateTime As Long
End Type

Private Type SYSTEMTIME ' 16 Bytes
wYear As Integer
wMonth As Integer
wDayOfWeek As Integer
wDay As Integer
wHour As Integer
wMinute As Integer
wSecond As Integer
wMilliseconds As Integer
End Type

Private Type WIN32_FIND_DATA ' 318 Bytes
dwFileAttributes As Long
ftCreationTime As FILETIME
ftLastAccessTime As FILETIME
ftLastWriteTime As FILETIME
nFileSizeHigh As Long
nFileSizeLow As Long
dwReservedØ As Long
dwReserved1 As Long
cFileName As String * 260
cAlternate As String * 14
End Type

Declare Function FindFirstFile& Lib "kernel32" Alias
"FindFirstFileA" (ByVal _
lpFileName As String, lpFindFileData As WIN32_FIND_DATA)

Declare Function FindClose& Lib "kernel32" (ByVal hFindFile As Long)

Private Const ERROR_NO_MORE_FILES = 18&
Private Const INVALID_HANDLE_VALUE = -1

Jun 15 '07 #2

John Mishefske

ManningFan wrote:

My code is thus:

sExe3 = sExe & "cmd.exe -s:" & sScrFile2

ShellWait sExe3, vbNormalFocus

All it does is open a CMD window and sit there, it's not processing
the text file I'm sending it like the FTP shell does.

Anyone know why? Can you fix my code? Any help is appreciated.

I think that this line:

sExe3 = sExe & "cmd.exe -s:" & sScrFile2

should be using sExe2 instead?

sExe3 = sExe2 & "cmd.exe -s:" & sScrFile2

--
'--------------------------
' John Mishefske
' UtterAccess Editor
' 2007 Microsoft Access MVP
'--------------------------

Jun 15 '07 #3

John Mishefske

John Mishefske wrote:

ManningFan wrote:

>My code is thus:

sExe3 = sExe & "cmd.exe -s:" & sScrFile2

ShellWait sExe3, vbNormalFocus

All it does is open a CMD window and sit there, it's not processing
the text file I'm sending it like the FTP shell does.

Anyone know why? Can you fix my code? Any help is appreciated.

I think that this line:

sExe3 = sExe & "cmd.exe -s:" & sScrFile2

should be using sExe2 instead?

sExe3 = sExe2 & "cmd.exe -s:" & sScrFile2

I should have read your post more carefully but your move command is
probably expanding to something like this:
(assuming) ComSpec=C:\WINDOWS\system32\cmd.exe and "test.txt" as sScrFile2

The code line:

sExe3 = sExe & "cmd.exe -s:" & sScrFile2

expands to:

cmd.execmd.exe -s: filename

So... you can see there are several issues here:
- cmd.exe provided twice
- there is no -s: would be an invalid cmd option

You might want to try:
sExe & " move " & sScrFile2 & " " & sDestination

or better yet, use the Name statement in VBA to move your file.
--
'--------------------------
' John Mishefske
' UtterAccess Editor
' 2007 Microsoft Access MVP
'--------------------------

Jun 15 '07 #4