How do I make a text file in vba? 2

[OhioSteve]

I am creating a function that writes a String to an external text file. Using some code fragments I found online, I was able to create this:

Function writer(outputMessage As String)
Dim strFile As String
strFile = CurrentProject.Path & "/Output.txt"
Open strFile For Append As 1
Write #1, outputMessage
Close #1
End Function

It works but there is room for improvement. Here are my questions for you:

a) What are the full names for the functions "Write" and "Close"? I tried "application.write" and "file.write" and "docmd.write"....none of them worked.

b) What is the signature for the Write and Close functions? I want know about optional agruments.

c) I recognize that the integer 1 somehow identifies the file. That syntax seems weird and counter-intuitive to me. Are there alternatives to that?

 

[JerryKlmns]

Put the cursor inside the word "Open" and hit F1. Choose VBA Open For c] look at FreeFile Function

 

[fneily]

Here's another way to create, write to, and append to a text file. It uses the Microsoft Scripting Runtime library so you must set a reference to it in your VBA window. This example prints some data from two fields on a form.

Private Sub Command3_Click()
' You must set a reference to the Microsoft Scripting Runtime
' (Tools menu, References command, check the Microsoft Scripting
' Runtime box, and then click OK).

Dim objFSO As Scripting.FileSystemObject
Dim objTS As Scripting.TextStream

' Create the text file.
Set objFSO = New Scripting.FileSystemObject
Set objTS = objFSO.OpenTextFile("c:/Access_Info.txt", _
ForAppending, True)

' Write the results to the text file and then
' close the file.
objTS.WriteLine "Amount = " & Me.amount.Value
objTS.WriteLine "FirstName = " & Me.firstname.Value
objTS.WriteBlankLines 1
objTS.Close

MsgBox "Access Info was written to file."
End Sub

 

[alvechurchdata]

a) What are the full names for the functions "Write" and "Close"?

These are the full names. This isn't really an object-oriented language and there's no concept of anything like a namespace here. These are just the names of functions that date right back to the earliest days of Basic - back when it was "BASIC", the Beginners All-purpose Symbolic Instruction Code. Same with the technique of using an integer to represent a file. That's how it's always been done.

If you want something more OOP, use the File System Object techniques that fneily describes.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[OhioSteve]

fneily-

I do alot of work in Java and asp.net. So the object-oriented approach feels more normal to me. Thanks for pointing me in the right direction!

-Steve

 

[mscallisto]

Here is my example of creating a text file with each cells contents delimited with the "|" character (shift the \ key) or any delimeter you want.

Sub pipey()
'Create a text file with each row cell delimited with "|"

Dim intUsedrows As Integer
Dim intUsedcolumns As Integer

Open "C:\Pipey.txt" For Output As #1

With Worksheets(1).Range("a:f")
intUsedrows = ActiveSheet.UsedRange.Rows.Count
intUsedcolumns = ActiveSheet.UsedRange.Columns.Count

For i = 1 To intUsedrows
  For j = 1 To intUsedcolumns - 1
   Print #1, .Cells(i, j); "|";
  Next j
   Print #1, .Cells(i, intUsedcolumns)
  Next i
End With
Close #1
MsgBox ("Done Successfully")
End Sub