MS Excel VB to export data to text file

Hi,

I am new vba and like to know if there is a way using excel macros or VB in Windows to:


1) create .txt file name using value or content of a cell say A1. It could be alphanumeric.
2) Then get excel data from range column B, where the rows count varies. Should get only data not empty rows of column B.
3) Save the text file on desktop, show message of exact location of file saved.
4) Now as its saved, if macro is run again with same text file name, should replace the previous file if its name is same.
5) The macro is run from another sheet in same workbook, not from the sheet which contains data. Say Macro is from sheet1 and data is in sheet2, the data contains quotes singe or double and pipe, which should not be affected on export.
6) And the clipboard should be cleared after export, as in this code.
We are using office 2010. Pleas suggest simple and easiest way.

Please suggest.

Hi..

Place a CommandButton on Sheet1 with the below code in it.

Tested by having filename in A1 on Sheet2 and the data on Sheet2 Column B (Row 1 down)..

Change the Filepath (code in Red) to suit.

Private Sub CommandButton1_Click()
    Dim fName As String, fPath As String, myArea
    fName = Sheets("Sheet2").Range("A1").Value
    fPath = "C:\Users\apo\Desktop\"
    With CreateObject("Scripting.FileSystemObject").CreateTextFile(fPath & fName & ".txt", True)
        For Each myArea In Sheets("Sheet2").Range("B1:B" & Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Row).SpecialCells(2).Areas
            .writeline Join(Application.Transpose(myArea), vbCrLf)
        Next myArea
    End With
    MsgBox "File Saved to: " & fPath
End Sub

@apo I later thought of having a folder on desktop of users. So, whatever the users desktop location may be, should create folder and save the text file there. I tried this using ur code.

Private Sub CommandButton1_Click()
    Dim strDir As String, fName As String, fPath As String, myArea
    strDir = "C:\Users\" & Environ$("Username") & "\Desktop\FFolder\"
    fName = Sheets("Sheet2").Range("A1").Value
    fPath = strDir
    
    If Dir(strDir, vbDirectory) = "" Then
        MkDir strDir
    Else
        MsgBox "Directory exists."
    End If

    With CreateObject("Scripting.FileSystemObject").CreateTextFile(fPath & fName & ".txt", True)
        For Each myArea In Sheets("Sheet2").Range("B1:B" & Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Row).SpecialCells(2).Areas
            .writeline Join(Application.Transpose(myArea), vbCrLf)
        Next myArea
    End With
    MsgBox "File Saved to: " & fPath
End Sub

Thanks for your code and much appreciate it.

@apo for some reason this script is failing at .writeline Join(Application.Transpose(myArea), vbCrLf). Runtime error '13' Type mismatch. Any suggestions please