save active sheet as text

[BORUCH]

Hi

i would like an excel vba that does the following

#1 it would save the active sheet as a text (tab delimited) file

#2 it would also make sure that the file name is not more then 30 characters, if it is more then 30 charterers ,it should trim the file name starting from the right side to get it to be 30 characters

#3 it would save the text file it to a specific directory, and if the same file name exits in that directory, it should not overwrite it, or make a copy, it should give you an error message saying file name already exits and do nothing.

Thanks

 

[BORUCH]

any updates to my question ?

 

[Michael M]

HAve a look at this thread Post #6

https://www.mrexcel.com/forum/excel-questions/664194-vba-save-specific-sheet-work-book-txt-file.html


If it is something you can work with, we may be able to modify it further if required

 

[BORUCH]

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


    On Error GoTo ErrHandler:
    
    sSaveAsFilePath = "C:\Users\BORUCH\Documents\BORUCB\test.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
    ActiveSheet.Copy '//Copy sheet 7 to new workbook
    ActiveWorkbook.SaveAs sSaveAsFilePath, xlTextWindows '//Save as text (tab delimited) file
    If ActiveWorkbook.Name <> ThisWorkbook.Name Then '//Double sure we don't close this workbook
        ActiveWorkbook.Close False
    End If


My_Exit:
Exit Sub


ErrHandler:
MsgBox Err.Description
Resume My_Exit
End Sub

HI

So this code does do someting that i asked for but not everything

#1 this code copies the active sheet to a new sheet i don't need that, the data that i want saved as a text file format is on its separate sheet already

#2 i need to be able to trim the file name for it to be no more then 30 characters if its less it leaves the file name how it is, if its more it trims it.

#3 the file name should be whatever the file name originally is not test...

 

[BORUCH]

HI

Can someone please help me with my request please ?

 

[Yongle]

You are being rather impatient
- we are not all on the same timezone as you
- it is not unreasonable to wait 24 hours before bumping

 

[Michael M]

Sorry....I have to sleep sometime...
Try this, UNTESTED

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

mybook = ActiveWorkbook.Name
    On Error GoTo ErrHandler:
If Len(mybook) > 30 Then
mybook = Left(mybook, 30)
End If
    sSaveAsFilePath = "C:\Users\BORUCH\Documents\BORUCB\" & mybook & ".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 '//Save as text (tab delimited) file
    If mybook.Name <> ActiveWorkbook.Name Then '//Double sure we don't close this workbook
        mybook.Close False
    End If


My_Exit:
Exit Sub


ErrHandler:
MsgBox Err.Description
Resume My_Exit
End Sub

 

[BORUCH]

HI

Thanks for answering

there is a couple of errors here first of all it doesn't have the error handler

#2 it saves the file name as .xlsx and .txt

#3 it doesn't ask you if it should override it or not

If you can get those fixed i would greatly appreciate it

Thanks

 

[Michael M]

#1 there is a couple of errors here first of all it doesn't have the error handler .....Error handler checks to see if file already exists see code in red

#2 it saves the file name as .xlsx and .txt ....I'm assuming the file existed as an .xlsx in the first place, when file is saved in a different format ie, (.txt) the original file will still exist

#3 it doesn't ask you if it should override it or not ....It does but only if the the file already exists

[color=red]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[/color]