Applying Powershell syntax directly within VBA

Question

I'm working on stripping the carriage returns (but not the line feeds) from a .csv automatically with VBA. I figured out some Powershell syntax that will do the trick but I need to embed it within the VBA so there's just one file I can share with colleagues. I also need the Powershell syntax to be directed to the file path used by the rest of the macro. Here's the last part of the macro I've written that needs the final touches applied:

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

Next, I need the following Powershell script to be run (I also need the filepaths in the Powershell to be updated Final_path variable from VBA). To be clear, I need C:\Users\Desktop\CopyTest3.csv and .\DataOutput_2.csv to be updated to be equal to the Final_path variable from VBA. The following Powershell syntax clears carriage returns (\r) from a .csv file while leaving the line feeds (\n).

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

Thanks in advance for your help!

I've looked at this post but I don't know how to use FSO - I need to be able to send this to a colleague and it will just run without activating different types of settings. https://stackoverflow.com/questions/51085734/embed-powershell-script-in-vba-macro-and-run-it-from-there — Bernard Voss, Nov 19 '20 at 16:13
@CristianCapsuna I saw your response on the following post and I'm hoping you might be able to help me out here. I don't know how to use FSO... https://stackoverflow.com/a/51099720/14670271 — Bernard Voss, Nov 19 '20 at 16:31
@TimWilliams I saw your solution at this link https://stackoverflow.com/a/51099720/14670271 I'm trying to strip the carriage returns from a .csv (visible in notepad++). If you have any ideas or pointers they would be much appreciated! — Bernard Voss, Nov 19 '20 at 16:36
It looks like you are new here. Please read this blog post to learn how to avoid frustration; both your own and others: https://stackoverflow.com/help/how-to-ask — HackSlash, Nov 19 '20 at 16:45
P.S. Do not invoke the names of others and demand that they help you. That is not how this site works. This is not a forum. Some questions are better suited to a forum format. — HackSlash, Nov 19 '20 at 16:46
Does this answer your question? [how to run powershell script through excel VBA](https://stackoverflow.com/questions/51293191/how-to-run-powershell-script-through-excel-vba) — HackSlash, Nov 19 '20 at 16:48
Thanks @HackSlash, I didn't mean to ruffle feathers. I had seen the post on how to run powershell through excel VBA but this issue is different because I do not want the macro to access a separate .ps1 file (the macro should stand alone). I'm trying to contain the Powershell syntax within the VBA itself. I saw a response to another post on creating a .ps1 file and adding syntax to it through the VBA but I don't understand how to get it to work automatically. Does that make sense? I appreciate your time and thoughts! — Bernard Voss, Nov 19 '20 at 16:53
Why not just do everything in VBA or in Powershell, but not mix the two? Why your current approach, which doesn't seem optimal at all? — BigBen, Nov 19 '20 at 16:59
Are you trying to convert "windows" line separators to "unix style"? Always helps to explain why you're doing what you're doing. — Tim Williams, Nov 19 '20 at 17:14
@BigBen The rest of the macro is written in VBA (I only shared the final sub where I'm trying to save and remove carriage returns). I haven't been able to find a way to remove carriage returns from a .csv within Excel (note: I did review a post on how to remove carriage returns from individual cells in the spreadsheets but not from the raw data like you would see in notepad++). If you have a tip or reference on how to remove the carriage returns (CR or \r) from the text version of the file with VBA only I would appreciate that too. Thanks for your time and attention! — Bernard Voss, Nov 19 '20 at 17:22
@TimWilliams a quick google search suggests that is exactly what I'm trying to do - thank you! I'm trying to do that because someone above me said the CR's need to be removed from the notepad++ version of the .csv with a macro. — Bernard Voss, Nov 19 '20 at 17:25
Another important note: I need to retain leading zeros in fields within the .csv (these appear to be lost when saved as a .csv(UTF-8). — Bernard Voss, Nov 19 '20 at 17:35

score 1 · Accepted Answer · answered Nov 19 '20 at 18:06

1

Here's one way to change the line separators in a text file using FSO to read in the file and write out a modified version with the line-ending you supply:

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

answered Nov 19 '20 at 18:06

Tim Williams

154,628
8
97
125

Thanks @TimWilliams! I also received this solution from another forum: – Bernard Voss Nov 19 '20 at 20:26
1

FYI if you cross-post your question to other sites it's polite to mention that in your post here, so folks can check the status over there before spending time on an answer here. Not a complaint about this instance, just a suggestion moving forward. – Tim Williams Nov 19 '20 at 20:52
thank you for the tip and thanks again for your time on this. I appreciate it. I had actually directed others to this link but they responded directly to me instead of posting. In the future I will request that the conversation takes place here instead. Big thanks again! – Bernard Voss Nov 20 '20 at 16:16

score 0 · Answer 2 · answered Nov 19 '20 at 20: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

Applying Powershell syntax directly within VBA

2 Answers2