[AccessD] Create and write Text File

Here is a routine I use to build fixed length strings to upload to the AS400
Jim Hale

Function ExportTableToText_TSB(strDatabase As String, strTable As String,
strFile As String) As Boolean
  ' Comments  : exports the data from a table to a fixed length text file
  ' Parameters: strDatabase - path and name of the database to look in or ""
(blank string) for the current database
  '             strTable - name of the table/query containing the data to
export
  '             strFile - name of the file to export to (if it exists, it
will first be deleted)
  ' Returns   : True if successful, False otherwise
  '
  Dim dbstmp As Database
  Dim rstTmp As Recordset
  Dim intFile As Integer
  Dim intCounter As Integer
  Dim strTmp As String

  On Error GoTo PROC_ERR

  If strDatabase = "" Then
    Set dbstmp = CurrentDb()
  Else
    Set dbstmp = DBEngine.Workspaces(0).OpenDatabase(strDatabase)
  End If

   ' Kill the file if necessary
  On Error Resume Next
  Kill strFile
  On Error GoTo PROC_ERR

  ' Open the file
  intFile = FreeFile
  Open strFile For Output As intFile

  ' Open the recordset and loop through it
  Set rstTmp = dbstmp.OpenRecordset(strTable, dbOpenDynaset)

  With rstTmp
    Do Until .EOF
      
      ' initialize the Tmp string
      strTmp = ""
  'Build the record with fixed length fields
      For intCounter = 0 To .Fields.Count - 1
        Select Case intCounter
        Case 0 'Company
            strTmp = strTmp & Right("       " & .Fields(intCounter).Value,
7)
        Case 1 'GL
            strTmp = strTmp & Right("        " & .Fields(intCounter).Value,
8)
        Case 2 'SL
            strTmp = strTmp & Right("     " & .Fields(intCounter).Value, 5)
        Case 3 'BYEAR
            strTmp = strTmp & Right("      " & .Fields(intCounter).Value, 6)
       Case 4 'BMONTH
            strTmp = strTmp & Right("        " & .Fields(intCounter).Value,
8)
       Case 5 'DEPT
            strTmp = strTmp & Right("     " & .Fields(intCounter).Value, 5)
      Case 6 'AMT
            strTmp = strTmp & Right("              " &
Format(.Fields(intCounter).Value, "###.00"), 14)
       Case 7 'REPOST
            strTmp = strTmp & Right("      " & .Fields(intCounter).Value, 6)
       Case Else 'PLANNO
            strTmp = strTmp & Right("       " & .Fields(intCounter).Value,
7)
        End Select
    Next intCounter
  
      Print #intFile, strTmp
  
      .MoveNext
    Loop
        
    .Close
  End With
  
  Close #intFile
  
  dbstmp.Close

  ExportTableToText_TSB = True

PROC_EXIT:
  Exit Function

PROC_ERR:
  ExportTableToText_TSB = False
  Resume PROC_EXIT

End Function

-----Original Message-----
From: Reuben Cummings [mailto:reuben at gfconsultants.com]
Sent: Monday, September 26, 2005 2:23 PM
To: AccessD
Subject: [AccessD] Create and write Text File


Anyone have a routine to create a text file and write to that text file?

I have the routine to create the strings to be added.  Now I just need to
create a text file and then write each string to that file.  There could be
anywhere from 1 to 250 strings to be added and each needs to be its own
line.

Actually what I'm making is a fixed length text file.

Thanks.

Reuben Cummings
GFC, LLC
812.523.1017




-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

***********************************************************************
The information transmitted is intended solely for the individual or
entity to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of or taking action in reliance upon this information by
persons or entities other than the intended recipient is prohibited.
If you have received this email in error please contact the sender and
delete the material from any computer. As a recipient of this email,
you are responsible for screening its contents and the contents of any
attachments for the presence of viruses. No liability is accepted for
any damages caused by any virus transmitted by this email.