Adding multiple formatted tables to one text file

[JoeCool71]

I don't know why this is vexing me, but I have run accross a request that requires me to add a Header and Trailer to my data. Everything that I'm accustomed to would errase and existing file if I were to export data to the same location. My client would like the header to be one line of data indicating where the file is coming from with a date stamp. Right below that would be all the data details. At the bottom there would be a one line trailer indicating the total number of records. I don't have a problem creating these individually, but I am having a problem adding them to the same file. The file will be a fixed width csv file. The header, details and trailer will be in different formats. Any help would be appreciated.

Thank you.
Jim

 

[RobBroekhuis]

Too bad there's a header - just a footer would be easy.
If the csv file already exists, then do something like:

dim s as string
open "MyCSV" for input as #1
open "MyFullDoc" for output as #2
print#2, (header information)
print#2, (more header information)
do while not eof(1)
line input#1,s
print#2,s
loop
print#2, (footer information)
print#2, (more footer information)
close#1
close#2

Rob

 

[Nikita6003]

Or you could try this:

Sub AddHeaderFooter()
    Dim l_wkbTextFile As Workbook
    Dim l_wksSheet As Worksheet
    
    Dim l_sHeaderText As String
    Dim l_sFooterText As String
    
    'Insert your header/footer text HERE
    l_sHeaderText = "YES"
    l_sFooterText = "NO"
    
    'set xl objects - for easier VBA manipulation
    Set l_wkbTextFile = Workbooks.Open("C:\Temp\Info.txt")
    Set l_wksSheet = l_wkbTextFile.Sheets(1)
    
    'Insert empty row 1 & enter header
    l_wksSheet.Cells(1, 1).EntireRow.Insert
    l_wksSheet.Cells(1, 1) = l_sHeaderText
    
    'Enter footer in the first available empty row counting up from the last row - we KNOW the text'll be in col A
    'so this will always work
    l_wksSheet.Cells(65536, 1).End(xlUp).Offset(1, 0) = l_sFooterText
    
    'Save & close - suppress any xl message you might get
    Application.DisplayAlerts = False
    l_wkbTextFile.SaveAs "C:\Temp\Info.txt", xlTextMSDOS
    l_wkbTextFile.Close xlDoNotSaveChanges
    Application.DisplayAlerts = True
    
    'Release object to be neat
    Set l_wksSheet = Nothing
    Set l_wkbTextFile = Nothing
    
End Sub

just beware that the file's saved as the correct kind of text file - i.e. with the fixed width columns still the correct fixed width. But using xlTextMSDOS sets it to ascii so it *should* be ok

Cheers
Nikki

 

[Nikita6003]

Or you could poss create a new file with just the header, append the text file you have now to the header file, and add the footer to the result
Guess that could even be done from the Command prompt
;-)

Cheers
Nikki