Export from Excel using alternative delimiters

[mov1904]

Hi all,

I need to export from Excel a large table in text format, and I cannot
use
either tab or comma.
I am aware of these solutions:
1- http://www.cpearson.com/excel/imptext.htm#Export (which kind-of-
works
but has a problem with size);
2- changes in regional settings (problem with impact on other lists,
etc.)

Preferably I would like to use a column delimiter ALT-028 and a row
delimiter ALT-030 (both invisible and cannot be typed).
Is there a solution to this problem?

I do not use Excel often and I do not know VBA programming, and since
this
is a one time issue, I cannot afford to learn it now.
This is why I am addressing the experts in this group.
I believe my problem should be common enough to have been solved by
now.
"There is no solution to this problem" is also an acceptable answer, I
just
need to know.

Many thanks in advance,
- Mike Ov.

 

[RB Smissaert]

Try something like this:

Sub StringToTextFile(ByVal txtFile As String, _
ByVal strString As String)

Dim hFile As Long

hFile = FreeFile

'Close before reopening in another mode.
'---------------------------------------
On Error Resume Next
Open txtFile For Input As hFile
Close #hFile

Open txtFile For Output As hFile
'Write #hFile, strString 'this will make start- and end quotes
Print #hFile, strString;
Close #hFile

End Sub

Sub Range2Text(rng As Range, _
strFile As String, _
Optional strColumnDelimiter As String = ",", _
Optional strRowDelimiter As String = vbCrLf)

Dim r As Long
Dim c As Long
Dim arr
Dim UB1 As Long
Dim UB2 As Long
Dim str As String

arr = rng

UB1 = UBound(arr)
UB2 = UBound(arr, 2)

For r = 1 To UB1
If r = 1 Then
For c = 1 To UB2
If c = 1 Then
str = arr(r, c)
Else
str = str & strColumnDelimiter & arr(r, c)
End If
Next c
Else
For c = 1 To UB2
If c = 1 Then
str = str & arr(r, c)
Else
str = str & strColumnDelimiter & arr(r, c)
End If
Next c
End If
str = str & strRowDelimiter
Next r

StringToTextFile strFile, str

End Sub


Sub test()

Range2Text Range(Cells(1), Cells(3, 3)), "C:\Test.txt"

End Sub


Not sure now how to tell VBA you want ALT-028 and ALT-030, but that should
be simple and you
probably know.


RBS

 

[Gord Dibben]

Mike

Earl Kiosterud's free TEXTWRITE add-in may do what you want.

http://www.smokeylake.com/excel/text_write_program.htm


Gord Dibben MS Excel MVP