Excel VBA - 将带有空格的变量路径传递给 WinSCP 命令行 [英] Excel VBA - Passing a variable path with spaces to WinSCP command-line

问题描述

我的一本 Excel 书籍中有这篇文章底部的代码(我第一次编写 vba 代码).这里的目标是允许用户:

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. 使用带有临时文件名的 MXLight 软件开始视频编码
2. 选择当前视频中人物所在的单元格
3. 停止视频编码，重命名临时文件，将其移动到特定文件夹，通过WinSCP软件通过FTP上传，标记为绿色，移动一个单元格下来.

所以在活动期间，您:

1. 按下按钮 1，即 Sub StartMXL
2. 然后突出显示您的单元格
3. 按下按钮 2，即 Sub StopAndProcess

我的问题如下:

1)首先，整个(停止和处理)按钮不起作用，因为上传功能失败，因为我不知道如何让 winscp 命令使用引用的变量......而不是尝试从字面上使用这个词.检查子上传下的代码，这是我尝试时的日志文件:

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

您可以在第 9 行看到它正在尝试逐字上传名为RealFile"的文件，而不是使用具有文件名和文件夹结构的变量的内容.该变量在代码的其他部分起作用，例如当我重命名和移动它时.

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.

有什么想法吗?

这是整个事情的总代码:

Here is the total code for the whole thing:

Public Sub StartMXL()
    Dim MXLapp As String
    MXLapp = "C:1a7j42wMXLight-2-4-0MXLight.exe"
    Shell (MXLapp & " record=on"), vbNormalNoFocus
    AppActivate Application.Caption
End Sub
---
Public Sub StopMXL()
    Dim MXLapp As String
    MXLapp = "C:1a7j42wMXLight-2-4-0MXLight.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 & "	empfilespiderman.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:1a7j42wMXLight-2-4-0
ecordings	empfilespiderman.TS"

    Call Shell( _
    "C:1a7j42wWinSCPWinSCP.com /log=C:1a7j42wWinSCPexcel.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

推荐答案

在 WinSCP 脚本中，你想要:

In WinSCP script, you want:

put "path with space"

请参阅带空格的命令参数.

在 WinSCP 命令行中，您必须将每个命令用双引号括起来，并将命令本身中的所有双引号括起来:

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"""

请参阅 WinSCP 命令行语法.

在 VB 中，您需要将字符串括在双引号中，并将字符串本身中的所有双引号括起来:

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

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

若要将路径替换为变量，请将 path 替换为空格 替换为 " & RealFile & ".

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

这给了你:

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

这篇关于Excel VBA - 将带有空格的变量路径传递给 WinSCP 命令行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！