manipulating text file from excel via vba

[leighcia]

Hello!

I am trying to add two columns of a Excel data to a text file with a
macro.

The text file contains two columns: one with month/year and the other
with interest rates.

The Excel data is just a column of interest rates (360 periods).I want
to add those interest rates to the end of the text file in the
interest rate column and then add dates int he other column.

I've tried to open the text file in Excel and then change it, but that
complicates things because the date formats don't transfer correctly.
I guess an alternative if anyone could help me would be to put entries
in as strings. But then how do I make sure the entries get added at
the end of the columns?

Thanks!

 

[JLatham]

You could open your text file for for appending and then write strings out
containing the data from Excel. Here's a kind of 'primative' code setup to
do such a thing:
Sub AppendMoreData()
Dim outFile As String
Dim outFilenum As Integer
Dim outFileRecord As String
Dim rowPointer As Long
Dim fieldSeparator As String

'pick one of these
'depending on your file format
fieldSeparator = vbKeyTab ' if tab delimited
fieldSeparator = ";" ' if semi-colon delimited
fieldSeparator = "|" ' if 'pipe' delimited
fieldSeparator = "," ' if comma (most common) delimited

outFile = "C:\myTestFile.txt"
outFilenum = FreeFile()
Open outFile For Append As #outFilenum
'set up to get data from Excel
'assumes data in A and B
Range("A1").Select
Do Until IsEmpty(ActiveCell.Offset(rowPointer, 0))
outFileRecord = ActiveCell.Offset(rowPointer, 0) & _
fieldSeparator & ActiveCell.Offset(rowPointer, 1)
Print #outFilenum, outFileRecord
outFileRecord = ""
rowPointer = rowPointer + 1
Loop
Close #outFilenum
MsgBox "All done."

End Sub


If you need to format either of the data items from the columns before
writing them to the file, you could pick those up one at a time from the
rows, put them into proper format, and then build the outFileRecord string
from that.

You'll need to know the format of that .txt file - whether its character
delimited (as with comma, semi-colon, tab, etc) and modify the code to use
the one it needs. If it is fixed field length then you need to bring in the
first data item, pad it with spaces to the proper length and then append the
second data item (padded if required) to that as your outFileRecord string.

Lets say your fields are fixed length, first record is in bytes 1-12 of a
record, 2nd field is in bytes 13-20 (8 bytes long), each record being a total
of 20 bytes long and you know that the data you're reading in will fit within
those limits (that is you don't have any that are too long). Then you could
do something like this within the loop
'get data item
Data1 = Trim(ActiveCell.Offset(rowPointer, 0))
'pad w/leading spaces
Data1 = String(12 - Len(Data1), " ") & Data1
'get 2nd data item
Data2 = Trim(ActiveCell.Offset(rowPointer, 1))
'pad it w/leading spaces also
Data2 = String(8 - Len(Data2), " ") & Data2
'put the two together for sending to text file
outFileRecord = Data1 & Data2
'alternately, you could not use outFileRecord and just use
Print #outFilenum, Data1 & Data2

Hope this helps get you going.