appending line to text file

[lif]

Hi,

Hope someone can help with this one.

I've written a macro that creates a text file with 3 columns of data.

I now need to add a line at the beginning of the text file. I can't
add it before saving as text for example because then the text file
contains tabs in A2 and A3 and this is a problem in this application.


So I need to find some way to add a line of text to the beginning of a
text file in an excel macro.

how would I go about writing directly to a text file without needing to
open the file in excel, adding text to a cell and closing again??

Any ideas?

 

[Leith Ross]

Hello lif,

Provided your file has a .txt extension, you can open and modify it
using NotePad.

Sincerely,
Leith Ross

 

[lif]

Hi Leith,

Sorry I wasn't clearer. I need to create a txt file from an excel
worksheet. I've written the macro that parses the data I need and
saves the results as .txt . However, I also need this .txt file to
have the following phrase "mesh autocreate" as the first line.

If I add the phrase "mesh autocreate" to the excel sheet in cell a1 in
the macro then when it is saved as .txt there are 2 tab characters
(corresponding to A2 and A3 since there are 3 columns of data)

I can open the file in notepad and remove the tabs manually - but the
next part of the macro I'm writing uses shell() to start a different
bit of software that uses this text file so it needs to be done within
the macro.

So I need to find a way to add that text to the beginning of the file
without the tabs.

I believe there is some way to manipulate the actual text file with
visual basic to get this done. But have no idea how it's done.

 

[Hemant_india]

hi lif
if you are using open stattement
it should be easy for u
read help topic for open statement

 

[Leith Ross]

Hello lif,

This code adds the line "mesh autocreate" to the file designated by the
variable OrigDoc. A new file, NewDoc, is opened and the line is added.
The contents of OrigDoc are then read and copied into it. Lastly, the
OrigDoc is deleted and NewDoc is renamed as OrigDoc. Be sure to change
the DocName and DocPath variables to match your own.

This could also be done using API calls. However, I am not sure how you
would envoke them outside of VBA.


Code:
--------------------
Sub UpdateTextFile()

Dim F1 As Integer
Dim F2 As Integer

DocName = "Test File 1.txt"
DocPath = "C:\Documents and Settings\Jim\My Documents\"

OrigDoc = DocPath & DocName
NewDoc = DocPath & "NewDoc.txt"

NewLine = "mesh autocreate"

F1 = FreeFile
Open NewDoc For Output As F1
F2 = FreeFile
Print #F1, NewLine
Open OrigDoc For Input As F2
Do While Not EOF(F2)
Input #F2, Data
Print #F1, Data
Loop
Close #F2
Close #F1

Kill OrigDoc
Name NewDoc As OrigDoc

End Sub

 

[lif]

Hi Leith,

Here's a belated thanks! I solved it in a very similar way. Here's my
code for posterity

Dim FinalFile As Integer
Dim PreFile As Integer
Dim PreName(1 To 25) As String
Dim FinalName(1 To 25) As String
Dim fso As Object
Dim Temp As String


FinalName(numberofwafermaps) =
WafermapPath & ERsheetname(I) & ".txt"
PreName(numberofwafermaps) =
WafermapPath & ERsheetname(I) & "-pre.txt"
FinalFile = FreeFile()


Set fso =
CreateObject("Scripting.FileSystemObject")

If
(fso.FileExists(FinalName(numberofwafermaps))) Then
Kill
FinalName(numberofwafermaps)
End If

Close #FinalFile
Open FinalName(numberofwafermaps)
For Append As FinalFile
Print #FinalFile, "Mesh AutoCreate"


PreFile = FreeFile()

Open PreName(numberofwafermaps)
For Input As PreFile
'
Do While Not EOF(PreFile)
Line Input #PreFile, Temp
Print #FinalFile, Temp
Loop

'CloseFiles:
'
' ' Close the destination file and
the source file.
Close #PreFile
Close #FinalFile

If
(fso.FileExists(PreName(numberofwafermaps))) Then
Kill
PreName(numberofwafermaps)
End If