Results 1 to 2 of 2

Thread: Call to Shell

  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am having a challange with the SHELL statement in VBA. I want pass three variables to the line of code but am unsuccessful. The issue is with passing the file name to the line of code.

    [codebox]Sub MoveProcessedFiles()
    Set objShell = CreateObject("Wscript.Shell")

    strFileName = "MyFile.xls"

    objSource = Left(Range("R_Path"), Len(Range("R_Path")) - 1)
    objDestination = "D:\Test\Processed"

    'Move individual file
    objCommand = "RoboCopy.Exe " & Chr(34) & objSource & " " & strFileName & Chr(34) & _
    " " & Chr(34) & objDestination & Chr(34) & " /MOV"

    'Move all files [This works for all files; note only two variables pass through]
    'objCommand = "RoboCopy.Exe " & Chr(34) & objSource & Chr(34) & _
    " " & Chr(34) & objDestination & Chr(34) & " /MOV"

    objShell.Run (objCommand)

    MsgBox "Done"
    End Sub
    [/codebox]


    Thanks for your assistance,
    John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    - Why do you use WScript.Shell? VBA has a built-in Shell command.

    - You create two command strings but you run only the second one; the first one is unused.

    - Shouldn't you have a "\" between the path and filename instead of a " "?

    - The prefix obj in the variable names objSource, objDestination and objCommand are unfortunate - they are strings, not objects.

    Try

    Shell "RoboCopy.Exe " & Chr(34) & objSource & "\" & strFileName & Chr(34) & _
    " " & Chr(34) & objDestination & Chr(34) & " /MOV"

    Shell "RoboCopy.Exe " & Chr(34) & objSource & Chr(34) & _
    " " & Chr(34) & objDestination & Chr(34) & " /MOV"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •