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?

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?

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?

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?

It doesn't generate an error, I actually have to hit CNTL-Break to get the
macro to end. And, then when I hit DEBUG, the line of code "Loop" is
highlighted yellow.

"XP" wrote:


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?

You could create a macro that exports the data the way you want.

Here are three sites that you could steal some code from:

Earl Kiosterud's Text Write program:
www.smokeylake.com/excel
(or directly: http://www.smokeylake.com/excel/text_write_program.htm)

Chip Pearson's:
http://www.cpearson.com/excel/imptext.htm

J.E. McGimpsey's:
http://www.mcgimpsey.com/excel/textfiles.html

(or maybe you could build your own formula and copy|paste into Notepad.)

Check out Earl's Text Write program first. It may do exactly what you want
right out of the box.

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?

--

Dave Peterson

Okay, I suspect that you may have a lot of rows in your source sheet that at
one time contained data but are now blank. This is causing your sheet size to
be artificially inflated.

You can test for this by activating your source sheet, then press "Ctrl" and
"End" at the same time. This jumps you to the last used cell in the sheet.
You will likely see that the used range in the sheet contains a lot of blank
rows and/or columns. These extra blanks are being copied into the array and
are also being written into the text file needlessly.

The fix for this is easy...go to the bottom of the sheet containing the data
you want in a text file. Then highlight and delete all rows BELOW your data,
even though they appear to be blank already. Then save your file. Repeat this
procedure for blank columns to the RIGHT of your data. Then save your file.

The next time you do a Ctrl + End it should take you to the last cell in
your sheet containing data.

The program should also run fine at this point; post back and let me know!

Hope this helps you out.

"Kurt Barr" wrote:

It doesn't generate an error, I actually have to hit CNTL-Break to get the
macro to end. And, then when I hit DEBUG, the line of code "Loop" is
highlighted yellow.

"XP" wrote:


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?

You were right about the file size. It was considerably over inflated. I
removed all the extra rows/columns, and ran the macro, and it still gets
caught in the loop. When I break out of the macro, and look at the text file
it is creating, it does not get past the first line. The first line that I
have in the spreadsheet is all Alpha, no Numeric. The next line down has both
Alpha and Numeric characters in it. If I take out the first line of data, and
then try to run the macro, it hangs up, and doesn't export anything.

"XP" wrote:


Okay, I suspect that you may have a lot of rows in your source sheet that at
one time contained data but are now blank. This is causing your sheet size to
be artificially inflated.

You can test for this by activating your source sheet, then press "Ctrl" and
"End" at the same time. This jumps you to the last used cell in the sheet.
You will likely see that the used range in the sheet contains a lot of blank
rows and/or columns. These extra blanks are being copied into the array and
are also being written into the text file needlessly.

The fix for this is easy...go to the bottom of the sheet containing the data
you want in a text file. Then highlight and delete all rows BELOW your data,
even though they appear to be blank already. Then save your file. Repeat this
procedure for blank columns to the RIGHT of your data. Then save your file.

The next time you do a Ctrl + End it should take you to the last cell in
your sheet containing data.

The program should also run fine at this point; post back and let me know!

Hope this helps you out.

"Kurt Barr" wrote:

It doesn't generate an error, I actually have to hit CNTL-Break to get the
macro to end. And, then when I hit DEBUG, the line of code "Loop" is
highlighted yellow.

"XP" wrote:


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?

That last loop is only trying to remove multiple blank spaces in your
original text, which you may not even need. Try commenting out the loop and
run the code; then check the file it produces and see what it looks like.

Comment the following lines in the "Clean" function:

'Do While InStr(argString, " ") 0
' argString = Replace(argString, " ", " ") 'remove double spaces
'Loop

Whether the cells contain alpha or numerics does not matter...

"Kurt Barr" wrote:

You were right about the file size. It was considerably over inflated. I
removed all the extra rows/columns, and ran the macro, and it still gets
caught in the loop. When I break out of the macro, and look at the text file
it is creating, it does not get past the first line. The first line that I
have in the spreadsheet is all Alpha, no Numeric. The next line down has both
Alpha and Numeric characters in it. If I take out the first line of data, and
then try to run the macro, it hangs up, and doesn't export anything.

"XP" wrote:


Okay, I suspect that you may have a lot of rows in your source sheet that at
one time contained data but are now blank. This is causing your sheet size to
be artificially inflated.

You can test for this by activating your source sheet, then press "Ctrl" and
"End" at the same time. This jumps you to the last used cell in the sheet.
You will likely see that the used range in the sheet contains a lot of blank
rows and/or columns. These extra blanks are being copied into the array and
are also being written into the text file needlessly.

The fix for this is easy...go to the bottom of the sheet containing the data
you want in a text file. Then highlight and delete all rows BELOW your data,
even though they appear to be blank already. Then save your file. Repeat this
procedure for blank columns to the RIGHT of your data. Then save your file.

The next time you do a Ctrl + End it should take you to the last cell in
your sheet containing data.

The program should also run fine at this point; post back and let me know!

Hope this helps you out.

"Kurt Barr" wrote:

It doesn't generate an error, I actually have to hit CNTL-Break to get the
macro to end. And, then when I hit DEBUG, the line of code "Loop" is
highlighted yellow.

"XP" wrote:


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?

That's perfect! It worked like a charm! Thanks for all the help with this!

"XP" wrote:


That last loop is only trying to remove multiple blank spaces in your
original text, which you may not even need. Try commenting out the loop and
run the code; then check the file it produces and see what it looks like.

Comment the following lines in the "Clean" function:

'Do While InStr(argString, " ") 0
' argString = Replace(argString, " ", " ") 'remove double spaces
'Loop

Whether the cells contain alpha or numerics does not matter...

"Kurt Barr" wrote:

You were right about the file size. It was considerably over inflated. I
removed all the extra rows/columns, and ran the macro, and it still gets
caught in the loop. When I break out of the macro, and look at the text file
it is creating, it does not get past the first line. The first line that I
have in the spreadsheet is all Alpha, no Numeric. The next line down has both
Alpha and Numeric characters in it. If I take out the first line of data, and
then try to run the macro, it hangs up, and doesn't export anything.

"XP" wrote:


Okay, I suspect that you may have a lot of rows in your source sheet that at
one time contained data but are now blank. This is causing your sheet size to
be artificially inflated.

You can test for this by activating your source sheet, then press "Ctrl" and
"End" at the same time. This jumps you to the last used cell in the sheet.
You will likely see that the used range in the sheet contains a lot of blank
rows and/or columns. These extra blanks are being copied into the array and
are also being written into the text file needlessly.

The fix for this is easy...go to the bottom of the sheet containing the data
you want in a text file. Then highlight and delete all rows BELOW your data,
even though they appear to be blank already. Then save your file. Repeat this
procedure for blank columns to the RIGHT of your data. Then save your file.

The next time you do a Ctrl + End it should take you to the last cell in
your sheet containing data.

The program should also run fine at this point; post back and let me know!

Hope this helps you out.

"Kurt Barr" wrote:

It doesn't generate an error, I actually have to hit CNTL-Break to get the
macro to end. And, then when I hit DEBUG, the line of code "Loop" is
highlighted yellow.

"XP" wrote:


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?

Great!

I'm glad I could help you out!



"Kurt Barr" wrote:

That's perfect! It worked like a charm! Thanks for all the help with this!

"XP" wrote:


That last loop is only trying to remove multiple blank spaces in your
original text, which you may not even need. Try commenting out the loop and
run the code; then check the file it produces and see what it looks like.

Comment the following lines in the "Clean" function:

'Do While InStr(argString, " ") 0
' argString = Replace(argString, " ", " ") 'remove double spaces
'Loop

Whether the cells contain alpha or numerics does not matter...

"Kurt Barr" wrote:

You were right about the file size. It was considerably over inflated. I
removed all the extra rows/columns, and ran the macro, and it still gets
caught in the loop. When I break out of the macro, and look at the text file
it is creating, it does not get past the first line. The first line that I
have in the spreadsheet is all Alpha, no Numeric. The next line down has both
Alpha and Numeric characters in it. If I take out the first line of data, and
then try to run the macro, it hangs up, and doesn't export anything.

"XP" wrote:


Okay, I suspect that you may have a lot of rows in your source sheet that at
one time contained data but are now blank. This is causing your sheet size to
be artificially inflated.

You can test for this by activating your source sheet, then press "Ctrl" and
"End" at the same time. This jumps you to the last used cell in the sheet.
You will likely see that the used range in the sheet contains a lot of blank
rows and/or columns. These extra blanks are being copied into the array and
are also being written into the text file needlessly.

The fix for this is easy...go to the bottom of the sheet containing the data
you want in a text file. Then highlight and delete all rows BELOW your data,
even though they appear to be blank already. Then save your file. Repeat this
procedure for blank columns to the RIGHT of your data. Then save your file.

The next time you do a Ctrl + End it should take you to the last cell in
your sheet containing data.

The program should also run fine at this point; post back and let me know!

Hope this helps you out.

"Kurt Barr" wrote:

It doesn't generate an error, I actually have to hit CNTL-Break to get the
macro to end. And, then when I hit DEBUG, the line of code "Loop" is
highlighted yellow.

"XP" wrote:


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?