VBA save specific sheet in work book as .txt file

[Zibi]

Hi

I need to save Sheet7 in active workbook to folder "test" on my C: drive as .txt file and check if the file exists prior to overrite.


Please help.

 

[Gary's Student]

If the file already exists, do you want to over-write or quit??

 

[Zibi]

If the file already exists, do you want to over-write or quit??

Can there be popup if the file exists to informe?
with question over-write or do not over-write

at the end the file most likely will be over writen

 

[Zibi]

Can there be pop up if the file exists to inform?
with question over-write or do not over-write


Actually the file should not be over written if exists, but statment stating that the file exists with file location would be great.

thanks for all the help.

Zibi

 

[xenou]

Hi,
Windows will alert about overwrites so you only need to avoid the crash if the user says "no":

Sub Foo()

    On Error Resume Next
    Sheet7.SaveAs "C:\Users\Public\test.txt"
    On Error GoTo 0

End Sub

For the record, to force a save that overwrites:

Sub Foo()

    On Error Resume Next
    Kill "C:\Users\Public\test.txt"
    Sheet7.SaveAs "C:\Users\Public\test.txt"
    On Error GoTo 0

End Sub

 

[xenou]

^^ Lazy - won't work.

Try instead:

[COLOR="Navy"]Sub[/COLOR] SaveFile()
[COLOR="Navy"]Dim[/COLOR] ans [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] sSaveAsFilePath [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]

    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]GoTo[/COLOR] ErrHandler:
    
    sSaveAsFilePath = "C:\Users\Public\test.txt"

    [COLOR="Navy"]If[/COLOR] Dir(sSaveAsFilePath) <> "" [COLOR="Navy"]Then[/COLOR]
        ans = MsgBox("File " & sSaveAsFilePath & " exists.  Overwrite?", vbYesNo + vbExclamation)
        [COLOR="Navy"]If[/COLOR] ans <> vbYes [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
        [COLOR="Navy"]Else[/COLOR]
            Kill sSaveAsFilePath
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    Sheet7.Copy [COLOR="SeaGreen"]'//Copy sheet 7 to new workbook[/COLOR]
    ActiveWorkbook.SaveAs sSaveAsFilePath, xlTextWindows [COLOR="SeaGreen"]'//Save as text (tab delimited) file[/COLOR]
    [COLOR="Navy"]If[/COLOR] ActiveWorkbook.Name <> ThisWorkbook.Name [COLOR="Navy"]Then[/COLOR] [COLOR="SeaGreen"]'//Double sure we don't close this workbook[/COLOR]
        ActiveWorkbook.Close [COLOR="Navy"]False[/COLOR]
    [COLOR="Navy"]End If[/COLOR]

My_Exit:
[COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]

ErrHandler:
MsgBox Err.Description
[COLOR="Navy"]Resume[/COLOR] My_Exit
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Note that we error handle this since file saves can fail for various reasons (lack of privileges, file is open, disk errors).

 

[Zibi]

Work Perfect Thanks For All You help

 

[prateekdewangan]

Thank you really helpful

Can you let me know, how can I remove the "" double quotes from the created .txt file?