save as csv

[Henke]

Hi,
Im having trouble saving excelfiles as csv. The excelfile contains 8
colums therefore I want 8 semicolons on each row in the csvfile. But
the last column sometimes just have an header and no data in the
cells. And then when I save and look in the csv-file there is 8
semicolons in the first 15 rows but in all of the other 2000 its just
7. Why? what am I doing wrong? I have windows xp and office 2000 and
Im almost certain that I didnt have this problem two years ago....

Best regards

Henke

 

[Jim Rech]

This is a long standing problem with Excel:

http://support.microsoft.com/default.aspx?scid=kb;en-us;77295&Product=xlw

I'd suggest a macro to get the result you want:

''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)
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
Print #1, CurrTextStr
Next
Close #1
End If
End Sub

 

[NC]

If you have eight columns containing data you should have seven separators
between the columns. The number of separators is always one less than the
number of columns. If you think of having two columns then you need only one
separator divide the data in each row into its two parts.

Therefore it looks like in the first 15 rows there are nine columns of data
and in the remaining rows there are eight.

Excel looks at each row separately and on each row will only use as many
columns as contain data.

Neal