Aplicar la sintaxis de Powershell directamente dentro de VBA

Question

Estoy trabajando para eliminar los retornos de carro (pero no los avances de línea) de un .csv automáticamente con VBA. Descubrí una sintaxis de Powershell que funcionará, pero necesito incrustarla dentro del VBA para que solo haya un archivo que pueda compartir con mis colegas. También necesito que la sintaxis de Powershell se dirija a la ruta del archivo utilizada por el resto de la macro. Aquí está la última parte de la macro que he escrito que necesita que se apliquen los toques finales:

Private Sub Save()

' Save Macro
' Save as a .csv

Dim new_file As String
Dim Final_path As String

new_file = Left(Application.ActiveWorkbook.FullName, Len(Application.ActiveWorkbook.FullName) - 4) & ".csv"
    
    ActiveWorkbook.SaveAs Filename:= _
        new_file _
        , FileFormat:=xlCSV, CreateBackup:=False

Final_path = Application.ActiveWorkbook.FullName

'Below, I need to pass the Final_path file path over to powershell as a variable then
'strip the carraige returns from the file and resave as the same .csv

End Sub

A continuación, necesito que se ejecute el siguiente script de Powershell (también necesito que las rutas de archivo en Powershell se actualicen en la variable Final_path de VBA). Para ser claros, necesito que C: \ Users \ Desktop \ CopyTest3.csv y. \ DataOutput_2.csv se actualicen para que sean iguales a la variable Final_path de VBA. La siguiente sintaxis de Powershell borra los retornos de carro (\ r) de un archivo .csv mientras deja los avances de línea (\ n).

[STRING]$test = [io.file]::ReadAllText('C:\Users\Desktop\CopyTest3.csv') $test = $test -replace '[\r]','' $test | out-file .\DataOutput_2.csv

¡Gracias de antemano por tu ayuda!

Answer 1

Aquí hay una forma de cambiar los separadores de línea en un archivo de texto usando FSO para leer en el archivo y escribir una versión modificada con el final de línea que proporcione:

Sub tester()
    SetLineEndings "C:\Users\blah\Desktop\Temp\temp.csv", vbLf
End Sub

Sub SetLineEndings(fpath As String, sep As String)
    Dim fIn, fOut, ext
    With CreateObject("scripting.filesystemobject")
        ext = .getextensionname(fpath)
        Set fIn = .OpenTextFile(fpath)
        Set fOut = .CreateTextFile(Replace(fpath, "." & ext, "_mod." & ext), True)
        Do Until fIn.atendofstream
            fOut.write fIn.readline & sep
        Loop
        fIn.Close
        fOut.Close
    End With
End Sub

Answer 2

Public FSO As New FileSystemObject

Private Sub Save()
'
' Save Macro
' Save as a .csv
'
Dim new_file As String
Dim Final_path As String
Dim PS_text As String
Dim PS_run As Variant
new_file = Left(Application.ActiveWorkbook.FullName, Len(Application.ActiveWorkbook.FullName) - 4) & ".csv"
    
    ActiveWorkbook.SaveAs Filename:= _
        new_file _
        , FileFormat:=xlCSV, CreateBackup:=False

Final_path = new_file

Application.DisplayAlerts = False
Dim b
Set b = VBA.CreateObject("WScript.Shell")

'creating temporary shall script
Set a = FSO.CreateTextFile(ThisWorkbook.Path & "\temp.ps1")
'saving commands into the script
a.WriteLine ("[STRING]$test = [io.file]::ReadAllText('" & Final_path & "')") a.WriteLine ("$test = $test -replace '[\r]',''") a.WriteLine ("$test | out-file " & Final_path)
'executing the script
b.Run "powershell " & ThisWorkbook.Path & "\temp.ps1", vbNormalFocus
Application.DisplayAlerts = True

End Sub