Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
appending to .txt file format problems
I currently have the following code. It basically appends a chunk of data at the end of a .txt file. However, the way its formatting is not what I want. Currently, it takes each cell in Range(A1:J?) and makes it's own row in the .txt file. I want a tab delimited file that maintains the column-like format A:J. Example: What I have and don't want in the .txt file: Data1 Data2 Data3 etc. What I need: Data1 Data2 Data3 ....Data8 Data9 Data10 ........Data16 etc. Sub XfertoNotepad() Dim FileNum As Integer Sheets("SQL").Select Range("A2").Select With ActiveSheet Set Lend = .Cells(.Rows.Count, "J").End(xlUp) End With FileNum = FreeFile Open "C:\4x\" & docsol For Append As #FileNum Print #FileNum, [a2] For Each cl In Range("A3:" & Lend) Print #FileNum, myStr myStr = "": myStr = myStr & cl Next 'appends the input to an existing file write to the textfile Print #FileNum, myStr Close #FileNum ' close the file End Sub -- pikapika13 ------------------------------------------------------------------------ pikapika13's Profile: http://www.excelforum.com/member.php...o&userid=10892 View this thread: http://www.excelforum.com/showthread...hreadid=566906 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
appending to .txt file format problems
Hi
I changed your code as below. I have hard coded the filename to test my code. Give this a try. Dim Lend As Long Dim FileNum As Integer Sheets("Sheet1").Select Range("A2").Select With ActiveSheet Lend = .Cells(2, 1).End(xlDown).Row FileNum = FreeFile Open "C:\data\t1\docsol.TXT" For Append As #FileNum Print #FileNum, [a2] For x = 3 To Lend mystr = "" For y = 1 To 9 mystr = mystr & .Cells(x, y).Value & Chr$(9) Next mystr = mystr & .Cells(x, 10).Value Print #FileNum, mystr Next End With Close #FileNum ' close the file -- Tony Green "pikapika13" wrote: I currently have the following code. It basically appends a chunk of data at the end of a .txt file. However, the way its formatting is not what I want. Currently, it takes each cell in Range(A1:J?) and makes it's own row in the .txt file. I want a tab delimited file that maintains the column-like format A:J. Example: What I have and don't want in the .txt file: Data1 Data2 Data3 etc. What I need: Data1 Data2 Data3 ....Data8 Data9 Data10 ........Data16 etc. Sub XfertoNotepad() Dim FileNum As Integer Sheets("SQL").Select Range("A2").Select With ActiveSheet Set Lend = .Cells(.Rows.Count, "J").End(xlUp) End With FileNum = FreeFile Open "C:\4x\" & docsol For Append As #FileNum Print #FileNum, [a2] For Each cl In Range("A3:" & Lend) Print #FileNum, myStr myStr = "": myStr = myStr & cl Next 'appends the input to an existing file write to the textfile Print #FileNum, myStr Close #FileNum ' close the file End Sub -- pikapika13 ------------------------------------------------------------------------ pikapika13's Profile: http://www.excelforum.com/member.php...o&userid=10892 View this thread: http://www.excelforum.com/showthread...hreadid=566906 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
appending to .txt file format problems
Here's one approach:
Private Sub CommandButton1_Click() Dim FileNum As Integer Dim DObj As MSForms.DataObject On Error GoTo Handler FileNum = FreeFile Open "C:\TestData.csv" For Append As #FileNum Set DObj = New MSForms.DataObject Range("A4:" & Cells(Rows.Count, "D").End(xlUp).Address).Copy With DObj .GetFromClipboard Print #FileNum, .GetText End With Close #FileNum ' close the file Exit Sub Handler: Close #FileNum End Sub NickHK "pikapika13" wrote in message ... I currently have the following code. It basically appends a chunk of data at the end of a .txt file. However, the way its formatting is not what I want. Currently, it takes each cell in Range(A1:J?) and makes it's own row in the .txt file. I want a tab delimited file that maintains the column-like format A:J. Example: What I have and don't want in the .txt file: Data1 Data2 Data3 etc. What I need: Data1 Data2 Data3 ....Data8 Data9 Data10 ........Data16 etc. Sub XfertoNotepad() Dim FileNum As Integer Sheets("SQL").Select Range("A2").Select With ActiveSheet Set Lend = .Cells(.Rows.Count, "J").End(xlUp) End With FileNum = FreeFile Open "C:\4x\" & docsol For Append As #FileNum Print #FileNum, [a2] For Each cl In Range("A3:" & Lend) Print #FileNum, myStr myStr = "": myStr = myStr & cl Next 'appends the input to an existing file write to the textfile Print #FileNum, myStr Close #FileNum ' close the file End Sub -- pikapika13 ------------------------------------------------------------------------ pikapika13's Profile: http://www.excelforum.com/member.php...o&userid=10892 View this thread: http://www.excelforum.com/showthread...hreadid=566906 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
appending to .txt file format problems
ADG & Nick, Thanks a lot! I'm new to VBA but this forum sure does have many helpful people. I swear that once I'm good enough, I'll put in my fair share. ADG, I used yours since yours was 1st response. It works great. Thanks! -- pikapika13 ------------------------------------------------------------------------ pikapika13's Profile: http://www.excelforum.com/member.php...o&userid=10892 View this thread: http://www.excelforum.com/showthread...hreadid=566906 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problems appending cells together | Excel Worksheet Functions | |||
Excel 2007 File Problems (Opening New Format) | Excel Discussion (Misc queries) | |||
Appending to a Cell doesn't keep format of existing content | Excel Programming | |||
Appending a second csv file | Excel Discussion (Misc queries) | |||
Appending to File | Excel Programming |