VBA - Save As Text (Tab Delimited)

bvbull200

New Member
Joined
Jul 29, 2013
Messages
23
I have inherited a workbook that is in need of some maintenance. There is one area where I'm a little stumped.

At the end of the macro I am working on, I need it to save a single sheet as a Text (Tab Delimited) file.

The macro that I inherited contains this:

Code:
fname = Application.GetSaveAsFilename(fileFilter:="Excel Files (*.xlsx), *.xlsx", Title:="Save As")    
    If fname <> False Then
        wbDMTOutput.SaveAs (fname)
    End If
That pops up a "Save As" dialogue box, but forces the file to be saved as a .xlsx. Since I now want it to be a .txt file, I originally modified it to this:

Code:
fname = Application.GetSaveAsFilename(fileFilter:="Text Files (*.txt), *.txt", Title:="Save As")
    
    If fname <> False Then
        wbDMTOutput.SaveAs (fname)
    End If
The .txt that it saves it as, however, is really screwy. Weird characters abound and it isn't formatted the same as if I were to manually open the workbook, click Save As, and choose Text (Tab Delimited) and save the file myself. I don't know why that is, but it appears to be trying to save a whole workbook instead of a sheet?

To see if I could get an output that at least worked, I did this code:

Code:
 BaseFolder = "C:\Users\p_bryan\Documents\"
      Filename = "TestTabDelimitedVBA" 'File name with no Extension.
      Extn = ".txt"
    ActiveWorkbook.SaveAs Filename:=BaseFolder & Filename & Extn, _
    FileFormat:=xlText, CreateBackup:=True
The file that it output with that code is perfect. Formatted correctly, no strange characters, etc.

The problem is that it can't be used in a workbook that will be accessed by multiple users since the file path is different for everyone.

What I'm graciously requesting help with is to have a prompt like the first code provides that allows the user to select where the file will be saved, but have it save in the xlText format that I utilized in the third code.

This would let every user put the file where they want and ensure that the saved file is in the proper format.

I hope this makes sense.

I thank you for your consideration of my dilemma and appreciate all assistance!
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,496
Office Version
365
Platform
Windows
Cross posted https://www.excelforum.com/excel-programming-vba-macros/1226698-save-as-text-tab-delimited.html

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 

bvbull200

New Member
Joined
Jul 29, 2013
Messages
23
Cross posted https://www.excelforum.com/excel-programming-vba-macros/1226698-save-as-text-tab-delimited.html

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

My apologies. I posted here first, there second.

Did not meant to offend.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,262
Messages
5,443,403
Members
405,234
Latest member
AA90

This Week's Hot Topics

Top