VBA - Save As Text (Tab Delimited)

bvbull200

New Member
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!
 

Fluff

MrExcel MVP, Moderator
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
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.
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top