Save .CSV file as .txt without opening

[QuantBurner]

Hi!
I download .csv files with CUSIPs. The cusips are nine characters and sometimes have leading zeroes or are sometimes a combination of numbers and "e". If I open the file, Excel drops the leading zeroes and interprets the e as scientific notation. However, if I open the .CSV file in word and save it as a .TXT file, I can then specify that the column with CUSIPS is text when I open it in Excel. Hence, why I'd like to save the .CSV file as a .TXT file without opening it using some VBA code.
Thanks!

 

[VBA Geek]

you can import a CSV from the DATA tab, and then choose TEXT format for the CUSIP column

then on the 3rd step, choose text for the CUSIP column

 

[QuantBurner]

What version of Excel is that?
I got the VBA code I needed to do your operation via the "record macro." It works like a champ.
Thanks!!!

 

[QuantBurner]

Well... I thought it would be easy to take the macro I recorded to get the VBA for the query and then add some VBA code before the query creation to have the user browse to select the file. But that's not working. I get "application-defined or object-defined" error. I would have thought I could capture the file path as a string via msoFileDialogOpen and plug that string into the activeworkbook.Queries.add function and OLEDB;Provider......
Suggestions?

 

[SpillerBD]

you can import a CSV from the DATA tab, and then choose TEXT format for the CUSIP column
then on the 3rd step, choose text for the CUSIP column

Since Excel 1995 or earlier.
Excel 2016 provides a better tool for data imports, see Get & Transform.

 

[QuantBurner]

I monkeyed with the VBA a bit and am now getting a "The import strCSVTest matches no exports. Did you miss a module reference?" Error.