Macro - Create Tab Delimited *.txt from range

Hello Excel experts! I am in need of a macro in Sheet A that creates a tab delimited blocks.txt file from range A36:C50 in Sheet B in directory C:\Users\user\Desktop\AsBuilt\blocks.txt Also, since ...

excel

Macros and VBA

office 365

HansVogelaar

May 17, 2022

JoAvg

Does this work?

Sub SaveRangeAsText()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim wt As Worksheet
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Set ws = Worksheets("B")
    Set wb = Workbooks.Add(xlWBATWorksheet)
    Set wt = wb.Worksheets(1)
    wt.Range("A1:C15").Value = ws.Range("A36:C50").Value
    wb.SaveAs Filename:=CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\AsBuilt\blocks.txt", _
        FileFormat:=xlUnicodeText
    wb.Close Savechanges:=False
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

HansVogelaar

MVP

May 18, 2022

JoAvg

What happens if you change the line

        wt.Range("A" & r).Value = "'" & wt.Range("A" & r).Value

        wt.Range("A" & r).Value = "''" & wt.Range("A" & r).Value

JoAvg

Brass Contributor

May 18, 2022

HansVogelaar

Same outcome. Won't change.

But! I concatenated the cell I want with double quotes " '' " and it exports as it is supposed to!

Also, the code creates a file with countless blanks lines at the end, so I replaced the

wt.Range("A" & r).Value = "'" & wt.Range("A" & r).Value

with

ws.Range("A" & r).Value = "''" & ws.Range("A" & r).Value

and it stops right at the bottom of the range.

I don't know if it is correct but it works and returns no faults!

Forum Discussion

Macro - Create Tab Delimited *.txt from range