Exporting active worksheet to a .txt

[excelbeginner1]

Good day

Please could you assist in am trying to write a VBA to export an active excel work sheet to a .txt file.
I only need the selected sheet.(sheet1)

i am attaching a sample of the work book as well as how the text files needs to look.

On another note is it possible to have the line (row)in be automatically copied by the quanity in the column name parcel quantity.
then exported to the text file. or must i manually copy the rows.

 

[JW00]

The following "WriteDataToTextFile" code will give you the same as the 2nd jpg, you will have to reference "Microsoft Forms 2.0 Object Library" in vba.

Sub WriteDataToTextFile()
Dim rng As Range
Dim arr() As Variant
Dim MyText As New MSForms.DataObject
Dim MyString As String
Dim i As Long, j As Long, k As Long, m As Long
Dim LR As Long, LC As Long

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.DisplayAlerts = False
    
    Range("A1").CurrentRegion.Select
    
    LR = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
    LC = ActiveCell.CurrentRegion.Columns.Count
    
    Set rng = Range(Cells(1, 1), Cells(LR, LC))
    
    arr = rng.Value
    
    For i = LBound(arr) To UBound(arr)
        MyString = MyString & Chr(34) & Chr(34) & arr(i, 1) & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & arr(i, 2) & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & arr(i, 3) & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & arr(i, 4) & Chr(34) & Chr(34) & "," & Chr(34) & Chr(34) & arr(i, 5) & Chr(34) & Chr(34) & vbNewLine
    Next i
    
    Range("A1").Select
    
    MyText.SetText (MyString)
    MyText.PutInClipboard
    WriteText
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.DisplayAlerts = True

End Sub

Sub WriteText()
Dim strTempFile As String
Dim strData As String
Dim spath As String

    spath = Environ("USERPROFILE") & "\Desktop"
    
    With CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        .GetFromClipboard
        strData = .GetText
    End With
    
    strTempFile = spath & "\temp.txt"
    
    With CreateObject("Scripting.FileSystemObject")
        .CreateTextFile(strTempFile, True).Write strData
    End With
    
    Shell "cmd /c ""notepad.exe """ & strTempFile & """"

End Sub