Save as File Using Visual Basic Excel Macro
Post date: Feb 1, 2011 5:28:24 PM
Private Sub CommandButton1_Click()
Application.DisplayAlerts = False
' Save file name and path into a variable
template_file = ActiveWorkbook.FullName
' Default directory would be c:\temp. Users however will have the ability to change where to save the file if need be.
' Notice that i'm only allowing the save as option to be of .txt format.
' I'm also attaching the current date to the file name.
fileSaveName = Application.GetSaveAsFilename( _
InitialFileName:="C:\temp\filename_" + VBA.Strings.Format(Now, "mmddyyyy") + ".txt", _
fileFilter:="Text Files (*.txt), *.txt")
If fileSaveName = False Then
Exit Sub
End If
' Save file as .txt TAB delimited
ActiveWorkbook.SaveAs Filename:= _
fileSaveName, FileFormat:=xlText, _
CreateBackup:=False
file_name_saved = ActiveWorkbook.FullName
MsgBox "Your Acct Rec upload file has been successfully created at: " & vbCr & vbCr & file_name_saved
' Go back to excel format after TAB delimited file has been created and saved
ActiveWorkbook.SaveAs Filename:= _
template_file, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
Application.DisplayAlerts = True
End Sub