Option Explicit

Sub ExportRangeToTXT(exportRangeName As String)

'This function exports a range into a text file

'Create a new module and paste this code into it

'Create a range named "exportFileName" (with only one cell) with the export text file path
'ex.: C:\MoxyDraw\MoxyDraw.txt
'**The function needs this range name to operate**

'Create a range name containing MoxyDraw commands
'This range name can be named any name and should be passed to this function using a button
'ex.: exportRange
'**The function needs this range name to operate**

'Create a button in your spreadsheet that calls this function with the name of the range to be exported.
'ex.: ExportRangeToTXT ("exportRange")

'Create another button to open the file
'ex.: OpenFile

'Another button could be created to clear the selected range
'ex.: ClearRange ("exportRange")

    Dim fso, textFile As Object
    Dim row As Range, col As Range, rangeToExport As Range
    Dim fileName, folder, textLine As String
    Dim numberErrors As Integer
    
    'Set the scripting file System
    Set fso = CreateObject("scripting.filesystemobject")
    
    'Set the text file name
    On Error GoTo FileNameRangeError
        fileName = Range("exportFileName")
    On Error GoTo 0
    
    'Get the Folder
    folder = Left(fileName, InStrRev(fileName, Application.PathSeparator)) ' or "\"
    
    'Check if the folder exists
    If Not fso.FolderExists(folder) Then

        MsgBox "File path doesn't exists."

        Exit Sub

    End If

    'Set the export range and check if range exists
    On Error GoTo ExportRangeError
        Set rangeToExport = Range(exportRangeName)
    On Error GoTo 0

    'Create the needed text file
    Set textFile = fso.CreateTextFile(fileName, True, True) 'Filename, Overwrite, Use Unicode

    'Reset the number of errors
    numberErrors = 0

    For Each row In rangeToExport.Rows
            
        'Reset text line content
        textLine = ""
    
        'Find cell content and separate it with a tab
        For Each col In row.Cells
            
            'Check if there is an error in the cell
            If IsError(col.Value) Then
                textLine = textLine & "ERR" & vbTab
                numberErrors = numberErrors + 1
            Else
              textLine = textLine & col.Value & vbTab
            End If
              
        Next col
        
        'Write the line to file
        textFile.writeline textLine
                
    Next row

    'Close the text file
    textFile.Close
    
    'Show a message box if there are errors
    If numberErrors >= 1 Then
        MsgBox "There " & IIf(numberErrors > 1, "are", "is") & numberErrors & " error" & IIf(numberErrors > 1, "s", "") & " in the export range." & _
        vbNewLine & _
        vbNewLine & _
        "In this export range, look for" & _
        vbNewLine & _
        "#REF!, #N/A!, #VALUE!, #NAME!, #DIV/0!, #NAME! etc." & _
        vbNewLine & _
        vbNewLine & _
        "The TEXT file is generated anyway. Look for ""ERR"" values inside it."
    End If
    
    Exit Sub

FileNameRangeError:
    
    MsgBox "The filename range named 'exportFileName' doesn't exists."
    
    Exit Sub
    
ExportRangeError:
    
    MsgBox "The export range named 'ExportRangeToTXT' doesn't exists."
    
    Exit Sub

End Sub

Sub OpenFile()

    Shell "explorer.exe" & " " & Range("exportFileName"), vbNormalFocus

End Sub

Sub ClearRange(rangeToClear As String)

    Range(rangeToClear).ClearContents

End Sub