Forum Discussion

Brass Contributor

May 17, 2022

Solved

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 ...

May 17, 2022

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

Try this:

Sub SaveRangeAsText()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim wt As Worksheet
    Dim r As Long
    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
    For r = 1 To 15
        wt.Range("A" & r).Value = "'" & wt.Range("A" & r).Value
    Next r
    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

JoAvg

Brass Contributor

May 18, 2022

HansVogelaar

Same outcome. No quote at the beginning.

Maybe it has to do with the fact that the prefixed single quote indicates the cell contents as text?

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!

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