VBA to save as tab delimited file

[br_meyers]

Hi all,

I've seen this topic discussed, but not sufficient to answer my question. Your help is appreciated.

My goal is to export a worksheet to a new workbook, save the new workbook in a specific location with a specific file name. The next part is where I'm struggling. I then want to take the open workbook that has been saved and "save as" a tab delimited file in the same location with the same name as the recently saved excel file. For example, if the Excel file has been saved as "CRD_Upload_02_13_18_09_08", I want the tab delimited .txt file to also save in this location with this same file name. I need the VBA code to be dynamic (ie, I don't want to put the exact location and filename within the code). I want it to take the name and location of the current file and just save it as a tab delimited file.

The code I'm using is below and ignores the tab delimited .txt step.

Note below: "Y1" is the filepath: \\john\root\shared\modelo\Diversified\Calabrese\Central Port\

Sub Save_As()
Sheets("Trade Sheet Source").Select
Dim FP As String, dt As String, wbNam As String
FP = Range("Y1").Value
wbNam = "CRD_Upload_"
dt = Format(CStr(Now), "mm_dd_yy_hh_mm")
ActiveWorkbook.SaveAs filename:=FP & wbNam & dt


Rows("1:1").Select
Selection.Delete Shift:=xlUp
ActiveWorkbook.Save

End Sub

Thank you.

 

[br_meyers]

Bump

 

[edmitchell]

Hi

I think this does what you are after, and closes the TAB delimited file once saved, and I've commented out some of the latter statements which you may need for other purposes (so you may need to adapt further)?

Sub Save_As()
'Get file path
Path = ThisWorkbook.Path
'FullName = ThisWorkbook.FullName
'Get file Name
Fname = ThisWorkbook.Name
'remove extension based on location of ".", so could go wrong if file name contains a dot!
Just_name = Left(Fname, InStr(Fname, ".") - 1)


'turn off any prompts from file save
Application.DisplayAlerts = False


'Save as text / tab delimited
ActiveWorkbook.SaveAs Filename:=Path + "\" + Just_name + ".txt", FileFormat:=xlText


'turn prompts back on
Application.DisplayAlerts = True


'close our text file (as we have saved it!)
ActiveWorkbook.Close savechanges:=False


'Rows("1:1").Select
'Selection.Delete Shift:=xlUp
'ActiveWorkbook.Save


End Sub