How to use VBA to export Excel range to TXT file
How to use VBA to export Excel range to TXT file
Embedded Files
Receive new articles directly in your inbox!! Register your email address
Excel has long been a beloved tool across industries. Its versatility knows no bounds, aiding professionals in crafting intricate spreadsheets capable of crunching numbers, analyzing trends, and organizing information with ease. Yet, often a challenge arises when Excel's output has to be used in other systems, particularly those belonging to a company's core infrastructure.
VBA offers a quick and effortless solution to convert Excel ranges in text files ( TXT)
Text files stand as a universally accepted file format across IT systems, allowing them to be read by different systems
This blog post will explain how to use VBA to convert an Excel range in a TXT file using the FileSystemObject The code will loop through a range of cells reporting each row on a separate row in the TXT file and separating the values in each cells with a comma delimiters.
1 The FileSystemObject
1 The FileSystemObject
VBA FileSystem Objectr allows to access the computer file system to create, delete, edit and copy files / folders. It is available in all MS Office Application including VBA
In this article it will be used to create a TXT file to which the content of an Excel range will be copied
An important aspect to be mentioned is that the FileSystemObject works only on Windows PC
The code will run on a Excel list featuring real estate properties and related type of building, city, location and price.
The end result is to have the same information reported in a TXT file where each row of the Excel sheet will be reported on a separate line in the TXT file and the value of each cell will be separated by a comma in the TXT file
Cell B3 of the spreadsheet contains the text to be used to name the TXT file
The steps that will be followed to build the code are the following
A TXT file wil be created under the directory mentioned in the code. Before running the code the user needs to verify he has access to the path where the TXT file wil be created
The code will loop through the Real Estate list and create a string containing all the values belonging to cells on the same row. The value of each cell will be separated in the TXT file by a comma.
The TXT file wil be closed and the FileScriptingObject cancelled
2 Code Walkthrough
2 Code Walkthrough
Sub txttest()
Dim fso As Object, ts As Object ' 1)
Dim linetowrite As String '2)
Dim i As Integer, b As Integer '3)
'Create the FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject") '4)
'Create the TextFile
Set ts = fso.CreateTextFile("C:\Users\" & Range("B3").Value & ".txt") '5)
i = 6 '6)
b = 2 '6)
linetowrite = "" '7)
ffso and ts are declared as objects, which will be used to work with the FileSystemObject and TextStream, respectively.
linetowrite is a string variable that is initialized as an empty string.
i and b are integer variables initialized with values (i = 6, b = 2).
The FileSystemObject (fso) is initiated using the CreateObject method.
This line creates a text file in the directory specified ("C:\Users\") with the filename based on the value in cell B3 of the active Excel sheet (Range("B3").Value).The .txt extension is concatenated to the value in B3 to create the filename.
i and b are initialized and will be used to loop through the range of cells
linetowrite is the variable to which the cells' content will be written
'Write the range of cells in the txt file
Do Until Cells(i, b).Value = "" '1)
Do Until Cells(i, b).Value = "" '2)
If b = 2 Then '3)
linetowrite = Cells(i, b).Value '3)
Else '3)
linetowrite = linetowrite & "," & Cells(i, b).Value '3)
End If '3)
b = b + 1 '4)
Loop '4)
ts.WriteLine linetowrite '5)
linetowrite = "" '6)
b = 2 '7)
i = i + 1 '8)
Loop
The second part of the code goes through a nested loop An outer loop is initiated and runs until the cell (i,b) is empty.
An inner loop runs until cells (i,b) are empty. The two loops allow to loop through all the rows and columns of the range
The if statement determines if the cell is located in the first column (B) of the range or not. In case it is it starts populating the linetowrite variable not starting with a comma. In case it has passed column B then it appends the linetowrite variable with a comma separating the cells' value
After each iteration the inner loop b is incremented by 1 It continues until it encounters the empty cell in row i.
Once the inner loop ends processing all the non empty cells in row i, the content stored in linetowrite is written to the text file (ts) using the WriteLine method.
Variable is set to empty for the next iteration
b is set to 2 to start processing from the second column; i is incremented by 1 to move to the next row
The outer loop continues the process until it finds an empty row
'Close the file
ts.Close '1)
'Clean up memory
Set fso = Nothing '2)
Set ts = Nothing '2)
End Sub
The txt file (ts) is closed.
The VBA Object, FileScripting and txt file are set to nothing (i.e. cancelled) in order to free up the PC memory
3 Full Code
3 Full Code
The full code is reported below
Sub txttest()
Dim fso As Object, ts As Object
Dim linetowrite As String
Dim i As Integer, b As Integer
'Create the FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")
'Create the TextFile
Set ts = fso.CreateTextFile("C:\Users\" & Range("B3").Value & ".txt")
i = 6
b = 2
linetowrite = ""
'Write the range of cells in the txt file
Do Until Cells(i, b).Value = ""
Do Until Cells(i, b).Value = ""
If b = 2 Then
linetowrite = Cells(i, b).Value
Else
linetowrite = linetowrite & "," & Cells(i, b).Value
End If
b = b + 1
Loop
ts.WriteLine linetowrite
linetowrite = ""
b = 2
i = i + 1
Loop
'Close the file
ts.Close
'Clean up memory
Set fso = Nothing
Set ts = Nothing
End Sub
Page updated
Google Sites
Report abuse