Anwenden der Powershell-Syntax direkt in VBA

Nov 20 2020

Ich arbeite daran, die Wagenrückläufe (aber nicht die Zeilenvorschübe) automatisch mit VBA von einer CSV-Datei zu entfernen. Ich habe eine Powershell-Syntax herausgefunden, die den Trick macht, aber ich muss sie in die VBA einbetten, damit ich nur eine Datei für Kollegen freigeben kann. Ich brauche auch die Powershell-Syntax, um auf den Dateipfad zu verweisen, der vom Rest des Makros verwendet wird. Hier ist der letzte Teil des Makros, den ich geschrieben habe und der den letzten Schliff benötigt:

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

Als nächstes muss das folgende Powershell-Skript ausgeführt werden (ich muss auch die Dateipfade in der Powershell aktualisieren, um die Variable Final_path von VBA zu aktualisieren). Um klar zu sein, müssen C: \ Users \ Desktop \ CopyTest3.csv und. \ DataOutput_2.csv aktualisiert werden, damit sie der Variablen Final_path von VBA entsprechen. Die folgende Powershell-Syntax löscht Wagenrückläufe (\ r) aus einer CSV-Datei, während die Zeilenvorschübe (\ n) verlassen werden.

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

Vielen Dank im Voraus für Ihre Hilfe!

Antworten

1 TimWilliams Nov 20 2020 at 01:06

Hier ist eine Möglichkeit, die Zeilentrennzeichen in einer Textdatei mithilfe von FSO zu ändern, um die Datei einzulesen und eine geänderte Version mit dem von Ihnen angegebenen Zeilenende zu schreiben:

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
BernardVoss Nov 20 2020 at 03:28
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