What is the error message and which line specifically does it stop on?


"Kurt Barr" wrote:

I tried to run it, and it got caught in the loop at the end of the second
function.

"XP" wrote:

Hi Kurt,

This looks like a lot, but I have included two functions below that should
produce a well formed text file with the separator of your choice...

1. Get the contents of your sheet into an array like this:

myData = ActiveSheet.UsedRange.Value

2. Call the "ArrayToTextFile" function like this:

Call ArrayToTextFile("C:\MyTildeFile.txt", "~", myData)

Note that the first argument should be the full path and file name for your
text file.
Here are the two functions that produce it (correct for line wrap):

Public Function ArrayToTextFile(argFullNameDestinCSV As String, argSeparator
As String, argArray As Variant)
'write an array into a text file;
Dim iFileNumberDestin As Integer
Dim sLine As String
Dim lCol As Long
Dim lRow As Long
Dim sItem As String
iFileNumberDestin = FreeFile()
Open argFullNameDestinCSV For Append As #iFileNumberDestin
For lRow = 1 To UBound(argArray, 1)
For lCol = 1 To UBound(argArray, 2)
sItem = argArray(lRow, lCol)
If Trim(sItem) = "" Then
sLine = sLine & """" & sItem & """" & argSeparator
Else
sItem = CleanString(sItem)
sLine = sLine & """" & sItem & """" & argSeparator
End If
Next lCol
sLine = Left(sLine, Len(sLine) - 1) & vbCrLf
Print #iFileNumberDestin, sLine;
sLine = ""
Next lRow
Close #iFileNumberDestin
End Function

Public Function CleanString(argString As String) As String
'RETURNS A CLEANED UP A STRING
argString = Trim(argString)
argString = Application.WorksheetFunction.Clean(argString)
argString = Application.Clean(argString) 'sometimes has different results
argString = Replace(argString, vbCrLf, "")
argString = Replace(argString, vbTab, "")
argString = Replace(argString, """", "'")
argString = Replace(argString, Chr(44), Chr(32)) 'replace commas with a
space: 44=comma; 59=semicolon; 32=space
Do While InStr(argString, " ") 0
argString = Replace(argString, " ", " ") 'remove double spaces
Loop
CleanString = argString
End Function

Hope this helps! Post back if you need clarification...

"Kurt Barr" wrote:

I need to export an excel file into a tilde delimted format. Is there any way
that I can do this programmatically, either by having the tidle (~) already
in the file, or by having a macro insert it between the columns?