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 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.
  - JoAvg
    Brass Contributor
    May 17, 2022
    HansVogelaar
    I already did but I still get the error message...
    Plus, I get a *txt named after the Sheet but in my Documents folder...
    Does the wb.SaveAs Filename:=Environ("userprofile") & "\Desktop\blocks.txt" perhaps need to be changed also?
    I cant seem to figure it out...