Forum Discussion
JoAvg
May 17, 2022Brass Contributor
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
JoAvg
May 17, 2022Brass Contributor
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
May 17, 2022MVP
If the sheet from which you want to export cells is not really named "B", change the line
Set ws = Worksheets("B")
accordingly.
- JoAvgMay 17, 2022Brass Contributor
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...
- HansVogelaarMay 17, 2022MVP
If you click Debug in the error message, which line is highlighted in yellow?
- JoAvgMay 17, 2022Brass Contributor
I don't get the the debug dialog.. just the dialog I posted ..
Sorry I am no good with VBA...