Move command using Shell in Access VBA

[joseph micheli]

I am trying to execute a move command in VBA for Access using the Shell
function. The code I'm using is as follows:

x = Shell("""MOVE ""P:\Field Audits\FAT\CompletedAudits\*.xls"" ""P:\Field
Audits\FAT\UploadedAudits\""")

The move command works fine when included in a .bat file and called from the
VBA module with the Shell function. However, I would like to be able to move
files one at a time instead enmass via the batch file.

The paths are good and there is a file to be moved. Any suggestions would
be greatly appreciated.

Thanks.

 

[Douglas J. Steele]

Why not use the VBA Name statement?

Something along the lines of:

Dim strDestFolder As String
Dim strFile As String
Dim strSrceFolder As String

strSrceFolder = "P:\Field Audits\FAT\CompletedAudits\"
strDestFolder = "P:\Field Audits\FAT\UploadedAudits\"

strFile = Dir(strSrceFolder & "*.xls")
Do While Len(strFile) > 0
Name strSrceFolder & strFile As strDestFolder & strFile
strFile = Dir
Loop

 

[Guest]

Move is not a program, so you can't shell to it.

Move is a command to the command program (cmd or command)
You can issue commands like this by using the command as a
command line parameter to the command program (cmd or command)

However, for this example, you want to use the Access
FileSystemObject or the VBA Name command.

(david)

 

[Van T. Dinh]

Just to expand David's explanation a bit, you need the full path to cmd.exe.
Here is how I would use the DOS "MOVE" (I don't normally use the return of
Shell so I don't use "x = "):

Shell "C:\Windows\System32\cmd.exe /C MOVE ""P:\Field
Audits\FAT\CompletedAudits\*.xls"" ""P:\Field Audits\FAT\UploadedAudits\""",
vbHide

You may need to check the full path to cmd.exe on your system.

OTOH, Douglas' method is the one I use in VBA.

 

[Douglas J. Steele]

If the user is running an older operating system, it may not be
C:\Windows\System32\cmd.exe. There's an Environment Variable (ComSpec) that
returns what to use:

Shell Environ("ComSpec") & " /C MOVE " & _
"""P:\Field Audits\FAT\CompletedAudits\*.xls"" " & _
"""P:\Field Audits\FAT\UploadedAudits\""", vbHide

 

[Robert Morley]

Just a side-question here...I notice in your comparison, you're using
{Len(strFile) > 0}. Is there an advantage to this over {strFile <>
vbNullString} (or {strFile <> ""}), or is it strictly a matter of coding
style? Just curious.


Thanks,
Rob

 

[Douglas J. Steele]

Len(strFile) > 0 is supposed to be marginally faster than strFile <>
vbNullString, which is supposed to be marginally faster than strFile <> "".
In practice, I doubt anyone would notice any difference, even in a large
loop.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Just a side-question here...I notice in your comparison, you're using
{Len(strFile) > 0}. Is there an advantage to this over {strFile <>
vbNullString} (or {strFile <> ""}), or is it strictly a matter of coding
style? Just curious.


Thanks,
Rob

 

[Robert Morley]

Thanks, Doug. I figured that might be the case, but didn't have Access or
VB6 handy to test it at the time.



Rob

Len(strFile) > 0 is supposed to be marginally faster than strFile <>
vbNullString, which is supposed to be marginally faster than strFile <>
"". In practice, I doubt anyone would notice any difference, even in a
large loop.