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 I will be using this same file to import blocks in AutoCAD, I wouldn't mind if the newly created file overwrites the previous without prompt.

Thank you!

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

18 Replies

HansVogelaar

MVP

May 17, 2022

JoAvg

Here is such a macro:

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:=Environ("userprofile") & "\Desktop\blocks.txt", _
        FileFormat:=xlUnicodeText
    wb.Close Savechanges:=False
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

JoAvg
Brass Contributor
May 17, 2022
HansVogelaar

Thank you for the immediate reply.
I get Runtime error 9 - Subscript out of a Range for some reason.
Hope you can be of assistance...
- HansVogelaar
  MVP
  May 17, 2022
  JoAvg
  
  If the sheet from which you want to export cells is not really named "B", change the line
  
  Set ws = Worksheets("B")
  
  accordingly.