Exporting Excel 2000 to Text File

[Christine1]

Can anyone tell me if there is any easy way to export an Excel 2000 speadsheet to a text file using (colon delimiters)?

Any help would be greatly appreciated!

Thanks!

 

[Mark W.]

Why not Save as tab-delimited and then edit the results with your favorite editor changing tabs to colons?

 

[JayMenon]

Presumably, Excel does not have this option.
Doesn't "Tab Delimited" or Coma Delimited options in "File,Save As - Save As Type" Serve the purpose ?

Thanks

 

[AJ]

Alternatively, you can use VBA to create a text file from your spreadsheet and build it however you might want it.
I don't know how familiar you are with VBA but the code below will convert your whole sheet into a text file with colons as delimiters. As of course any given cell may have a colon in it, I took the precaution of adding double quotas around these cells - don't know if that's what you'd want.

Anyway, have a look and see what you think.<PRE>
Sub ExportColonDelimited()

Dim MyCol As Integer, MaxCol As Integer
Dim MyRow As Long, MaxRow As Long
Dim strg As String

MaxCol = ActiveSheet.Cells.SpecialCells(xlLastCell).Column
MaxRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row

MyRow = 1

Open "C:Christine.txt" For Output As #1
Do Until MyRow = MaxRow + 1
strg = ""
For MyCol = 1 To MaxCol
If MyCol > 1 Then
strg = strg & ":"
End If
If InStr(1, Cells(MyRow, MyCol).Value, ":", 1) > 0 Then
strg = strg & Chr(34) & Cells(MyRow, MyCol).Value & Chr(34)
Else
strg = strg & Cells(MyRow, MyCol).Value
End If

Next
Print #1, strg
MyRow = MyRow + 1
Loop
Close #1
End Sub</PRE>
Rgds
AJ
This message was edited by AJ on 2002-08-22 10:34

 

[Christine1]

AJ

Your script worked great! Thanks for all your help.

Christine