Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CSV export, deliminating character
I am trying to export an excel file to csv. But unlike the name
"comma-separated..." suggests excel uses semicolons to seperate my cells. I would need the cells separated by commas rather. As some of my cells contain semicolons as data, the workaround of replacing all the ; with , in wordpad does not work. I already tried to change the list-seperate character in windows-regional options but that didn't work either. Thanks in advance... Hannes |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
CSV export, deliminating character
Changing your regional settings list separator should definitely do it,
Hannes. It does for me. You may have to restart Excel after the switch, however, to get it to recognize the change. If all else fails you can use a macro to create the CSV. See below. -- Jim Rech Excel MVP 'No quotes around strings ''Outputs the selection if more than one cell is selected, else entire sheet Sub OutputActiveSheetAsTrueCSVFile() Dim SrcRg As Range Dim CurrRow As Range Dim CurrCell As Range Dim CurrTextStr As String Dim ListSep As String Dim FName As Variant FName = Application.GetSaveAsFilename("", "CSV File (*.csv), *.csv") If FName < False Then 'ListSep = Application.International(xlListSeparator) ListSep = "," 'use this to force commas as separator regardless of regional settings If Selection.Cells.Count 1 Then Set SrcRg = Selection Else Set SrcRg = ActiveSheet.UsedRange End If Open FName For Output As #1 For Each CurrRow In SrcRg.Rows CurrTextStr = "" For Each CurrCell In CurrRow.Cells CurrTextStr = CurrTextStr & CurrCell.Value & ListSep Next While Right(CurrTextStr, 1) = ListSep CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) - 1) Wend Print #1, CurrTextStr Next Close #1 End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 - Formatting text in cell (character by character) | Excel Discussion (Misc queries) | |||
XML special character export | Excel Discussion (Misc queries) | |||
Deliminating Text | Excel Discussion (Misc queries) | |||
export re-order input fields to export file [csv] | Excel Worksheet Functions | |||
Excel-Match 1st text character in a string to a known character? | Excel Worksheet Functions |