Fixed width export

[Ken Ivins]

I need to export two separate queries in a fixed width format to the same
asc file. Unfortunately the field sizes of the files do not match. For more
details see my original posting at bottom of this post.

Joe Fallon Access MVP was kind enough to give me the below code which I have
adapted and works to a point. By duplicating the "Do While Loop" for the
second query I was able to get both sets of data in one file. The down side
was that it was delimited and not fixed width. Each field has to be fixed
width.

The second problem is that in the first query, I have to end the line at 351
characters and then wrap the next group of 351 characters on the next line,
etc. All fixed width.

Any idea on how to do this, by modifying what is below or a different
approach?

Thanks,
Ken Ivins






Public Sub ExportDelim(strTable As String, strExportFile As String,
strDelimiter As String, Optional blnHeader As Boolean)

'strTable is the table or query name
'strExportFile is the full path and name of file to export to
'strDelimiter is the field deliminator to use like Chr(9) for tab or
Chr(44) for comma or ??

Dim fld As Field
Dim varData As Variant
Dim rs As Recordset
Dim intFileNum As Integer

'set recordset on table or query
Set rs = CurrentDb.OpenRecordset(strTable, dbOpenSnapshot)

'get file handle and open for output
intFileNum = FreeFile()
Open strExportFile For Output As #intFileNum

If blnHeader Then
'output the header row if requested
varData = ""
For Each fld In rs.Fields 'traverse the fields collection
varData = varData & fld.Name & strDelimiter
Next

'remove extra last strDelimiter
varData = Left(varData, Len(varData) - 1)

'write out the header row
Print #intFileNum, varData
End If

'now your data
Do While Not rs.EOF
varData = ""
'concatenate the data row
For Each fld In rs.Fields
varData = varData & fld.Value & strDelimiter
Next

'remove extra last strDelimiter
varData = Left(varData, Len(varData) - 1)

'write out data row
Print #intFileNum, varData

rs.MoveNext
Loop

Close #intFileNum
rs.Close
Set rs = Nothing
End Sub







I have a database with a table for Groups and one for Subscribers in those
groups (one to many). I need to create 2 ASCII files the merge them to email
to another location.

The first table is fixed width with my sender ID in it followed by a
continuous string of up to 30 GroupIDs (9 character ID numbers). Then
multiple lines of GroupIDs (39/line) until all the GroupIds are listed. The
table is has a total of 5 basic fields and one long GroupID field for a
total of 351 characters.

The second fixed width table has subscriber information in it. It has about
50 fields of various lengths for a total of 351 characters.

My final export must combine these two tables. The first table at the top
then the information of the second table.

I am comfortable in sending this by email through code. I am also fine in
creating the second table.

I am not sure how to create the second table that automatically wraps the
Group IDs. I assume I start with a pivot table to get all the GroupIDs but
where to go from there is my problem.

Lastly how to combine these two into one ASCII file really is beyond my
knowledge.

This set of newsgroups have been very helpful in the past and I really
appreciate your time and effort. I hope some day to learn enough to repay
my dept by helping others as well.

Thanks,
Ken

 

[John Nurick]

Hi Ken,

You can use a function along the lines of this air code to convert a
field value into a string padded to a specified length with spaces:

Public Function AlignLeft (V As Variant, Width As Long) As String
Dim S as String
If IsNull(V) Then
S = Space(W)
Else
S = Left(CStr(V) & Space(W), W)
End If
AlignLeft = S
End Function

and then concatenate them into a single string containing the whole of
your fixed-width record.

 

[Ken Ivins]

John,

Thanks. I'm still a novice at doing code. I think I understand what you are
doing here. I'll post again if I can not figure it out.

Ken