VBA 내에서 직접 Powershell 구문 적용

Question

VBA를 사용하여 .csv에서 캐리지 리턴 (줄 바꿈 제외)을 자동으로 제거하는 작업을하고 있습니다. 트릭을 수행 할 몇 가지 Powershell 구문을 알아 냈지만 동료와 공유 할 수있는 파일이 하나만 있도록 VBA 내에 포함해야합니다. 또한 나머지 매크로에서 사용하는 파일 경로로 이동하려면 Powershell 구문이 필요합니다. 마지막으로 적용해야하는 매크로의 마지막 부분은 다음과 같습니다.

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

다음으로 다음 Powershell 스크립트를 실행해야합니다 (VBA에서 Final_path 변수를 업데이트하려면 Powershell의 파일 경로도 필요합니다). 명확하게하기 위해 C : \ Users \ Desktop \ CopyTest3.csv 및. \ DataOutput_2.csv를 VBA의 Final_path 변수와 동일하게 업데이트해야합니다. 다음 Powershell 구문은 줄 바꿈 (\ n)을 남겨두고 .csv 파일에서 캐리지 리턴 (\ r)을 지 웁니다.

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

도움에 미리 감사드립니다!

Answer 1

다음은 FSO를 사용하여 텍스트 파일의 줄 구분 기호를 변경하여 파일을 읽고 사용자가 제공 한 줄 끝으로 수정 된 버전을 작성하는 한 가지 방법입니다.

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