help with vba

BORUCH

Active Member
Joined
Mar 1, 2016
Messages
301
hi

i need help adding somwthinf to thios vba

Code:
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
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,451
Office Version
  1. 2010
Platform
  1. Windows
try something like this:
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,954
Messages
5,525,880
Members
409,669
Latest member
JDCupps

This Week's Hot Topics

Top