Saving as csv

[klunshada]

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

 

[Fat Cat]

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]

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?

 

[Fat Cat]

Do you mean replace the line breaks with commas ?

 

[KenCriss]

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.

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

 

[klunshada]

Sorry that's a little over my head.

How do I run the code?

 

[KenCriss]

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]

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]

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.

 

[klunshada]

Awesome!!!

You have been a great help. Thanks again.