Aplicando a sintaxe Powershell diretamente no VBA

Question

Estou trabalhando para remover os retornos de carro (mas não os avanços de linha) de um .csv automaticamente com VBA. Descobri uma sintaxe do Powershell que resolverá o problema, mas preciso incorporá-la ao VBA para que haja apenas um arquivo que eu possa compartilhar com os colegas. Também preciso que a sintaxe do Powershell seja direcionada ao caminho do arquivo usado pelo restante da macro. Aqui está a última parte da macro que escrevi e que precisa dos toques finais aplicados:

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

Em seguida, preciso que o script Powershell a seguir seja executado (também preciso que os caminhos de arquivo no Powershell sejam atualizados na variável Final_path do VBA). Para ficar claro, preciso que C: \ Users \ Desktop \ CopyTest3.csv e. \ DataOutput_2.csv sejam atualizados para serem iguais à variável Final_path do VBA. A seguinte sintaxe do Powershell limpa os retornos de carro (\ r) de um arquivo .csv enquanto deixa os avanços de linha (\ n).

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

Agradeço antecipadamente por sua ajuda!

Answer 1

Esta é uma maneira de alterar os separadores de linha em um arquivo de texto usando FSO para ler o arquivo e escrever uma versão modificada com o final de linha fornecido:

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