saveas VBA

[MR3]

Sub saveas()
fileSaveName = Application.GetSaveAsFilename( _
    fileFilter:="Text Files (*.txt), *.txt")
If fileSaveName <> False Then
    MsgBox "Save as " & fileSaveName
End If
End Sub [\code]

when i run this code it seems to work but when i go to the directory where the new file is supposed to be save the file does not exist.

 

[VoG]

You have to save the file! Maybe

Sub saveMeas()
filesavename = Application.GetSaveAsFilename( _
fileFilter:="Text Files (*.txt), *.txt")
If filesavename <> False Then
MsgBox "Save as " & filesavename
ActiveWorkbook.saveas Filename:=filesavename, FileFormat:=xlTextWindows
End If
End Sub

 

[MR3]

which argument would date/timestamp go in for the saveas in the file name?

 

[VoG]

Do you mean that you want to append the date and time to the filename that GetSaveAsFileName returns?

 

[MR3]

Yep that is correct so that every time i run the macro, a new file name with new timestamp will be generated. This will ensure organization of hundreds of files.

 

[VoG]

Try like this

Sub SaveMeAs()
Dim FileSaveName As Variant
FileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Text Files (*.txt), *.txt")
If FileSaveName <> False Then
    FileSaveName = Left(FileSaveName, Len(FileSaveName) - 3) & Format(Now, "mm-dd-yyyy hh:mm:ss") & "txt"
    MsgBox "Save as " & FileSaveName
    ActiveWorkbook.saveas Filename:=FileSaveName, FileFormat:=xlTextWindows
End If
End Sub

 

[VoG]

Correction

Sub SaveMeAs()
Dim FileSaveName As Variant
FileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Text Files (*.txt), *.txt")
If FileSaveName <> False Then
    FileSaveName = Left(FileSaveName, Len(FileSaveName) - 4) & Format(Now, "mm-dd-yyyy hh:mm:ss") & ".txt"
    MsgBox "Save as " & FileSaveName
    ActiveWorkbook.saveas Filename:=FileSaveName, FileFormat:=xlTextWindows
End If
End Sub

 

[MR3]

I keep getting this error.... and it highlights the line ActiveWorkbooks

ActiveWorkbook.SaveAs Filename:=FileSaveName, FileFormat:=xlTextWindows

 

[VoG]

What does the MsgBox say the file save name is?

 

[VoG]

You could also try

Sub SaveMeAs()
Dim FileSaveName As Variant
FileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Text Files (*.txt), *.txt")
If FileSaveName <> False Then
    FileSaveName = Left(FileSaveName, Len(FileSaveName) - 4) & Format(Now, "mm-dd-yyyy hh:mm:ss") & ".txt"
    MsgBox "Save as " & FileSaveName
    ActiveWorkbook.SaveAs Filename:=FileSaveName, FileFormat:=xlText
End If
End Sub