| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SaveAs CSV - Pipe Delimited
Hello,
My system settings are set to have the list seperator = ( | ) , not ( , ). But when I use the following command in VB, the output file still uses commas - WS.SaveAs "c:\filename.csv", xlCSV I presume there is a listing of valid "FileFormats". If there is a link, that would also be appreciated. Thank you |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SaveAs CSV - Pipe Delimited
NVM.
I found the link to valid file formats... http://msdn.microsoft.com/en-us/libr...ileformat.aspx Looks like it can't be done with that line of code. "stevieb" wrote: Hello, My system settings are set to have the list seperator = ( | ) , not ( , ). But when I use the following command in VB, the output file still uses commas - WS.SaveAs "c:\filename.csv", xlCSV I presume there is a listing of valid "FileFormats". If there is a link, that would also be appreciated. Thank you |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SaveAs CSV - Pipe Delimited
stevieb wrote:
I presume there is a listing of valid "FileFormats". If there is a link, that would also be appreciated. From the VBA Help (find help for Workbook.SaveAs - click on highlighted FileFormat parameter - click on highlighted XlFileFormat-value): XlFileFormat can be any of the following XlFileFormat-constants: xlCSV xlCSVMSDOS xlCurrentPlatformText xlDBF3 xlDIF xlExcel2FarEast xlExcel4 xlAddIn xlCSVMac xlCSVWindows xlDBF2 xlDBF4 xlExcel2 xlExcel3 xlExcel4Workbook xlExcel5 xlExcel7 xlExcel9795 xlHtml xlIntlAddIn xlIntlMacro xlSYLK xlTemplate xlTextMac xlTextMSDOS xlTextPrinter xlTextWindows xlUnicodeText xlWebArchive xlWJ2WD1 xlWJ3 xlWJ3FJ3 xlWK1 xlWK1ALL xlWK1FMT xlWK3 xlWK3FM3 xlWK4 xlWKS xlWorkbookNormal xlWorks2FarEast xlWQ1 xlXMLSpreadsheet ------ If none of those works, you will have to do it manually: Loop over all rows, assemble a line with a loop over all columns and put it to your outfile: ''''''''''''''''''''''''''''''' Const myDelimiter As String = "|" Const lastColumn As Long = 12 ' whatever you like Dim lastRow as Long Dim fileHandle as Long Dim strLine as String Dim row as Long, col as Long ' Determine the highest used row number in the imported data lastRow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).row Open "yourfile.csv" For Binary Access Write As #fileHandle strLine = "" For row = 1 to lastRow For col = 1 to lastColumn strLine = strLine & ActiveSheet.Cells(row, col).Value & myDelimiter Next col ' remove last delimiter if you want to and append linebreak strLine = Left (strLine, Len(strLine) - Len (myDelimiter)) & vbCrLf Put #fileHandle, , strLine Next row Close #fileHandle ''''''''''''''''''''''''''''''' HTH, Lars |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SaveAs CSV - Pipe Delimited
stevieb wrote:
Looks like it can't be done with that line of code. My other post contains a solution below the list - don't forget to scroll to the bottom ;) Best Regards, Lars |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SaveAs CSV - Pipe Delimited
Thanks Lars.
The solution you provided was going to be my last resort if my original request could not be made. I was under the impression it could be done using a different 'File Format.' INstead of the 'brute force' type method. "Lars Uffmann" wrote: stevieb wrote: I presume there is a listing of valid "FileFormats". If there is a link, that would also be appreciated. From the VBA Help (find help for Workbook.SaveAs - click on highlighted FileFormat parameter - click on highlighted XlFileFormat-value): XlFileFormat can be any of the following XlFileFormat-constants: xlCSV xlCSVMSDOS xlCurrentPlatformText xlDBF3 xlDIF xlExcel2FarEast xlExcel4 xlAddIn xlCSVMac xlCSVWindows xlDBF2 xlDBF4 xlExcel2 xlExcel3 xlExcel4Workbook xlExcel5 xlExcel7 xlExcel9795 xlHtml xlIntlAddIn xlIntlMacro xlSYLK xlTemplate xlTextMac xlTextMSDOS xlTextPrinter xlTextWindows xlUnicodeText xlWebArchive xlWJ2WD1 xlWJ3 xlWJ3FJ3 xlWK1 xlWK1ALL xlWK1FMT xlWK3 xlWK3FM3 xlWK4 xlWKS xlWorkbookNormal xlWorks2FarEast xlWQ1 xlXMLSpreadsheet ------ If none of those works, you will have to do it manually: Loop over all rows, assemble a line with a loop over all columns and put it to your outfile: ''''''''''''''''''''''''''''''' Const myDelimiter As String = "|" Const lastColumn As Long = 12 ' whatever you like Dim lastRow as Long Dim fileHandle as Long Dim strLine as String Dim row as Long, col as Long ' Determine the highest used row number in the imported data lastRow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).row Open "yourfile.csv" For Binary Access Write As #fileHandle strLine = "" For row = 1 to lastRow For col = 1 to lastColumn strLine = strLine & ActiveSheet.Cells(row, col).Value & myDelimiter Next col ' remove last delimiter if you want to and append linebreak strLine = Left (strLine, Len(strLine) - Len (myDelimiter)) & vbCrLf Put #fileHandle, , strLine Next row Close #fileHandle ''''''''''''''''''''''''''''''' HTH, Lars |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Pipe-Delimited CSv File | Excel Discussion (Misc queries) | |||
| Retrieving Pipe (|) delimited text file into ADO Recordset | Excel Programming | |||
| How can I convert tab delimited files to pipe delimited? | Excel Discussion (Misc queries) | |||
| Save file as text pipe delimited | Excel Programming | |||
| VBA macro (pipe delimited) code help | Excel Programming | |||
Linear Mode
