Problems exporting to CSV

[Jake]

Hi

I want to export data to a csv file in the following very strict format:
# header information 1
#
# header information 2
# header information 3
TF52 - 1,TF5231228454
TF52 - 2,TF5243422937

....etc

The problem I have is that Excel 2000 appends a trailing comma to the end of
the first four lines (the ones with the # header info) but the application I
need to import the data into screws up when it gets to these commas.

Is there any way I can force Excel to omit the trailing comma if the second
field is blank?

Thanks

Jake

 

[JE McGimpsey]

One way:


Public Sub CommaSV()
Const DELIMITER As String = ","
Dim myRecord As Range
Dim myField As Range
Dim sOut As String

Open "Test.txt" For Output As #1
For Each myRecord In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In Range(.Cells, _
Cells(.Row, Columns.Count).End(xlToLeft))
sOut = sOut & DELIMITER & myField.Text
Next myField
Print #1, Mid(sOut, 2)
sOut = Empty
End With
Next myRecord
Close #1
End Sub

 

[Jake]

I used to do a little programming, but that was 20 years ago and I haven't
touched it since.

but...

scanning though that bit of VB I can see what it's doing.

and...

dumped it straight into Excel, assigned it to a button and it worked first
time.

Many, many thanks for your time and effort!

Jake