I the current article I will simply present how to save the results from a filtered table in MS Excel in a separate text file. To do this you simply need some table as the following one, where you can have some values in Excel cells. To simplify the current article, I have created only two columns – Name and Town. I will use the second one to filter the values, equal to Sofia.
Once they are filtered, I have only the values on the lines of “Peter”, “Keeper”, “Nothing” and “To”, which I will try to export as a text file, located on the same folder, as the current file.
After running the macro below, this is the result:
How do we achieve this result? This is what we do in the VBA code:
- We define the location of the current file (sPath).
- We copy the used range of the sheet.
- We define the last row of the used range.
- Then we open the sPath for input and with two embedded loops we go around the table and for each row we generate a line like the following “Peter”;”Sofia”.
- We close the file and we are ready.
The code is the one below:
VBA - Export Excel to txt file
Dim iFirstCounter As Integer
Dim iSecondCounter As Integer
Dim sText As String
Dim sPath As String
Dim iLast As Integer
sPath = ThisWorkbook.Path & "\Output.txt"
iLast = Range("A65536").End(xlUp).Row
Open sPath For Output As #1
For iFirstCounter = 1 To iLast
For iSecondCounter = 1 To ActiveSheet.UsedRange.Columns.Count
sText = sText & ";" & Cells(iFirstCounter, iSecondCounter).Value
sText = Mid(sText, 2, Len(sText) - 1)
Print #1, sText
sText = ""
Enjoy it! 🙂