Excel VBA - Passing a variable path with spaces to

问题:

I have the code at the bottom of this post inside one of my excel books (my first time ever writing vba code). The goal here is to allow users to:

1. start a video encode using MXLight software with a temp file name
2. select a cell with the person currently on video
3. stop the video encode, rename the temp file, move it to a specific folder, upload it via FTP via WinSCP software, mark it green, move one cell down.

So during the event, you:

1. Press button 1 which is the Sub StartMXL
2. then you highlight your cell
3. Press button 2 which is the Sub StopAndProcess

My questions are the following:

1) First and foremost, the entire (stop and process) button doesn't work because the upload function fails, because I can't figure out how to get the winscp command to use the variable referenced... and not try to literally use that word. Check the code under the Sub Upload, and here is the log file when I try that:

1 . 2015-11-12 17:53:18.490 Connected
2 . 2015-11-12 17:53:18.490 Using FTP protocol.
3 . 2015-11-12 17:53:18.490 Doing startup conversation with host.
4 > 2015-11-12 17:53:18.491 PWD
5 < 2015-11-12 17:53:18.520 257 "/" is the current directory
6 . 2015-11-12 17:53:18.520 Getting current directory name.
7 . 2015-11-12 17:53:18.520 Startup conversation with host finished.
8 < 2015-11-12 17:53:18.520 Script: Active session: [1] [e-mail]
9 > 2015-11-12 17:53:18.520 Script: put RealFile
10. 2015-11-12 17:53:18.520 Copying 1 files/directories to remote directory "/"
11. 2015-11-12 17:53:18.520   PrTime: Yes; PrRO: No; Rght: rw-r--r--; PrR: No (No); FnCs: N; RIC: 0100; Resume: S (102400); CalcS: No; Mask: 
12. 2015-11-12 17:53:18.520   TM: B; ClAr: No; RemEOF: No; RemBOM: No; CPS: 0; NewerOnly: No; InclM: ; ResumeL: 0
13. 2015-11-12 17:53:18.520   AscM: *.*html; *.htm; *.txt; *.php; *.php3; *.cgi; *.c; *.cpp; *.h; *.pas; *.bas; *.tex; *.pl; *.js; .htaccess; *.xtml; *.css; *.cfg; *.ini; *.sh; *.xml
14* 2015-11-12 17:53:18.520 (EOSError) System Error.  Code: 2.
15* 2015-11-12 17:53:18.520 The system cannot find the file specified

You can see on line 9 it's trying to literally upload the file called "RealFile" instead of using the contents of the variable with file name and folder structure. That variable is working in other parts of the code, such as when I'm renaming and moving it.

Any idea there?

Here is the total code for the whole thing:

Public Sub StartMXL()
    Dim MXLapp As String
    MXLapp = "C:\1a7j42w\MXLight-2-4-0\MXLight.exe"
    Shell (MXLapp & " record=on"), vbNormalNoFocus
    AppActivate Application.Caption
End Sub
---
Public Sub StopMXL()
    Dim MXLapp As String
    MXLapp = "C:\1a7j42w\MXLight-2-4-0\MXLight.exe"
    Shell (MXLapp & " record=off"), vbNormalNoFocus
    AppActivate Application.Caption
End Sub
---
Sub ChooseRootDir()
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Please choose a folder"
        .AllowMultiSelect = False
        If .Show = -1 Then Sheets("rawdata").Range("I1").Value = .SelectedItems(1)
    End With
End Sub
---
Public Sub RenameAndMove()
    Dim TempFile As String
    Dim RealFile As String

    If Len(Dir(Sheets("rawdata").Range("I1").Value & "\" & Sheets("rawdata").Range("J1").Value, vbDirectory)) = 0 Then
        MkDir Sheets("rawdata").Range("I1").Value & "\" & Sheets("rawdata").Range("J1").Value
    End If
        If Len(Dir(Sheets("rawdata").Range("I1").Value & "\" & Sheets("rawdata").Range("J1").Value & "\" & Sheets("rawdata").Range("K1").Value, vbDirectory)) = 0 Then
        MkDir Sheets("rawdata").Range("I1").Value & "\" & Sheets("rawdata").Range("J1").Value & "\" & Sheets("rawdata").Range("K1").Value
    End If
        If Len(Dir(Sheets("rawdata").Range("I1").Value & "\" & Sheets("rawdata").Range("J1").Value & "\" & Sheets("rawdata").Range("K1").Value & "\" & Sheets("rawdata").Range("L1").Value, vbDirectory)) = 0 Then
        MkDir Sheets("rawdata").Range("I1").Value & "\" & Sheets("rawdata").Range("J1").Value & "\" & Sheets("rawdata").Range("K1").Value & "\" & Sheets("rawdata").Range("L1").Value
    End If

    TempFile = Sheets("rawdata").Range("I1").Value & "\tempfile\spiderman.TS"
    RealFile = Sheets("rawdata").Range("I1").Value & "\" & Sheets("rawdata").Range("J1").Value & "\" & Sheets("rawdata").Range("K1").Value & "\" & Sheets("rawdata").Range("L1").Value & "\" & ActiveCell.Value & ".TS"

    Name TempFile As RealFile
End Sub
---
Public Sub Upload()
    Dim RealFile As String
    Dim TempFile As String

    RealFile = Sheets("rawdata").Range("I1").Value & "\" & Sheets("rawdata").Range("J1").Value & "\" & Sheets("rawdata").Range("K1").Value & "\" & Sheets("rawdata").Range("L1").Value & "\" & ActiveCell.Value & ".TS"
    TempFile = "C:\1a7j42w\MXLight-2-4-0\recordings\tempfile\spiderman.TS"

    Call Shell( _
    "C:\1a7j42w\WinSCP\WinSCP.com /log=C:\1a7j42w\WinSCP\excel.log /command " & _
    """open ftp://ftp1934501:[e-mail]/"" " & _
    """put RealFile"" " & _
    """exit""")
End Sub
---
Sub StopAndProcess()
    Call StopMXL
    Call RenameAndMove
    Call Upload
    Selection.Interior.ColorIndex = 4
    ActiveCell.Offset(1, 0).Select
End Sub

回答1:

In WinSCP script, you want:

put "path with space"

See Command parameters with spaces.

---

On WinSCP command line, you have to enclose each command to a double quotes and double all double quotes in the command itself:

"put ""path with space"""

See WinSCP command-line syntax.

---

In VB you need to enclose the string in double quotes and double all double quotes in the string itself:

"""put """"path with space"""""" "

And to replace the path with a variable, substitute the path with space with " & RealFile & ".

This gives you:

"""put """"" & RealFile & """"""" "