VBA Macros

Save a range as text file using VBA [tutorial]

Last updated on November 3, 2015

Chandoo

Last night I asked members of our Chandoo.org facebook page to share an Excel problem you are struggling with. Francis asked,

How to save a file as .txt in vba without quotes? When I save as .txt, the file has got quotes inside of it. I used the code Print, but it didnt work because the file loses its delimitation.
Does anyone know how to solve this?

Let’s understand how to save a range as text and overcome the double quote problem.

Saving a range as Text – the easy way

Say you have a file like this:

The easy option is to save your worksheet as text file using below macro.


Sub saveText()
    ActiveWorkbook.SaveAs filename:= _
        ThisWorkBook.Path & "\textfile-" & Format(Now, "ddmmyy-hhmmss") & ".txt", FileFormat:=xlText, _
        CreateBackup:=False
End Sub

While this works, it has 2 problems.

It exports the entire current worksheet.
It adds double quotes “” to text values or formatted cells.

So you get this.

Saving a range as Text – the proper way

We can create a blank text file using VBA and write the range data values in to that file. This way we will have full control over what goes in to the file and how it’s formatted.

Here is the code:


Sub saveText2()
    Dim filename As String, lineText As String
    Dim myrng As Range, i, j
    
    filename = ThisWorkbook.Path & "\textfile-" & Format(Now, "ddmmyy-hhmmss") & ".txt"
    
    Open filename For Output As #1
    
    Set myrng = Range("data")
    
    For i = 1 To myrng.Rows.Count
        For j = 1 To myrng.Columns.Count
            lineText = IIf(j = 1, "", lineText & ",") & myrng.Cells(i, j)
        Next j
        Print #1, lineText
    Next i
    
    Close #1
End Sub

Let’s understand the code…

Create a file name

We take the current workbook path and set up textfile-time stamp.txt in that directory.

Note, the time stamp portion is dynamic and changes every time you run the code.

We then open the file using Open filename For Output As #1 line.

This sets up a new file and opens it for us to write anything we want.

Loop thru range data and write values to the file

We loop thru each and every cell of the range("data"). We need to take all the values in a row and concatenate them with delimiter comma (,).

This is done in nested for loops (related: Introduction to For loop- Excel VBA)

We loop thru each column in a row and construct lineText.

We then print this lineText to file #1 using,

Print #1, lineText

Finally we close the file.

The end result

This is what we get.

Download Save Text example macro

Click here to download the example workbook. Examine the savetext macro to learn more.

How do you create text files using VBA?

Do you create text / CSV / TSV files from Excel data? How do you automate the process? Please share your tips and ideas in the comments section.

Learn more powerful ways to use VBA:

Share this tip with your colleagues

32 Comments
Ask a question or say something...
Tagged under advanced excel, downloads, file processing using VBA, Learn Excel, macros, no-nl, text processing, VBA
Category: VBA Macros

Want an AWESOME
Excel Class?

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.

Rebekah S

Reporting Analyst

FREE Goodies for you...

Related Tips

Excel Challenges

26 Responses to “Get busy this weekend, with OR XOR AND [Excel Homework]”

Stef@n says:

July 29, 2011 at 11:48 am

first solution for AND
The two numbers are in A1 and B1
= SUBSTITUTE (SUBSTITUTE (A1+B1*9*9, 9, 1), 8, 0)
regards
Stef@n

Reply
Stef@n says:

July 29, 2011 at 11:50 am

next solution for OR
=1*SUBSTITUTE (A1+A2;2;1)

regards
Stef@n

Reply
Stef@n says:

July 29, 2011 at 11:52 am

last solution for XOR
=1*SUBSTITUTE (A1+A2;2;0)

regards
Stef@n

Reply
Irvine says:

July 29, 2011 at 12:26 pm

Or you could make use of the VBA logical operators!

Define the following as custom functions
Public Function BITXOR(x As Long, y As Long)
BITXOR = x Xor y
End Function

Public Function BITAND(x As Long, y As Long)
BITAND = x And y
End Function

Public Function BITOR(x As Long, y As Long)
BITOR = x Or y
End Function

and then use them such:
A B =BITOR(A,B) =BITAND(A,B) =BITXOR(A,B)
0101 0100 0101 0100 0001

Reply
Stef@n says:

July 29, 2011 at 12:46 pm

an another solution for AND
=1*SUBSTITUTE (SUBSTITUTE (A1+A2;1;0);2;1)

note:
the binary numbers are in A1 and A2 !
regards
Stef@n

Reply
Shair says:

July 29, 2011 at 2:22 pm

I was obviously playing hooky at the beach during the bit-wise math lesson – you lost me at “Understanding bit-wise operations” 🙂

Reply
Anup Agarwal says:

July 30, 2011 at 8:46 am

After looking at the above solutions, I find my solution silly, but still:

For the following formulae,
Row 1: headers,
Row 2: OR
Row 3: AND
Row 4: XOR

Column 1: Input 1
Column 2: Input 2
Column 3: Result

OR
{=SUM(IF(MID(A2,ROW(OFFSET($A$1,0,0,LEN(A2),1)),1)+MID(B2,ROW(OFFSET($A$1,0,0,LEN(B2),1)),1)>0,1,0)*10^(LEN(A2)-ROW(OFFSET($A$1,0,0,LEN(B2),1))))}

AND
{=SUM(IF(MID(A3,ROW(OFFSET($A$1,0,0,LEN(A3),1)),1)+MID(B3,ROW(OFFSET($A$1,0,0,LEN(B3),1)),1)=2,1,0)*10^(LEN(A3)-ROW(OFFSET($A$1,0,0,LEN(B3),1))))}

XOR
{=SUM(IF(MID(A4,ROW(OFFSET($A$1,0,0,LEN(A4),1)),1)+MID(B4,ROW(OFFSET($A$1,0,0,LEN(B4),1)),1)=1,1,0)*10^(LEN(A4)-ROW(OFFSET($A$1,0,0,LEN(B4),1))))}

Reply
Hui... says:

July 31, 2011 at 11:57 pm

@Anup
Please don't consider your solution silly
Firstly, You are the 3rd person to submit an answer
Secondly, The best formula/function is the one that you know and understand.

Reply
Michael Azer says:

August 1, 2011 at 1:40 pm

I think I have a very tedious solution, which people won't have the patience to do except in small numbers.

I used the same problem setup as "Anup Agarwal"

AND =IF(AND(MID(B2,1,1)="1",MID(C2,1,1)="1"),1,0)&IF(AND(MID(B2,2,1)="1",MID(C2,2,1)="1"),1,0)&IF(AND(MID(B2,3,1)="1",MID(C2,3,1)="1"),1,0)&IF(AND(MID(B2,4,1)="1",MID(C2,4,1)="1"),1,0)

OR =IF(OR(MID(B3,1,1)="1",MID(C3,1,1)="1"),1,0)&IF(OR(MID(B3,2,1)="1",MID(C3,2,1)="1"),1,0)&IF(OR(MID(B3,3,1)="1",MID(C3,3,1)="1"),1,0)&IF(OR(MID(B3,4,1)="1",MID(C3,4,1)="1"),1,0)

=IF(OR(AND(MID(B4,1,1)="1",MID(C4,1,1)="0"),AND(MID(B4,1,1)="0",MID(C4,1,1)="1")),1,0)&IF(OR(AND(MID(B4,2,1)="1",MID(C4,2,1)="0"),AND(MID(B4,2,1)="0",MID(C4,2,1)="1")),1,0)&IF(OR(AND(MID(B4,3,1)="1",MID(C4,3,1)="0"),AND(MID(B4,3,1)="0",MID(C4,3,1)="1")),1,0)&IF(OR(AND(MID(B4,4,1)="1",MID(C4,4,1)="0"),AND(MID(B4,4,1)="0",MID(C4,4,1)="1")),1,0)

Reply
Michael Azer says:

August 1, 2011 at 1:43 pm

Sorry my last post was totally messed up

AND
=IF(AND(MID(B2,1,1)="1",MID(C2,1,1)="1"),1,0)&IF(AND(MID(B2,2,1)="1",MID(C2,2,1)="1"),1,0)&IF(AND(MID(B2,3,1)="1",MID(C2,3,1)="1"),1,0)&IF(AND(MID(B2,4,1)="1",MID(C2,4,1)="1"),1,0)
OR
=IF(OR(MID(B3,1,1)="1",MID(C3,1,1)="1"),1,0)&IF(OR(MID(B3,2,1)="1",MID(C3,2,1)="1"),1,0)&IF(OR(MID(B3,3,1)="1",MID(C3,3,1)="1"),1,0)&IF(OR(MID(B3,4,1)="1",MID(C3,4,1)="1"),1,0)
XOR
=IF(OR(AND(MID(B4,1,1)="1",MID(C4,1,1)="0"),AND(MID(B4,1,1)="0",MID(C4,1,1)="1")),1,0)&IF(OR(AND(MID(B4,2,1)="1",MID(C4,2,1)="0"),AND(MID(B4,2,1)="0",MID(C4,2,1)="1")),1,0)&IF(OR(AND(MID(B4,3,1)="1",MID(C4,3,1)="0"),AND(MID(B4,3,1)="0",MID(C4,3,1)="1")),1,0)&IF(OR(AND(MID(B4,4,1)="1",MID(C4,4,1)="0"),AND(MID(B4,4,1)="0",MID(C4,4,1)="1")),1,0)

Reply
Stephen says:

August 1, 2011 at 1:45 pm

@stefan,
I just couldn't get your solutions to work.
01010101010 + 01010101110 = 02020210120
what am i doing wrong?

Reply
Stephen says:

August 1, 2011 at 1:56 pm

@anup
...I got yours to work!

Reply
Rich says:

August 1, 2011 at 8:17 pm

@Stephen - I get the same, but Stef@an's second solution for AND does work (at least for the test cases I used)

Reply
Stef@n says:

August 2, 2011 at 9:09 am

@ Stephen / Rich
yes , you are right ! - only this works:

OR
=1*SUBSTITUTE (A1+A2;2;1)
XOR
=1*SUBSTITUTE (A1+A2;2;0)
AND
=1*SUBSTITUTE (SUBSTITUTE (A1+A2;1;0);2;1)

Reply
Michael Azer says:

August 3, 2011 at 1:36 pm

@Stef@n - You're answer is really smart, I never knew about the substitute function before. Great Work!

Reply
Stef@n says:

August 3, 2011 at 2:45 pm

Thx Michael 🙂
yes - it is simply easy 😉
if you add 1 and 1 - excel calculate 2
and then you have to substitute the 2 - new = 0 respectively 1

Reply
Rob says:

September 11, 2011 at 4:16 pm

Here is a good resource for people wanting to learn binary and hexadecimal.
http://justwebware.com/bitwise/bitwise.html

Reply
Kyle McGhee says:

January 29, 2012 at 3:57 pm

Three that weren't asked for:
NOT
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1+A2,0,3),1,0),3,1)

EQV
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1+A2,0,3),2,3),1,0),3,1)

IMP
=SUBSTITUTE(SUBSTITUTE(A1+SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,0,3),1,0),3,1),0,1),2,0)

(was using Daniel Ferry's bitwise file to verify against)

Reply
Q says:

February 27, 2012 at 9:05 am

@ Kyle

Not only takes one parameter and inverts 0 -1 and 1-0

Took out the +A2
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,3),1,0),3,1)

Reply
Richard Tallent says:

November 1, 2012 at 3:57 pm

Great solutions!

I'll add two:

NAND =1*SUBSTITUTE (A1+A2,2,0)

NOR=1*SUBSTITUTE(SUBSTITUTE (SUBSTITUTE(A1+A2,0,2),1,0),2,1)

Reply
John Fuller says:

August 29, 2014 at 4:15 pm

This will work for binary numbers of any size (although the text format mask will have to have as many zeroes as there are digits in the longest addend)

Assume binary #s are in C35 & C36, then add and format as text in C37:
=TEXT(C36+C35,"000000000000")
-sum- = 101112211112

AND - SUBSTITUTE 0s for 1s in -sum-, then sub 1s for 2s
=SUBSTITUTE(SUBSTITUTE(C37,"1","0"),"2","1")

OR - sub 1s for 2s in -sum-
=SUBSTITUTE(C37,"2","1")

XOR - sub 0s for 2s in -sum-
=SUBSTITUTE(C37,"2","0")

Reply
Shane says:

September 21, 2014 at 3:47 am

Just wandered by:

AND:

=SUBSTITUTE(A1+A2,1,0)/2

Reply
- xlpadawan says:
  
  September 22, 2014 at 2:22 pm
  
  Clever, Shane. I like that.
  
  Reply
How do I perform a XOR calucation of two binary numbers in excel 2007 - QuestionFocus says:

December 9, 2017 at 2:27 pm

[…] post http://www.excelhero.com/blog/2010/01/5-and-3-is-1.html for examples using Sumproduct, and http://chandoo.org/wp/2011/07/29/bitwise-operations-in-excel/ for examples using Text […]

Reply
Ronald Vonk says:

December 6, 2018 at 12:00 pm

Hi Chandoo,

I am not (yet) really into bitwise calculation, but I am looking for a way to speed up my vba calculation with very big numbers. Would is ben convenient to use bitwise notation for this?

Best regards,

Ronald (the Netherlands)
p.s. love your country!

Reply
- Hui... says:
  
  December 7, 2018 at 4:25 am
  
  @Ronald
  
  I'd suggest asking this in the Chandoo.org Forums
  https://chandoo.org/forum/
  
  Attach a sample file with an example of some data and describe what you want to achieve
  
  Reply

Save a range as text file using VBA [tutorial]

Chandoo

Saving a range as Text – the easy way

Saving a range as Text – the proper way

Let’s understand the code…

Download Save Text example macro

How do you create text files using VBA?

Get FREE Excel + Power BI Tips

Welcome to Chandoo.org

Mandatory Trainings: Are we doing them? [Data Analysis Challenge – 002]

FREE Calendar & Planner Excel Template for 2026

Who is my boss’s boss? [Data Analytics Challenge – 001]

New Zealand GST Calculation with Excel [Free Template]

Make a Pivot from Another Pivot Table in Excel

Related Tips

Mandatory Trainings: Are we doing them? [Data Analysis Challenge – 002]

Who is my boss’s boss? [Data Analytics Challenge – 001]

New Zealand GST Calculation with Excel [Free Template]

Make a Pivot from Another Pivot Table in Excel

How to use XLOOKUP with two sheets?

26 Responses to “Get busy this weekend, with OR XOR AND [Excel Homework]”

Leave a Reply

Get FREE Excel & Power-BI Newsletter

Get Started

Online Classes

About

Downloads