VBA to Save CSV as Text

[kentdangk]

Hi, this is my first post thus I apologize if it is incomplete. Using VBA, I am able to open a CSV file in Notepad yet I am unable to save it as a .txt file. I attempted many other methods (not included here) thus please help. Thanks.

Please Login or Register  to view this content.

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

[6StringJazzer]

The reason that your code fails where shown is that VBA is a Microsoft Office environment that doesn't know anything about Notepad. It only knows about Excel, Word, Outlook, PowerPoint, Project, and Visio (and I might be missing one). There is no such thing as "Application.Notepad" built into VBA.

First, what is your goal? What format do you want the .txt file to be written in? A .csv file is simply a plain text file; the extension tells applications that they can interpret commas as field delimiters. Excel writes a .txt file by using tabs as delimiters, but Notepad does not. If you are in Notepad and write a .csv file as a .txt file, you will get exactly the same thing with a different name.

If you want a tab delimited file you should open the file in Excel, then save it as .txt, instead of using Notepad.

[MrShorty]

Or, if you are not interested in changing the delimiter, you could simply invoke the Window's "Rename" command and change the file extension from .csv to .txt. For the record, I don't know how to do this from VBA, but it should work.

I don't know batch programming either, but this could be something perhaps done in some batch file that would rename *.csv to *.txt or copy *.csv to *.txt or some such command. Again, this would leave the file unchanged (comma is still the delimiter). If you want to convert to a tab delimited file, you will need to use a text editor or excel to replace commas with tabs.

[kentdangk]

Thanks Jeff for adding code tags as well as your explanation. My goal output a customized text file involving pipes(|), etc. from a CSV that I created to feed into a system.

Thanks to MrShorty, my problem is solved and a solution to the code is:

Please Login or Register  to view this content.