help with vba

[BORUCH]

hi

i need help adding somwthinf to thios vba

sub SaveFile2222()
Dim ans As Long
Dim sSaveAsFilePath As String


mybook = ActiveWorkbook.Name
    On Error GoTo ErrHandler:
If Len(mybook) > 25 Then
mybook = Left(mybook, 25)
End If
    sSaveAsFilePath = "C:\Users\jack\Desktop\AAAA\" & Replace(mybook, ".xlsx", "") & ".txt"






    If Dir(sSaveAsFilePath) <> "" Then
        ans = MsgBox("File " & sSaveAsFilePath & " exists.  Overwrite?", vbYesNo + vbExclamation)
        If ans <> vbYes Then
            Exit Sub
        Else
            Kill sSaveAsFilePath
        End If
    End If
    ActiveWorkbook.SaveAs sSaveAsFilePath, xlTextWindows




My_Exit:
Exit Sub




ErrHandler:
MsgBox Err.Description
Resume My_Exit
End Sub

From my understating is, that if you get the MSG box asking if you want to override it, and you hit "NO" it doesn't override it

I would like that if you hit "no" the save as dialog box should open up and you should be able to change the name of the file

Also there should be a check that if you name the file and you attempt to save it and the file name is more then 25 charterers you should not be able to save it

It should also only allow you to save it as a .txt format

Any help is greatly appreciated

 

[BORUCH]

Any updates ?

 

[BORUCH]

Does anyone have an answer ?

 

[offthelip]

try something like this:

Sub SaveFile2222()
Dim ans As Long
Dim sSaveAsFilePath As String




mybook = ActiveWorkbook.Name
    On Error GoTo ErrHandler:
If Len(mybook) > 25 Then
mybook = Left(mybook, 25)
End If
    sSaveAsFilePath = "C:\Users\jack\Desktop\AAAA\" & Replace(mybook, ".xlsx", "") & ".txt"












    If Dir(sSaveAsFilePath) <> "" Then
        ans = MsgBox("File " & sSaveAsFilePath & " exists.  Overwrite?", vbYesNo + vbExclamation)
        If ans <> vbYes Then
         newnam = InputBox("Enter Name of file, 25 chars Max")
         sSaveAsFilePath = "C:\Users\jack\Desktop\AAAA\" & newnam & ".txt"
           
        Else
            Kill sSaveAsFilePath
        End If
    End If
    ActiveWorkbook.SaveAs sSaveAsFilePath, FileFormat:=xlTextWindows








My_Exit:
Exit Sub