VBA - Open a .csv in Notepad

[sanantonio]

Hi All,

Could I please get some help?

In order to upload data in SAP I export data from another source, run it through my formatting tool and produce a .csv file. Unfortunately the concat I require to make the data readable in SAP includes a comma so the .csv will always save with "

Of course the "s aren't visible when the .csv is opened in Excel, only in Notepad. But if you try and upload to SAP without opening notepad, doing a ctrl+h and getting rid of all the "s.

In an ideal word I'd like a code that opens it in notepad, removes the "s for me and saves it again. But I'm not greedy. I'd settle for just a code that opens the .csv in Notepad for me?

I try...

Sub WHATEVER()
    CreateObject("Shell.Application").Open ("filepath\20221215.csv")
End Sub

But of course this just opens the .csv in excel.

Could any of you fine folks help me out?

 

[rlv01]

Try this:

Shell "notepad.exe filepath\20221215.csv", vbNormalFocus

Of course the "filepath\20221215.csv" part needs to be an actual valid file path.

 

[sanantonio]

That's amazing it works thank you!
One last test though I need the file name to reference a cell. But it doesn't seem to work when I do that?

Sub test()
Shell "notepad.exe L:\filepath\" & "Sheet5.Range("H1") & ".csv", vbNormalFocus

End Sub

Am I missing something here? It just flashes read when I try this?

 

[Domenic]

You have an extra double quote. It should be . . .

Shell "notepad.exe L:\filepath\" & Sheet5.Range("H1").Value & ".csv", vbNormalFocus

You'll also notice that I have specified the Value property of the Range object for clarity.

Cheers!

 

[sanantonio]

Thanks both! Solved my problem!