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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

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
326
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
326
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
326
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.
 

Forum statistics

Threads
1,171,652
Messages
5,876,705
Members
433,206
Latest member
james_y

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top