Export Specific Excel data into Text file with VBA

This is the blog to give you the way to generate the Text file from Excel data using simple VBA (Visual Basic for Applications) Scripts.

I have an Excel sheet with some Column A and B values and needs to convert into a Text file without manually copying and pasting it.

Solution:

With Excel VBA you can automate tasks in Excel by writing so-called macros. Here we will see some simple steps to write and execute it.

Enable Developer Tab:

To enable the Developer Tab in Excel,

1. Go to File → Options → Customize Ribbon. On the right side of the dialog box, select Main tabs (if necessary).

2. Check the Developer check box.

4. Click OK.

5. You can find the Developer tab next to the View tab.

Command Button

To place a command button on your worksheet, execute the following steps.

1. On the Developer tab, click Insert.

2. In the ActiveX Controls group, click Command Button.

3. Drag a command button on your worksheet and rename it.

Assign a Macro

To assign a macro (one or more code lines) to the command button, execute the following steps.

1. Right-click CommandButton1 (make sure Design Mode is selected).

2. Click View Code.

The Visual Basic Editor appears.

3. Place your cursor between Private Sub CommandButton1_Click() and End Sub.

4. Add the code line shown below.

Complete Code snippet

Private Sub CommandButton1_Click()
Dim myFile As String, rng As Range, cellValue As String, i As Integer, j As Integer
currentDateAndTime = Now()
myFile = Application.DefaultFilePath & “\Import_Order_File_” & Format(currentDateAndTime, “mm_dd_yyyy_hh_mm”) & “.txt”Set rng = Selection
Open myFile For Output As #1
For i = 1 To rng.Rows.Count
For j = 1 To rng.Columns.Count
cellValue = rng.Cells(i, j).Value
If j = rng.Columns.Count Then
Print #1, cellValue
'Write #1, If you want the data as comma separator
Else
Print #1, cellValue,
'Write #1, If you want the data as comma separator
End If
Next j
Next i
Close #1
MsgBox (“The data has been exported to txt!!!.”)
End Sub

5. Save and Close the Visual Basic Editor.

6. Select the range in Excel which you need in the Txt file. Then, click the command button on the sheet (make sure Design Mode is deselected).

7. If you want the output as comma-separated, instead of using Print #1, use Write #1.

8. The message box will display the message as below. Go and check for the Txt file location.

9. Open the created Txt file in the application path. As per this code, it will get stored in C:\Documents.

I believe this blog is useful for your Excel Macro requirement.

Thanks for reading!

Please leave your feedback in the comments section.

Leave a Reply

Your email address will not be published. Required fields are marked *

*