Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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? |
#2
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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? |
#3
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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? |
#4
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]() 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? |
#5
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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? |
#6
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]() 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? |
#7
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Parsing text containing a tilde "~"? | Excel Discussion (Misc queries) | |||
CANNOT Import comma delimted, quote qualifed text file into Excel. | Excel Discussion (Misc queries) | |||
Export Data to Text file with fix format | Excel Programming | |||
How do I export data in text format with minimum of spaces? | Excel Discussion (Misc queries) | |||
How to make text data export to excel in text format. | Excel Programming |