Referencing a 100x2 range then print to file

Question

I have a range that I want to write to a file alternating between columns at each iteration. I would have A1 > B1 > A2 > B2 etc., to give an example:

      A   |   B
1   Hello | World
2   Whats | Up

Now I'd have this in a text file as:

Hello
World
Whats
Up

I have the following code which will grab the info from my first column, but I am stuck at adding the second column at every iteration:

Sub mac()
Dim fso As New FileSystemObject
Dim stream As TextStream
Set stream = fso.CreateTextFile("F:\Hmmmmm.txt", True)

  Range("G2").Select

  Do Until IsEmpty(ActiveCell)
     stream.WriteLine ActiveCell
     ' Here I would affectively want stream.WriteLine ActiveCell + 1 Column
     ActiveCell.Offset(1, 0).Select
  Loop

End Sub

score 1 · Answer 1 · edited May 23 '17 at 11:50

First, if you haven't already done so make sure you've selected the "Microsoft File Scripting" reference under Tools in the VBA editor (For more help read How do I use FileSystemObject in VBA?).

The following should work. Note to read my comments - you can change portions of the code to fit your spreadsheet/needs

Sub mac()
    Dim ws As Worksheet
    Dim fso As New FileSystemObject
    Dim stream As TextStream
    Dim DataTable As Range
    Dim StartCell As Range
    Dim c As Range

    'Edit this line to save the text file to drive\path\filename of your choice
    Const TextFileName As String = "F:\Hmmmmm.txt"       

    Set ws = ActiveSheet
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set stream = fso.CreateTextFile(FileName:=TextFileName, Overwrite:=True)

    'Edit this line to adjust to your spreadsheet
    'I assume your data starts in Cell A1
    Set StartCell = ws.Range("A1")

    Set DataTable = StartCell.CurrentRegion

    For Each c In DataTable.Cells
            stream.WriteLine c.Value
    Next c

    Set c = Nothing
    Set StartCell = Nothing
    Set DataTable = Nothing
    Set ws = Nothing
    Set stream = Nothing
    Set fso = Nothing
End Sub

Referencing a 100x2 range then print to file

1 Answers1