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

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 <(E-Mail Removed)>,
"Dan R." <(E-Mail Removed)> 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

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

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 <(E-Mail Removed)>,
"Dan R." <(E-Mail Removed)> 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