Saving as csv

klunshada

New Member
Joined
Jun 13, 2003
Messages
17
I have a spreadsheet with 900 columns each has just one entry. When I save as a csv, the csv file has the data with the line breaks but no commas.

Please help.

Thanks! Steve
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Fat Cat

Active Member
Joined
Nov 5, 2004
Messages
336
Do you mean 900 Rows ?

I think there is only 256 columns on a sheet in Excel.

Commas are only inserted in csv files to separate values from cells across a row
LineFeeds are used to seperate one row from the next
 

klunshada

New Member
Joined
Jun 13, 2003
Messages
17
Ya you are right. 900 rows. Makes sense now on the line feeds.

Is there a work around on this to get the commas in between each value?
 

KenCriss

Active Member
Joined
Jun 6, 2005
Messages
323

ADVERTISEMENT

See if this works. Select the cells to output and change the file name to suit your needs. Then run it. It will output your results with a "," at the end.
Code:
Private Sub Save_CSV()
    Dim delim As String, TxtArray() As String
    
    delim = ","
    
    MyFile = "c:\temp\test.txt"
    ReDim TxtArray(1 To Selection.Rows.Count)
    
    For Each cell In Selection.Resize(Selection.Rows.Count, 1)
    
        i = i + 1
    
        For Each subcell In Intersect(cell.EntireRow, Selection)
            TxtArray(i) = TxtArray(i) & subcell.Text & delim
        Next subcell

    Next cell
    
    Close #1
    Open MyFile For Output As #1
        For i = 1 To UBound(TxtArray)
            Print #1, TxtArray(i)
        Next i
    Close #1
        
    Msg = "File saved as:" & vbCrLf
    Msg = Msg & MyFile
    MsgBox Msg, vbOKOnly, "CSV File"
    
    
End Sub
 

KenCriss

Active Member
Joined
Jun 6, 2005
Messages
323

ADVERTISEMENT

Basically you have to put this code in a macro.

Open the workbook that you want it to run in.

Hit:

Alt+F11

From the Menu, choose Insert Module.

Paste a copy of this code, into the Code page that opens. Change code as appropriate.

In Excel, select a range of cells to output, then choose Tools/Macro and choose Save_CSV to run the macro.
 

klunshada

New Member
Joined
Jun 13, 2003
Messages
17
Worked great!!!!

One last question. Do you need to load the macro every time you open a new file or can you save it to use with any excel file?

Thank you very much.
 

KenCriss

Active Member
Joined
Jun 6, 2005
Messages
323
Glad to help.
You could put it in your personal.xls file or just keep it in that workbook and make sure you have that workbook open so you can use it. In other words, as long as the macro is in an active workbook or add-in you can use it. Personally, I keep frequently used macros in personal.xls.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,760
Messages
5,574,084
Members
412,566
Latest member
TexasTony
Top