Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
manipulating text file from excel via vba
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
manipulating text file from excel via vba
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. "leighcia" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Manipulating ranges in a different file | Links and Linking in Excel | |||
Manipulating ranges in a different excel file | Excel Worksheet Functions | |||
Help with manipulating text in Excel 2003 | Excel Discussion (Misc queries) | |||
Manipulating Text File | Excel Programming | |||
Manipulating text file data | Excel Programming |