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:
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:
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:
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!
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 IfThat 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 IfThe .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:=TrueThe 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!
