Stosowanie składni Powershell bezpośrednio w języku VBA

Nov 20 2020

Pracuję nad usuwaniem powrotów karetki (ale nie wysuwów wiersza) z pliku .csv automatycznie za pomocą VBA. Wymyśliłem jakąś składnię Powershell, która załatwi sprawę, ale muszę ją osadzić w VBA, więc jest tylko jeden plik, który mogę udostępnić współpracownikom. Potrzebuję również, aby składnia Powershell była skierowana do ścieżki pliku używanej przez resztę makra. Oto ostatnia część napisanego przeze mnie makra, która wymaga ostatnich poprawek:

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

Następnie potrzebuję do uruchomienia następującego skryptu Powershell (potrzebuję również ścieżek plików w Powershell, aby zaktualizować zmienną Final_path z VBA). Żeby było jasne, potrzebuję aktualizacji C: \ Users \ Desktop \ CopyTest3.csv i. \ DataOutput_2.csv, tak aby była równa zmiennej Final_path z VBA. Poniższa składnia programu PowerShell usuwa znaki powrotu karetki (\ r) z pliku .csv, pozostawiając znaki wysuwu wiersza (\ n).

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

Z góry dziękuje za twoją pomoc!

Odpowiedzi

1 TimWilliams Nov 20 2020 at 01:06

Oto jeden ze sposobów zmiany separatorów linii w pliku tekstowym przy użyciu FSO do wczytania pliku i zapisania zmodyfikowanej wersji z podanym zakończeniem linii:

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