Exporting Colored Cells Without Content from Excel to CSV

Question

I have a simple Excel spreadsheet with headers and rows like the example below. All of the cells under columns 1,2, and 3 have a cell color but do not have any cell content.

If, as the example below shows, column3 exists for Item2, then the appropriate cell for Item2 under Col3 is a different color than the other cells.

     | Col1 | Col2 | Col3 |
---------------------------
Item1|      |      |      |
Item2|      |      |      |

My questions are: is there a way to export the spreadsheet to a CSV file so that any cell colored differently from the other cells is preserved in the CSV file? If not, is there a way to get Excel to export the actual cell background colors into the CSV?

I plan on importing the CSV into a database. I could run if/then cases for any kind content I can force Excel to export to CSV, like a bit (1) or varchar (FF0000).

Thank you for your help! It's much appreciated!

Answer 1

CSV is text only and doesn't contain any color or any other formatting. You could, however, store the color value in a string representation along side the item.

Answer 2

I think you are going to have to use a macro to get this done the way you have the problem presented.

The way I usually handle this type of problem is to use conditional formatting with values in a cell. for instance if I want the cell to be yellow, I will write a rule that say if it see a value of "Y", then fill the cell and color the text yellow. That way things look the same, but I can export/import nicely.

Hope this helps

Answer 3

I have used csv files with Excel many times over the years and usually found Excel native csv handling very fickle. I tend to write my own csv handlers.

Here's an example. You will need to modify it to suit your needs.

Sub SaveAsCsv(ws As Worksheet, Name As String)
    Dim fso As FileSystemObject
    Dim fl As TextStream
    Dim rw As Long
    Dim ln As String
    Dim Name as str 

    On Error GoTo EH

    Name = "Your Path and File name"    
    Set fso = New FileSystemObject
    Set fl = fso.CreateTextFile(Name, True)
    For each rw in ws.UsedRange.Rows
        ' Modify this to match your Items colunmn number
        If rw.Cells(1,ItemColumnNumber) <> "" then
            ln = ""
            ' Modify this to match your colunmn numbers
            For col = Col1 To LastCol
                ln = ln & rw.Cells(1,col).Interior.Color & ","
                ' or process the colour into whatever code you want                    
            Next
            fl.WriteLine Left(ln, Len(ln) - 1)
        End If
    Next rw
EH:
    On Error Resume Next
    If Not fl Is Nothing Then fl.Close
    Set fl = Nothing
    Set fso = Nothing

End Sub

It includes an early bound reference to Microsoft Scripting Runtime, so set the reference or change to late bound if you want. Add any error handling you need

Note: iterating over cell ranges like this can be slow esp for large ranges. But given that you have to access the cell formats I don't see any other choice.