Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate & Write to Text File
I'm trying to concatenate columns 1, 3, 4 and write the results to a
text file. I have some code from one of J-Walk's books but I can't seem to manipulate it to do what I want. Here it is if you want something to start with: Sub ExportRange() Dim Filename As String Dim NumRows As Long, NumCols As Integer Dim r As Long, c As Integer Dim Data Dim ExpRng As Range Set ExpRng = Selection NumCols = ExpRng.Columns.Count NumRows = ExpRng.Rows.Count Filename = "C:\Windows\Desktop\textfile.txt" Open Filename For Output As #1 For r = 1 To NumRows For c = 1 To NumCols Data = ExpRng.Cells(r, c).Value If IsNumeric(Data) Then Data = Val(Data) If IsEmpty(ExpRng.Cells(r, c)) Then Data = "" If c < NumCols Then Write #1, Data; Else Write #1, Data End If Next c Next r Close #1 End Sub Thank You, -- Dan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate & Write to Text File
One way:
Public Sub ExportRange() Const csDELIM As String = "" Const csFILENAME As String = "C:\Windows\Desktop\textfile.txt" Dim rCell As Range Dim nFileNumber As Long Dim sData As String If TypeOf Selection Is Range Then nFileNumber = FreeFile Open csFILENAME For Output As nFileNumber For Each rCell In Selection.EntireRow.Columns(1).Cells With rCell sData = .Text & csDELIM & .Offset(0, 2).Text & _ csDELIM & .Offset(0, 3).Text End With Print #nFileNumber, sData Next rCell Close #nFileNumber End If End Sub If you want a delimiter between cell values, change csDELIM to suit. In article om, "Dan R." wrote: I'm trying to concatenate columns 1, 3, 4 and write the results to a text file. I have some code from one of J-Walk's books but I can't seem to manipulate it to do what I want. Here it is if you want something to start with: Sub ExportRange() Dim Filename As String Dim NumRows As Long, NumCols As Integer Dim r As Long, c As Integer Dim Data Dim ExpRng As Range Set ExpRng = Selection NumCols = ExpRng.Columns.Count NumRows = ExpRng.Rows.Count Filename = "C:\Windows\Desktop\textfile.txt" Open Filename For Output As #1 For r = 1 To NumRows For c = 1 To NumCols Data = ExpRng.Cells(r, c).Value If IsNumeric(Data) Then Data = Val(Data) If IsEmpty(ExpRng.Cells(r, c)) Then Data = "" If c < NumCols Then Write #1, Data; Else Write #1, Data End If Next c Next r Close #1 End Sub Thank You, -- Dan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate & Write to Text File
Excellent, works great JE. Quick question if I might, I'm trying to
stay away from using a selected range. Could I just use something like this: Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Set rng1 = Range("A3", Cells(Rows.Count, 1)) Set rng2 = Range("C3", Cells(Rows.Count, 3)) Set rng3 = Range("D3", Cells(Rows.Count, 4)) And then maybe: sData = rng1 & rng2 & rng3 or something of the like? Also, our computers are set up like this: C:\Documents and Settings \username\Desktop, is there a way to have the txtfile output to the users desktop without having to prompt for the directory? Thanks again, -- Dan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Concatenate & Write to Text File
Probably better to replace
For Each rCell In Selection.EntireRow.Columns(1).Cells with For Each rCell in Range("A3:A" & _ Range("A" & Rows.Count).End(xlUp).Row) In article . com, "Dan R." wrote: Excellent, works great JE. Quick question if I might, I'm trying to stay away from using a selected range. Could I just use something like this: Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Set rng1 = Range("A3", Cells(Rows.Count, 1)) Set rng2 = Range("C3", Cells(Rows.Count, 3)) Set rng3 = Range("D3", Cells(Rows.Count, 4)) And then maybe: sData = rng1 & rng2 & rng3 or something of the like? Also, our computers are set up like this: C:\Documents and Settings \username\Desktop, is there a way to have the txtfile output to the users desktop without having to prompt for the directory? Thanks again, -- Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Open CSV file, format data and write output to a text file. | Excel Programming | |||
Concatenate to a text file | Excel Programming | |||
Write Text File | Excel Programming | |||
Concatenate Text File | Excel Programming | |||
Concatenate Text File | Excel Programming |