VBA Shell strips quotes from PowerShell command line

Question

I'm trying to convert windows CRLF newline to UNIX LF newline with a POWERSHELL command. I would like a VBA macro to do that.

So far my code is:

Sub conversiontoUNIX()    
    Dim wsh As Object
    Set wsh = VBA.CreateObject("WScript.Shell")
    Dim waitOnReturn As Boolean: waitOnReturn = True
    Dim windowStyle As Integer: windowStyle = 1
    Dim file As String, listfichiers As Variant, outputfolder As String, replace As String, command_shell As Variant
    outputfolder = Environ("USERPROFILE") & "\Downloads\" & "run_param_" & Format(Date, "dd_mm_yy")
    listfichiers = listfiles(outputfolder)
    
    For filx = LBound(listfichiers) To UBound(listfichiers)
    file = outputfolder & "\" & listfichiers(filx)
    Requst = "PowerShell -noexit -Command (Get-Content " & file & ") -join ""`n"" > " & file & ""

    command_shell = Shell(Requst, vbNormalFocus)
    Next filx
End Sub

I run into an error:

How can i manage to run my code ?

Best regards,

Jouvzer

check your command first. Do a `Debug.Print Requst` get the result from the immediate window. Open up a command shell in Windows, copy the command from the immediate window there and run it. Does it work or error? — Pᴇʜ, Apr 08 '21 at 14:59

score 1 · Answer 1 · answered Apr 08 '21 at 16:39

1

The error message indicates that the "" was removed and the backtick-newline character is not understood. Try adding another pair of double quotes around the `n

answered Apr 08 '21 at 16:39

Rich Moss

2,195
1
13
18

score 1 · Accepted Answer · answered Apr 08 '21 at 17:06

1

You need backslashes to preserve quotes around `n in command line:

Requst = "PowerShell -noexit -Command (Get-Content " & file & _
      ") -join \""`n\"" > " & file & ""

answered Apr 08 '21 at 17:06

artnib

480
4
7

Seems you have to escape double-quotes with [extra double-quotes](https://stackoverflow.com/questions/9024724/how-do-i-put-double-quotes-in-a-string-in-vba) in VBA, not with backslashes. Am I missing something about how the quotes are interpreted by Shell()? – Rich Moss Apr 08 '21 at 23:45
1

@Rich Moss [A double quote mark preceded by a backslash (\") is interpreted as a literal double quote mark (")](https://learn.microsoft.com/en-us/cpp/c-language/parsing-c-command-line-arguments?redirectedfrom=MSDN&view=msvc-160). [Everyone quotes command line arguments the wrong way](https://learn.microsoft.com/en-us/archive/blogs/twistylittlepassagesallalike/everyone-quotes-command-line-arguments-the-wrong-way) – artnib Apr 09 '21 at 03:59

VBA Shell strips quotes from PowerShell command line

2 Answers2