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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
Option Explicit Sub SaveFilteredResults() 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" Sheets("Sheet1").Activate ActiveSheet.UsedRange.Copy Sheets.Add Range("A1").Select ActiveSheet.Paste 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 Next iSecondCounter sText = Mid(sText, 2, Len(sText) - 1) Print #1, sText sText = "" Next iFirstCounter Close #1 End Sub |
Enjoy it! 🙂