Script/macro to save a copy as tab delimited text file and then close original file

**Merlin803** · 10-02-2015, 09:37 AM

Hello:

I need a VB Code to save a copy of opened sheet 1 as a tab delimited text file (but not overwrite the original), to a specific path.

I think this is close, but there are two issues, the resulting text file is basically jibberish and I would like the original Excel sheet to just close after this is ran, not saved.

Sub SaveExcelToTabDelimitedWithoutQuotes()
Dim sFilename As String, Path As String
sFilename = "filename_" & Format(Date, "yyyy-mm-dd") & ".txt"
Path = "S:\Report_FTP"
Workbooks.Add
'Saving the Workbook
ActiveWorkbook.SaveAs Path & "\" & sFilename

End Sub

Please let me know if you have any questions.
Thanks.

**Kenneth Hobson** · 10-02-2015, 11:20 AM

Welcome to the Forum! Please paste code between code tags. Click the # icon in a reply toolbar to insert them.

Use the Sheet's SaveAs method rather than the Workbook's SaveAs. Set the fileformat to xlTextWindows. e.g.

Please Login or Register  to view this content.

**Merlin803** · 10-02-2015, 11:46 AM

Thank you. I gave the following a try but the saved .txt file still contains "jibberish" that is unreadable. On the other hand, if I manually choose to save the sheet as a .txt file, the contents in the saved file are readable.

Please Login or Register  to view this content.

**Kenneth Hobson** · 10-02-2015, 11:59 AM

IF you record the macro you will see what you left off.

, xlTextWindows

do

Please Login or Register  to view this content.

**Merlin803** · 10-02-2015, 01:34 PM

Doh! Thanks!!

Script/macro to save a copy as tab delimited text file and then close original file

LinkBack

Thread Tools

Rate This Thread

Display

Script/macro to save a copy as tab delimited text file and then close original file

Re: Script/macro to save a copy as tab delimited text file and then close original file

Re: Script/macro to save a copy as tab delimited text file and then close original file

Re: Script/macro to save a copy as tab delimited text file and then close original file

Re: Script/macro to save a copy as tab delimited text file and then close original file

Thread Information

Users Browsing this Thread

Similar Threads

Macro to Save As a Copy of the File, Save the Original File, & Overwrite Previous Versions

vb code to save from xls to pipe delimited txt without quotes with original file name

Copy an open file and close the original

Save CSV file as Pipe delimited text file

[SOLVED] How can I save a file as a comma-delimited text file in Excel?

Bookmarks

Bookmarks

Posting Permissions