Create a formated textfile from an MS Access table using VBA

Question

I am trying to use VBA to write an MS Access table to a text file. Unfortunately specific columns of the table must be picked and the textfile should have a specific format.

The Access table has numerous fields and I want to choose just the columns Field1, Field2 and Field3.

The created textfile should then have the following format:

A.1 17
A.2 15
A.3 14
B.1 10
B.2 10
B.3 46
C.1 15
C.2 10
C.3 15

So Field1 and 2 should be separated by a dot and Field2 and 3 by a space. So far I only managed to read the Access table using ADODB.Recordset but didn't get any further.

BIBD BIBD · Accepted Answer · 2015-09-03T15:00:00

Basically you are going to need to open up a file write to it. Here's one way based on what I found here to demonstrate:

Dim FilePath As String
Dim RowData As String

'' ... other variables set like 'LastRow', etc.

FilePath = Application.DefaultFilePath & "\file.txt"
Open FilePath For Output As #2

'' I'd replace this for loop with code that would walk the RecordSet and write the data with 
'' Recordset.Open and .MoveNext's as Andre451 indicated in the comments
For i = 1 To LastRow 
    RowData = "whatever pattern"
    Write #2, RowData
Next i
Close #2

(written but not tested)

There is a way you can dump a table/query result directly to a text file given a predefined pattern, but I've always found it pretty persnickety, so the above is probably your quickest and dirtiest solution.

Create a formated textfile from an MS Access table using VBA

1 Answers