RGB values Macro

[Alan_Hook]

Hello All,

I have been scouring the boards for a solution to my problem and I have found a similar problem and solution but it is does not work with my specific case/workbook.

I have a program created in XNA .net framework which will take an image and break it down into individual pixel RGB values and create a .csv file with the RGB in each cell for each pixel. I would like to cange the font colour and background of the cell to match the value in the cell. The values in each cell are written 0,0,0 > 255,255,255. The problem is similar to this post http://excel.bigresource.com/Track/excel-Y30kLP4F/ but i need to affect the cell containing the value, and the values are written in each cell instead or separated across 3 columns.

I want to recreate the image as a coloured data set with each cell representative of a pixel or an image, containing the RGB value and background colour or the code conatined in the cell...

Any ideas

 

[Richard Schollar]

CSV files don't have any formatting (as they are just text files). Why would you want this anyway if you already have the RGB value? Incidentally, unless you are using xl2007 or higher, the max number of RGB values that Excel can display is 56.

 

[Ruddles]

Alan, just so we can understand what we're playing with, can you post a sample of the CSV file? Does it look like this:-

0,0,0
255,0,0
255,255,255

i.e. three values per line, so they're imported into three columns in Excel?

Or does it look like this:-

"0,0,0"
"255,0,0"
"255,255,255"

i.e. everything ends up in column A when imported into Excel?

 

[Alan_Hook]

I am using Excel 2007 so the palette restrictions shouldn't be a problem. I intended to copy the data from the .csv into a .xlsx/convert the .csv into a sheet.
I am trying to pull together a demonstration for students that shows jpeg images as data sets, to show them that rasterized images are just representations of data and can be created as data sheets.
Any help or advise is more than welcome.

 

[Richard Schollar]

OK gotcha. Ruddles has asked a pertinent question - also, are your system separators comma (,) or semi-colons (; )? In principal, you should be able to open the file up and have Excel convert the cell values to a fill colour, then reduce the Zoom (substantially) so you can see at least a semblance of the image.

Does depend on how the values are stored in the csv file though - hopefully they are surrounded by "" if you use commas as system separators too.

 

[Ruddles]

Okay, I'll have a go... create a new workbook, right click the tab for Sheet1 and select View Code, then replace anything which is already in the code window with this:-

Option Explicit
 
Public Sub ImportRGBvalues()
 
  Dim iPtr As Integer
  Dim sFileName As String
  Dim intFH As Integer
 
  Dim iRow As Long
  Dim iRec As Long
  Dim sRec As String
 
  Dim sTime As Date
 
  Dim arrRGB As Variant
 
  iPtr = InStrRev(ActiveWorkbook.FullName, ".")
  sFileName = Left(ActiveWorkbook.FullName, iPtr - 1) & ".csv"
  sFileName = Application.GetOpenFilename(FileFilter:="Comma separated values (*.csv), *.csv")
  If sFileName = "False" Then Exit Sub
 
  Close
  intFH = FreeFile()
  Open sFileName For Input As intFH
 
  Range("A1").ClearContents
  Range("A1").NumberFormat = "@"
  sTime = Now()
 
  iRow = 0
   Do Until EOF(intFH)
    Line Input #intFH, sRec
    iRow = iRow + 1
    Cells(iRow, 1) = Replace(sRec, Chr(34), "")
    iRec = iRec + 1
    DoEvents
  Loop
 
  Close intFH
 
  For iRow = 1 To iRec
    arrRGB = Split(Cells(iRow, 1), ",")
    Cells(iRow, 2).Interior.Color = RGB(arrRGB(0), arrRGB(1), arrRGB(2))
  Next iRow
 
  MsgBox "Finished: " & CStr(iRec) & " records imported" & Space(10) & vbCrLf & vbCrLf _
       & "Run time: " & Format(Now() - sTime, "hh:nn:ss") & Space(10), _
       vbOKOnly + vbInformation
 
End Sub

Then from the worksheet, go Developer > Macros, select ImportRGBvalues and click Run.

Is that the sort of effect you were trying to create?

 

[Alan_Hook]

The code writes the values of each pixel as a quotes comma serperated value, seperated by commas, with a new line for each row -

"255,255,255","255,255,255","255,255,255","216,0,0","216,0,0","216,0,0","216,0,0","216,0,0","255,255,255","255,255,255","255,255,255","255,255,255"
"255,255,255","255,255,255","216,0,0","216,0,0","216,0,0","216,0,0","216,0,0","216,0,0","216,0,0","216,0,0","216,0,0","255,255,255"
"255,255,255","255,255,255","112,104,0","112,104,0","112,104,0","248,171,0","248,171,0","112,104,0","248,171,0","255,255,255","255,255,255","255,255,255"
"255,255,255","112,104,0","248,171,0","112,104,0","248,171,0","248,171,0","248,171,0","112,104,0","248,171,0","248,171,0","248,171,0","255,255,255"
"255,255,255","112,104,0","248,171,0","112,104,0","112,104,0","248,171,0","248,171,0","248,171,0","112,104,0","248,171,0","248,171,0","248,171,0"
"255,255,255","112,104,0","112,104,0","248,171,0","248,171,0","248,171,0","248,171,0","112,104,0","112,104,0","112,104,0","112,104,0","255,255,255"
"255,255,255","255,255,255","255,255,255","248,171,0","248,171,0","248,171,0","248,171,0","248,171,0","248,171,0","248,171,0","255,255,255","255,255,255"
"255,255,255","255,255,255","112,104,0","112,104,0","216,0,0","112,104,0","112,104,0","112,104,0","255,255,255","255,255,255","255,255,255","255,255,255"
"255,255,255","112,104,0","112,104,0","112,104,0","216,0,0","112,104,0","112,104,0","216,0,0","112,104,0","112,104,0","112,104,0","255,255,255"
"112,104,0","112,104,0","112,104,0","112,104,0","216,0,0","216,0,0","216,0,0","216,0,0","112,104,0","112,104,0","112,104,0","112,104,0"
"248,171,0","248,171,0","112,104,0","216,0,0","248,171,0","216,0,0","216,0,0","248,171,0","216,0,0","112,104,0","248,171,0","248,171,0"
"248,171,0","248,171,0","248,171,0","216,0,0","216,0,0","216,0,0","216,0,0","216,0,0","216,0,0","248,171,0","248,171,0","248,171,0"
"248,171,0","248,171,0","216,0,0","216,0,0","216,0,0","216,0,0","216,0,0","216,0,0","216,0,0","216,0,0","248,171,0","248,171,0"
"255,255,255","255,255,255","216,0,0","216,0,0","216,0,0","255,255,255","255,255,255","216,0,0","216,0,0","216,0,0","255,255,255","255,255,255"
"255,255,255","112,104,0","112,104,0","112,104,0","255,255,255","255,255,255","255,255,255","255,255,255","112,104,0","112,104,0","112,104,0","255,255,255"
"112,104,0","112,104,0","112,104,0","112,104,0","255,255,255","255,255,255","255,255,255","255,255,255","112,104,0","112,104,0","112,104,0","112,104,0"

This is the .csv for an 8bit mario. For Mario it was easy enough to produce the sheet by hand but i want to work on a larger scale with 300x400 pixel images so i'm looking for a way to automate this.

Ruddles, the code doesn't seem to work, it imports each line of the .csv into the first column and colours some (3) cells but in the adjacent column

 

[Ruddles]

Excellent - that's exactly what I needed to know! Try this:-

[COLOR=#000000]Option Explicit[/COLOR]
 
[COLOR=#000000]Public Sub ImportRGBvalues()[/COLOR]
 
[COLOR=#000000]  Dim iPtr As Integer
  Dim sFileName As String
  Dim intFH As Integer
  
  Dim iRow As Long
  Dim iRec As Long
  Dim sRec As String
  
  Dim sTime As Date
  
  Dim arrRGB As Variant
  
  iPtr = InStrRev(ActiveWorkbook.FullName, ".")
  sFileName = Left(ActiveWorkbook.FullName, iPtr - 1) & ".csv"
  sFileName = Application.GetOpenFilename(FileFilter:="Comma-separated values (*.csv), *.csv")
  If sFileName = "False" Then Exit Sub
  
  Close
  intFH = FreeFile()
  Open sFileName For Input As intFH
   
  Columns("A").ClearContents
  Columns("A").NumberFormat = "@"
  Columns("B").Interior.Color = xlNone
  sTime = Now()
    
  iRow = 0
   Do Until EOF(intFH)
    Input #intFH, sRec
    iRow = iRow + 1
    Cells(iRow, 1) = Replace(sRec, Chr(34), "")
    iRec = iRec + 1
    DoEvents
  Loop
  
  Close intFH
   
  For iRow = 1 To iRec
    arrRGB = Split(Cells(iRow, 1), ",")
    Cells(iRow, 2).Interior.Color = RGB(arrRGB(0), arrRGB(1), arrRGB(2))
  Next iRow
  
  MsgBox "Finished: " & CStr(iRec) & " records imported" & Space(10) & vbCrLf & vbCrLf _
       & "Run time: " & Format(Now() - sTime, "hh:nn:ss") & Space(10), _
       vbOKOnly + vbInformation[/COLOR]
 
[COLOR=#000000]End Sub[/COLOR]