Using VB to convert Excel rows to a file

Question

A little inexperienced with VB, so I hope somebody can help with what feels like a simple problem!

Essentially, I need to generate a text file (with .exp extension) for each row of an Excel spreadsheet. However, the number of columns and rows is unknown in advance, and the format needs to be fairly specific. For example, given the following Excel format:

Col1 | Col2
1    | 2
8    | 9
17   | 46

I'd expect 3 files produced. The first file would read as follows:

Col1 1
Col2 2

The second:

Col1 8
Col2 9

The third:

Col1 17
Col2 46

Is there a simple way to do this using VB?

Answer 1

It's been a while since I played with VBA but something like this should work: -

Dim fso As New FileSystemObject
Dim stream As TextStream
Dim iLoop As Integer
iLoop = 1
While Sheets("Sheet1").Range("A" & iLoop) <> ""
    Set stream = fso.CreateTextFile("C:\Row" & iLoop & ".txt", True)
    stream.WriteLine "Col1 " & Sheets("Sheet1").Range("A" & iLoop)
    stream.WriteLine "Col2 " & Sheets("Sheet1").Range("B" & iLoop)
    stream.Close
    iLoop = iLoop + 1
Wend

May need some tweaking, I haven't tested this but this is the basic idea.

Answer 2

I never using Excel, nor yet have a copy to test any code, so bear with me.

A base idea for VBScript (stand-alone .vbs file)...

'connect to Excel
Set XLApp = CreateObject("Excel.Application")
XLApp.Visible = True

'open the file
XLApp.DisplayAlerts = False
Set XLBook = XLApp.WorkBooks.Open("c:\path_to\my.xls")
XLApp.DisplayAlerts = True

'if you know the Worksheet name:
sSheetName = "MySheetName"
Set XLSheet = XLBook.Sheets.Item(sSheetName)

'else use index:
Set XLSheet = XLBook.Worksheets(1)

'or if you want to process all sheets:
For Iter = 1 To XLBook.Worksheets.Count
    Set XLSheet = XLBook.Worksheets(Iter)
    ...
Next

'you can use XLSheet.Columns.Count to iterate
'or if the columns names are known:
Set Col1 = XLSheet.Columns("Col1")
Set Col2 = XLSheet.Columns("Col2")

'or get Range:
Set Range = XLSheet.Columns("Col1:Col2")
'same as above:
Set Range = XLSheet.Range("Col1:Col2")

'vbs FileSystemObject:
Set fso = CreateObject("Scripting.FileSystemObject")

iLoop  = 1 'to enumerate file names
intRow = 2 '?... not sure about this

Do While Range.Cells(intRow,1).Value <> ""
    Set stream = fso.CreateTextFile("Row" & iLoop & ".exp", True)
        stream.WriteLine "Col1 " & Range.Cells(intRow, 1).Value
        stream.WriteLine "Col2 " & Range.Cells(intRow, 2).Value
    stream.Close
    iLoop = iLoop + 1
    intRow = intRow + 1
Loop

'XLBook.Save
XLBook.Close
XLApp.Quit

Set stream = Nothing
Set fso    = Nothing
Set Range  = Nothing
Set XLSheet = Nothing
Set XLBook  = Nothing
Set XLApp   = Nothing