Change separation between row data from comma to tabulation

SunnyX
SunnyX used Ask the Experts™
on
I have a code that copy past data from particular sheet to txt file however data row in txt separates by comma. What should I change in code in order that separation will be by tabulation ( key "Tab" in a keyboard )
Many thx in advance !
In attachment you can find examples

Option Explicit
Sub test()
    Dim TempSht As Worksheet
    Dim rRng As Range
    Dim sFullPath As String
    sFullPath = ThisWorkbook.Path & Application.PathSeparator

    With Sheet1
        Set rRng = .Range(.Cells(8, 2), .Cells(.Rows.Count, 7).End(xlUp))
    End With
    Set TempSht = Sheets.Add
    rRng.Copy TempSht.Range("A1")
    TempSht.Copy
    ActiveWorkbook.SaveAs Filename:=sFullPath & "testing.txt", FileFormat:=xlCSV, CreateBackup:=False
    Application.DisplayAlerts = False
    TempSht.Delete
    ActiveWorkbook.Close True
    Application.DisplayAlerts = True
End Sub

Open in new window

testing-comma-.txt
testing-tab.txt
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi,

Change this line:

ActiveWorkbook.SaveAs Filename:=sFullPath & "testing.txt", FileFormat:=xlCSV, CreateBackup:=False

To read:

ActiveWorkbook.SaveAs Filename:=sFullPath & "testing.txt", FileFormat:=xlText, CreateBackup:=False

Author

Commented:
Thx !
You're welcome :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial