Adding header and footer rows to a text file from Access 97

Posted on 2003-02-28
Medium Priority
Last Modified: 2008-03-06
I'm posting this question again since I didn't get any response previously and am up against the wall for a solution.

I need to create an external, fixed length text file containing both a header and footer. All 3 files must conform to a fixed file format (starting location, ending location and length)

I have 3 tables with the necessary info to accomplish this from which I create 3 seperate text files.

Creating the files is not my problem. Appending (inserting) the header and footer files into the main file is.

If anyone could share the correct syntax to do this it will be greatly appreciated.

Of course, if anyone has a better idea of how to do this I welcome that input as well.
Question by:SERVTRKR
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 41

Expert Comment

ID: 8047437

Expert Comment

ID: 8047594

You can accomplish this with Windows command line as follows:

copy HeaderTextFileName+MainTextFileName+FooterTextFileName OutputTextFileName

Please notice that there are no spaces between the file name and the plus (+) sign.



Author Comment

ID: 8051481
Nosterdamus: Could you be more specific. I now have 3 files (HeadertextFileName, MaintextFileName, FooterTextFileName). Are you saying the user would go to what used to be the old DOS prompt and key in this command? Is there not a way to do the completely from Access?
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.


Accepted Solution

Nosterdamus earned 200 total points
ID: 8058498

For some reason, I thought that you need to do the concat only once. Never mind that...

Here is a function that will concat any three files, and will output them to a single file.

The function recieves four parameters:
TextFileName1 As String (Input file 1. in your case, the header file)
TextFileName2 As String (Input file 1. in your case, the main file)
TextFileName3 As String (Input file 1. in your case, the footer file)
TextFileName4 As String (Output File)

The function performs the following checkings before starting the concat execution:
Input file 1 exists. (if not, returns code -100)
Input file 2 exists. (if not, returns code -101)
Input file 3 exists. (if not, returns code -102)

output file does NOT exist. if it does, a question "Overwite?" apears. if the user answer withe Yes, the file is deleted and execution starts, otherwise, execution terminated, and returns code -200.

If execution completes successfuly, the function return code -1.

If some other error occured durring execution, the function displays the error code & description and it returns code is equivalent to Err.Number.

I reccomend that when the function is called, make sure to pass the full path & file name to it.

I also added a Test sub which you can test the perfomance of the function. To run the Test sub, create three text files:
and execute. the file C:\TEMP\Output.txt will be created.

Copy the following code and paste it into a (new?) module:

Public Const ERR_MAIN_FILE_NOT_FOUND = -101
Public Const ERR_OUTPUT_FILE_EXIST = -200

Public Function ConcatFiles(strHeaderFileName As String, _
                            strMainFileName As String, _
                            strFooterFileName As String, _
                            strOutputFileName As String) As Integer
On Error GoTo Err_ConcatFiles

    Dim nConcatFileStatus As Integer
    Dim nHeaderFileNo As Integer
    Dim nMainFileNo As Integer
    Dim nFooterFileNo As Integer
    Dim nOutputFileNo As Integer
    Dim strLine As String
    nConcatFileStatus = FILE_CONCAT_NO_ERRORS
    If Dir(strHeaderFileName) = "" Then 'header file not found
        nConcatFileStatus = ERR_HEADER_FILE_NOT_FOUND
        MsgBox "The intended header file " & strHeaderFileName & _
               " is missing!" & vbCrLf & vbCrLf & _
               "Can not continue -> excecution terminated!", _
               vbCritical + vbOKOnly, "Header file missing"
    End If

    If nConcatFileStatus = FILE_CONCAT_NO_ERRORS Then
        If Dir(strMainFileName) = "" Then 'main file not found
            nConcatFileStatus = ERR_MAIN_FILE_NOT_FOUND
            MsgBox "The intended main file " & strMainFileName & _
                   " is missing!" & vbCrLf & vbCrLf & _
                   "Can not continue -> excecution terminated!", _
               vbCritical + vbOKOnly, "Main file missing"
        End If
    End If

    If nConcatFileStatus = FILE_CONCAT_NO_ERRORS Then
        If Dir(strFooterFileName) = "" Then 'footer file not found
            nConcatFileStatus = ERR_FOOTER_FILE_NOT_FOUND
             MsgBox "The intended footer file " & strFooterFileName & _
                   " is missing!" & vbCrLf & vbCrLf & _
                   "Can not continue -> excecution terminated!", _
               vbCritical + vbOKOnly, "Footer file missing"
       End If
    End If

    If nConcatFileStatus = FILE_CONCAT_NO_ERRORS Then
        If Dir(strOutputFileName) <> "" Then 'output file exist
            If MsgBox("The file name " & strOutputFileName & " already exist!" & _
               vbCrLf & vbCrLf & "Do you want to OVERWRITE it?", vbExclamation + vbYesNo, _
               "Output file exist") = vbYes Then
                Kill (strOutputFileName)
                nConcatFileStatus = ERR_FOOTER_FILE_NOT_FOUND
            End If
        End If
    End If

    If nConcatFileStatus = FILE_CONCAT_NO_ERRORS Then
        nOutputFileNo = FreeFile()
        Open strOutputFileName For Output As #nOutputFileNo
        nHeaderFileNo = FreeFile()
        Open strHeaderFileName For Input As #nHeaderFileNo
        Call MoveLines(nHeaderFileNo, nOutputFileNo)
        Close nHeaderFileNo
        nMainFileNo = FreeFile()
        Open strMainFileName For Input As #nMainFileNo
        Call MoveLines(nMainFileNo, nOutputFileNo)
        Close nMainFileNo
        nFooterFileNo = FreeFile()
        Open strFooterFileName For Input As #nFooterFileNo
        Call MoveLines(nFooterFileNo, nOutputFileNo)
        Close nFooterFileNo
        Close nOutputFileNo
    End If
    ConcatFiles = nConcatFileStatus 'Return function status
    Exit Function

    nConcatFileStatus = Err.Number
    MsgBox "Error: " & Err.Number & vbCrLf & _
           "Desc:  " & Err.Description & vbCrLf & vbCrLf & _
           "Execution terminated abnormaly!!!", vbCritical + vbOKOnly, _
           "Abnormal termination"
    Resume Exit_ConcatFiles

End Function

Private Sub MoveLines(nInFile As Integer, nOutFile As Integer)
    Do Until EOF(nInFile)
        Line Input #nInFile, strLine
        Print #nOutFile, strLine
End Sub

Public Sub test()
     Const HEADER_FILE = "Header.txt"
     Const MAIN_FILE = "Main.txt"
     Const FOOTER_FILE = "Footer.txt"
     Const OUTPUT_FILE = "Output.txt"
     Const IN_FILES_PATH = "C:\TEMP\"
     Const OUT_FILE_PATH = "C:\TEMP\"
     Dim strHeaderFile As String
     Dim strMainFile As String
     Dim strFooterFile As String
     Dim strOutputFile As String
     Dim nReturnStatus As Integer
     strHeaderFile = IN_FILES_PATH & HEADER_FILE
     strMainFile = IN_FILES_PATH & MAIN_FILE
     strFooterFile = IN_FILES_PATH & FOOTER_FILE
     strOutputFile = OUT_FILE_PATH & OUTPUT_FILE
     'call the ConcatFiles function
     nReturnStatus = ConcatFiles(strHeaderFile, strMainFile, strFooterFile, strOutputFile)
     MsgBox "ConcatFiles function return code = " & nReturnStatus
End Sub



Author Comment

ID: 8098234
Thanks for the help.

Expert Comment

ID: 8098377

Glad I could help & good luck with your app.



Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question