Macro - Create Tab Delimited *.txt from range

[JoAv]

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, or without opening.

Thank you!

 

[Joe4]

I think the easiest way is to open a new workbook, copy just the data you want over to that (A36:C50), then save that new workbook as a Tab Delimited text file and close.
You can get a lot of the VBA code that you need for this by turning on your Macro Recorder, and recording yourself performing these steps manually once.
Then stop the Macro Recorder, and you have your code!

We can help you if you need to do any clean-up/changes to it afterwards.
If you do, post the code you recorded here, and let us know what you need changed.

 

[JoAv]

I got this one but doesn't work properly...
Any help will be appreciated.

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

 

[Joe4]

That does not look like recorded code.
Where did you get that code from?
Did you try my suggestion?

Note that in your code I do not see the variable "xlWBATWorksheet" being set to anything.

 

[JoAv]

That does not look like recorded code.
Where did you get that code from?
Did you try my suggestion?

Note that in your code I do not see the variable "xlWBATWorksheet" being set to anything.

I got it to work finally.

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

However, I would appreciate if you helped with a problem I have.
In cells A36:A50 I have a single quote prefixed (e.g. '3FA8) which is mandatory for CAD software to import the info from the txt file.
When saving txt file, the quote is missing. Is there some kind of workaround so that the final file has a prefixed quote for every row, A36 to A50?
Example:
'3FAD G.P. ENG GROUND PLAN OF XXXXX STREET
'3FC7 AS BUILIT No XXX.LP-GP/0-CPXXXXX/XX.XX
'3FDE FILE NAME XX-XXXXXX.DWG

Thank you.

 

[Joe4]

However, I would appreciate if you helped with a problem I have.
In cells A36:A50 I have a single quote prefixed (e.g. '3FA8) which is mandatory for CAD software to import the info from the txt file.
When saving txt file, the quote is missing. Is there some kind of workaround so that the final file has a prefixed quote for every row, A36 to A50?
Example:
'3FAD G.P. ENG GROUND PLAN OF XXXXX STREET
'3FC7 AS BUILIT No XXX.LP-GP/0-CPXXXXX/XX.XX
'3FDE FILE NAME XX-XXXXXX.DWG

Thank you.

That is a new question, and should thus be posted to a new thread (so it appears as a new unanswered question in the "Unanswered threads" list that people use).

 

[Alex Blakenburg]

Note that in your code I do not see the variable "xlWBATWorksheet" being set to anything.

I have used xlWBATWorksheet before to force Excel to create a new workbook with only 1 worksheet.
Although I set my Excel to open with only 1 sheet (and I think everyone should), many users use the default of 3.
Its called "xlWBATemplate constant"

XlWBATemplate enumeration (Excel)
Excel VBA Create New Workbook: 16 Easy-To-Follow Macro Examples

Or succinctly from Better Solution
Excel Workbooks - Templates

"It is possible to create a new workbook that contains just a single worksheet:
Workbooks.Add(xlWBATemplate.xlWBATWorksheet)"

 

[Joe4]

I have used xlWBATWorksheet before to force Excel to create a new workbook with only 1 worksheet.
Although I set my Excel to open with only 1 sheet (and I think everyone should), many users use the default of 3.
Its called "xlWBATemplate constant"

XlWBATemplate enumeration (Excel)
Excel VBA Create New Workbook: 16 Easy-To-Follow Macro Examples

Or succinctly from Better Solution
Excel Workbooks - Templates

"It is possible to create a new workbook that contains just a single worksheet:
Workbooks.Add(xlWBATemplate.xlWBATWorksheet)"

Thanks for the explanation Alex.
I was not familiar with that.