Append to File

[tagteam]

I have some code that writes out each record in a table to a flat fixed width file. The file is 501 characters in length.

That all works fine but i also have some additional lines I would like to append to the end of that file. I have already created the code that will write out the data i need but I am not sure how to append it to the file. So far I have only been able to write it to a new file.

This is my code to write out to a file

Public Type NameofRecord6  VarRecordFormat As String * 1
  VarBillingInvoicingParty As String * 4
  VarBilledParty As String * 4
  VarAccountDate As String * 4
  VarInvoiceNumber As String * 16
  VarPriceMasterCurrencyIndicator As String * 1
  VarContactType As String * 2
  VarCompanyName As String * 50
  VarContactName As String * 35
  VarContactTitle As String * 35
  VarContactPhone As String * 25
  VarContactFax As String * 25
  VarContactEmail As String * 60
  VarContactAddress As String * 45
  VarContactAddress2 As String * 45
  VarContactAddress3 As String * 45
  VarContactAddress4 As String * 45
  VarContactCity As String * 35
  VarContactState As String * 2
  VarContactCountryCode As String * 2
  VarZipCode As String * 10
  VarReserved As String * 9
  
    
End Type
 
Public Sub OutputTextfileRecord6()
Dim rs As DAO.Recordset
Dim objFile As Object, TextFile As Object
Dim TextRecord As NameofRecord6
'Set rs = CurrentDb.OpenRecordset("q_Export500ByteForClientManual")
Set rs = CurrentDb.OpenRecordset("q_Export500ByteRecord6")
Set objFile = CreateObject("Scripting.FileSystemObject")
Set TextFile = objFile.CreateTextFile("E:\A_500byte\export6.txt", True)
Do Until rs.EOF
  With TextRecord
    .VarRecordFormat = Nz(rs![RecordFormat], "")
    .VarBillingInvoicingParty = Nz(rs![BillingInvoicingParty], "")
    .VarBilledParty = Nz(rs![BilledParty], "")
    .VarAccountDate = Nz(rs![AccountDate], "")
    .VarInvoiceNumber = Nz(rs![InvoiceNumber], "")
    .VarPriceMasterCurrencyIndicator = Nz(rs![PriceMasterCurrencyIndicator], "")
    .VarContactType = Nz(rs![ContactType], "")
    .VarCompanyName = Nz(rs![CompanyName], "")
    .VarContactName = Nz(rs![ContactName], "")
    .VarContactTitle = Nz(rs![ContactTitle], "")
    .VarContactPhone = Nz(rs![ContactPhone], "")
    .VarContactFax = Nz(rs![ContactFax], "")
    .VarContactEmail = Nz(rs![ContactEmail], "")
    .VarContactAddress = Nz(rs![ContactAddress], "")
    .VarContactAddress2 = Nz(rs![ContactAddress2], "")
    .VarContactAddress3 = Nz(rs![ContactAddress3], "")
    .VarContactAddress4 = Nz(rs![ContactAddress4], "")
    .VarContactCity = Nz(rs![ContactCity], "")
    .VarContactState = Nz(rs![ContactState], "")
    .VarContactCountryCode = Nz(rs![ContactCountryCode], "")
    .VarZipCode = Nz(rs![ZipCode], "")
    .VarReserved = Nz(rs![Reserved], "")
    
    
    TextFile.WriteLine (.VarRecordFormat & .VarBillingInvoicingParty & .VarBilledParty & .VarAccountDate & .VarInvoiceNumber & _
    .VarPriceMasterCurrencyIndicator & .VarContactType & .VarCompanyName & .VarContactName & .VarContactTitle & .VarContactPhone & .VarContactFax & _
    .VarContactEmail & .VarContactAddress & .VarContactAddress2 & .VarContactAddress3 & .VarContactAddress4 & .VarContactCity & _
    .VarContactState & .VarContactCountryCode & .VarZipCode & .VarReserved)
    
  End With
  rs.MoveNext
Loop
rs.Close
TextFile.Close
End Sub

Instead of writing that out to E:\A_500byte\export6.txt I would like to append it to E:\A_500byte\export.txt

I create a public sub to test appending text the the file but i can't get it to work

Public Sub Append6ToExport()Const fsoForAppend = 8


Dim objFSO
Set objFSO = Server.CreateObject("Scripting.FileSystemObject")


'Open the text file
Dim objTextStream
Set objTextStream = objFSO.OpenTextFile("E:\A_500byte\export.txt", fsoForAppend)


'Display the contents of the text file
objTextStream.WriteLine "Hello, World!"
objTextStream.WriteLine "This is fun!!"


'Close the file and clean up
objTextStream.Close
Set objTextStream = Nothing
Set objFSO = Nothing
End Sub

I would like to get the above code working and then figure out how to get it to append the data from the first code block in this post to that file.

When i try to run the above code block i get the error "Run-time error '424': Object required . It happens on this line:
Set objFSO = Server.CreateObject("Scripting.FileSystemObject")

Any help is appreciated

[orange]

I use this code to append records to a log file.
It may useful to you.

'---------------------------------------------------------------------------------------
' Procedure : Logger
' Author    : jack
' Date      : 1/21/2009
' Purpose   : To write/append records to a LOG file using FileSystemObject.
'
'Parameters
' sLogName As String -- full path and file name of the log file
' sLogRec As String  -- record to be written to the log
'
' NOTE: Each log record has a timestamp appended
'
' Special Note/restriction:
'***** Must set a reference to MICROSOFT SCRIPTING RUNTIME ***
'---------------------------------------------------------------------------------------
'
Sub Logger(sLogName As String, sLogRec As String)
      Dim tslog As TextStream
      Dim fileLog As File
      Dim i As Integer
      Dim fso As FileSystemObject

10       On Error GoTo Logger_Error

20    Set fso = New FileSystemObject
30    Set fileLog = fso.GetFile(sLogName)
40    Set tslog = fileLog.OpenAsTextStream(ForAppending)
50    tslog.WriteLine Now() & vbTab & sLogRec
60    tslog.Close

Logger_Exit:
70       Exit Sub

Logger_Error:
80       MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure Logger of Module ADO_Etc"
90       Resume Logger_Exit

End Sub

Here's an example where I append a record to the log based on a button click event.

Private Sub btnLogIt_Click()
    Dim logfile As String
10    logfile = "c:\users\mellon\documents\myAccesslog.txt"  'this is the default
20    Call Logger(logfile, Nz(Me.txtLog, "working with ======> " & CurrentDb.name))
30    Me.txtLog = ""
40    Me.Requery

End Sub

[John_G]

Set objFSO = Server.CreateObject("Scripting.FileSystemObject")

Access doesn't know what "server" is in that line.

In your first code block, you have : Set objFile = CreateObject("Scripting.FileSystemObject") - without "Server."

Try taking "Server." out.

[tagteam]

Thanks removing the "Server" did indeed cause it to work.

Now I could use some help with getting it to output some data that I am generating instead of the hand coded

'Display the contents of the text file
objTextStream.WriteLine "Hello, World!"
objTextStream.WriteLine "This is fun!!"


Here is a sample fo the code that generates the data I want

Dim rs As DAO.Recordset
Dim objFile As Object, TextFile As Object
Dim TextRecord As NameofRecord6
'Set rs = CurrentDb.OpenRecordset("q_Export500ByteForClientManual")
Set rs = CurrentDb.OpenRecordset("q_Export500ByteRecord6")
Set objFile = CreateObject("Scripting.FileSystemObject")
Set TextFile = objFile.CreateTextFile("E:\A_500byte\export6.txt", True)
Do Until rs.EOF
  With TextRecord
    .VarRecordFormat = Nz(rs![RecordFormat], "")
    .VarBillingInvoicingParty = Nz(rs![BillingInvoicingParty], "")

I tried working it in like this but it didnt work. I commented out the existing code. Basically, I was hoping it would still run the loop but instead of writing it out to the text file E:\A_500byte\export6.txt it would append it to the file E:\A_500byte\export.txt

Const fsoForAppend = 8Dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Open the text file
Dim objTextStream
Set objTextStream = objFSO.OpenTextFile("E:\A_500byte\export.txt", fsoForAppend)


'Dim rs As DAO.Recordset
'Dim objFile As Object, TextFile As Object
Dim TextRecord As NameofRecord6
'Set rs = CurrentDb.OpenRecordset("q_Export500ByteForClientManual")
'Set rs = CurrentDb.OpenRecordset("q_Export500ByteRecord6")
'Set objFile = CreateObject("Scripting.FileSystemObject")
'Set TextFile = objFile.CreateTextFile("E:\A_500byte\export6.txt", True)
Do Until rs.EOF
  With TextRecord
    .VarRecordFormat = Nz(rs![RecordFormat], "")
    .VarBillingInvoicingParty = Nz(rs![BillingInvoicingParty], "")
    .VarBilledParty = Nz(rs![BilledParty], "")
    .VarAccountDate = Nz(rs![AccountDate], "")
    .VarInvoiceNumber = Nz(rs![InvoiceNumber], "")
    .VarPriceMasterCurrencyIndicator = Nz(rs![PriceMasterCurrencyIndicator], "")
    .VarContactType = Nz(rs![ContactType], "")
    .VarCompanyName = Nz(rs![CompanyName], "")
    .VarContactName = Nz(rs![ContactName], "")
    .VarContactTitle = Nz(rs![ContactTitle], "")
    .VarContactPhone = Nz(rs![ContactPhone], "")
    .VarContactFax = Nz(rs![ContactFax], "")
    .VarContactEmail = Nz(rs![ContactEmail], "")
    .VarContactAddress = Nz(rs![ContactAddress], "")
    .VarContactAddress2 = Nz(rs![ContactAddress2], "")
    .VarContactAddress3 = Nz(rs![ContactAddress3], "")
    .VarContactAddress4 = Nz(rs![ContactAddress4], "")
    .VarContactCity = Nz(rs![ContactCity], "")
    .VarContactState = Nz(rs![ContactState], "")
    .VarContactCountryCode = Nz(rs![ContactCountryCode], "")
    .VarZipCode = Nz(rs![ZipCode], "")
    .VarReserved = Nz(rs![Reserved], "")
    
    
    TextFile.WriteLine (.VarRecordFormat & .VarBillingInvoicingParty & .VarBilledParty & .VarAccountDate & .VarInvoiceNumber & _
    .VarPriceMasterCurrencyIndicator & .VarContactType & .VarCompanyName & .VarContactName & .VarContactTitle & .VarContactPhone & .VarContactFax & _
    .VarContactEmail & .VarContactAddress & .VarContactAddress2 & .VarContactAddress3 & .VarContactAddress4 & .VarContactCity & _
    .VarContactState & .VarContactCountryCode & .VarZipCode & .VarReserved)
    
  End With
  rs.MoveNext
Loop
'rs.Close
'TextFile.Close


objTextStream.Close
Set objTextStream = Nothing
Set objFSO = Nothing
End Sub

[John_G]

You have commented out his line:

'Set TextFile = objFile.CreateTextFile("E:\A_500byte\export6.txt", True)

so TextFile is not being set to ...export6.txt. I don't see it declared in that code segment, so I'm assuming it was declared somewhere else and given the value for ...export.txt, otherwise the code would fail.

(you DO use Option Explicit in every code module, right....?)

[tagteam]

I am a newbie to programming so I don't use option explicit every time. I looked it up and realized that the options to require variables was not set. I have corrected this and will use option explicit in the future.

The file was declared above as Set objTextStream = objFSO.OpenTextFile("E:\A_500byte\export.txt", fsoForAppend)

I was hoping that the append would just take the place of the Set TextFile and instead of writing it out to the new file export6.txt it would append it to the file export.txt.


In my mind what is happening is:

At the top I am saying I am going to append

Const fsoForAppend = 8
Dim objFSO
Set objFSO = CreateObject("Scripting.FileSystemObject"

Then I say where I am going to append it to

Dim objTextStream
Set objTextStream = objFSO.OpenTextFile("E:\A_500byte\export.txt", fsoForAppend)

Then I run the code that will get the data I want to append. That is the part where I say Dim TextRecord as NameofRecord6 and loop through all the records.

Then I close the textobject

objTextStream.Close
Set objTextStream = Nothing

Set objFSO = Nothing

[John_G]

From what I can see, you are setting objTextStream to ...export.txt, but you are writing to TextFile with TextFile.WriteLine, so it is writing to whatever TextFile is set to, which may be ...export6.txt.

Try changing TextFile.WriteLine to objTextStream.WriteLine.

[tagteam]

Thank you for the feedback. i have done as John_G suggested and got the error - Runtime error 91: Object variable or With block variable not set.

So I looked at the code and uncommented my

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("q_Export500ByteForClientM anual")
Set rs = CurrentDb.OpenRecordset("q_Export500ByteRecord6")

because i figured it did not know what it was supposed to loop through if i didnt open that query and it then worked!!!

Super thankful for all the help.

[John_G]

Happy to help, and glad you got it working! I should have seen that - Doh!

Happy new year, and good luck with your project