Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 45223
  • Last Modified:

Excel VBA - formatting Cell color

I am using a Spreadsheet object in conjunction with my ASP code.  I found various properties for changing an Excel spreadsheet but can't figure out how to change the cell's background.

Set objSpreadsheet = Server.CreateObject("OWC.Spreadsheet")


            objSpreadsheet.Cells(p,1).font.size = "12"
            objSpreadsheet.Cells(p,1).font.bold = True
            objSpreadsheet.Cells(p,1).font.italic = True
            objSpreadsheet.Cells(p,1).font.color = vbRed

I'm not sure if these properties are coming straight from Excel VBA or what.  I also don't know what vb is in vbRed because when I print this to the screen doing a Response.Write in ASP, the value behind vbRed is 255 which is probably the R in RGB but I still don't get it.  I can't just plug in a color # like #ffffff.  For instance jSpreadsheet.Cells(p,1).font.color = #RRRFFF won't work

so my 3 questions are:

1) Is this Excel VBA when I am using the OWC.Spreadsheet object?
2) How to change the cell's background color
3) What is vbRed
0
dba123
Asked:
dba123
  • 10
  • 6
  • 2
1 Solution
 
p_sieCommented:
1 Why not use Set objSpreadsheet = Server.CreateObject("Excel.Application")

2
objSpreadsheet.Cells(p,1).Select
With Selection.Interior
        .ColorIndex = 3
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
    End With

3 objSpreadsheet.Cells(p,1).Select
    Selection.Font.ColorIndex = 3 (3 = red)

You can get these codes by recording a macro in Excel and then look at the code
0
 
dba123Author Commented:
1 ) Because Excel.Application is bad.  It is resource intensive, creates one  or more a running instances of Excel on our server.  I am not going that route.  Using COM is much more efficient.



can this be done in a one liner without using a With statment?

objSpreadsheet.Cells(p,1).Select
With Selection.Interior
        .ColorIndex = 3
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
    End With
0
 
dba123Author Commented:
Where did you get this info?  Is this VBA?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
dba123Author Commented:
Or is that what you mean, run Excel macros to figure out where you got them
0
 
dba123Author Commented:
Then why doesn't this work:

            objSpreadsheet.Cells(p,1).ColorIndex = 3
            objSpreadsheet.Cells(p,1).Pattern = xlSolid
            objSpreadsheet.Cells(p,1).PatternColorIndex = xlAutomatic
0
 
dba123Author Commented:
Your With statement totally ties up my server and processing continues forever on my asp page
0
 
dba123Author Commented:
Your With statement totally ties up my server and processing continues forever on my asp page
0
 
p_sieCommented:
Sorry can't help you any further
0
 
dba123Author Commented:
darn..anyone else?
0
 
SlyWCommented:
Take a look at the following link:

http://msdn.microsoft.com/library/en-us/off2000/html/octocmsowcobjectmodels.asp?frame=true

It is Microsoft's reference for Office Web Components (the OWC in OWC.Spreadsheet).

The following code changes the first cell color to red:

<html>
  <head>
            <script language="VBScript">
                  Sub ColorRed()

                        document.SS1.Cells(1,1).Interior.Color = RGB(255,0,0)

                  End Sub
            </script>
  </head>
  <body>
        <button id="bt1" onclick="ColorRed()">Colorize</button>
        <object id="SS1" classid=CLSID:0002E510-0000-0000-C000-000000000046 style="width:49%;height:350"></object>
  </body>
</html>

Notice, however, that this is all client based as in the logic behind ColorRed() is contained and executed within the Browser.  Although you can instantiate OWC objects from within ASP, I have not found any Save or SaveAs methods for the Spreadsheet object.

Hope this helps,
Sly
0
 
SlyWCommented:
Oh yeah, the vb in vbRed is simply used as a moniker indicating that vbRed is a Visual Basic constant representing the color Red.  You will notice that I used the RGB() function to create the color red.  It takes three parameters: the red component, green component, and blue component.

In order to use this you will probably need to translate your #rrggbb value into the individual decimal parts.

Something like the following should work (VBScript):

Function HexToDec(sHex)
  Const csHEXChars = "0123456789abcdef"
  Dim DecValue, iIndex, sChar

  DecValue = 0
  For iIndex = 0 To (Len(sHex) - 1)
    sChar = Mid(sHex, Len(sHex) - iIndex, 1)
    DecValue = DecValue + ((InStr(1, csHEXChars, sChar) - 1) * (16 ^ iIndex))
  Next

  HexToDec = DecValue
End Function
0
 
dba123Author Commented:
It's really weird.  I can go into Photoshop, pick a color from the color grid and then take the RGB values from it, shove it into my line here below and the color comes up almost close but sometimes much lighter or darker than what shows in photoshop.  Also you are using a function where I just used the one liner.  I assume it is because you are using straight VB and not ASP with VBSript is why you used the function ColorRed()?

objSpreadsheet.Cells(p,1).Interior.Color = RGB(229,229,231) - produces close-to color when compared to the same color in Photoshop

I don't know why the colors are not exact.  I assume Excel can't just take any color and is restricted to 256?
0
 
SlyWCommented:
This may sound strange, but try switching around the values passed into RGB.  Since two of the values are the same, you should need only try:

RGB(229,229,231) '::: From above
RGB(229,231,229)
RGB(231,229,229)

And see if any of those are closer.  I have experienced where the decimal value, vs. the Hex value, are reversed for some reason.  Never took the time to figure out why, just accpeted it.

It is entirely possible that the Excel OWC is limited to 256 colors.  Plug the code described above into a .ASP or .HTML file and then view it in IE you will see the Excel OWC in action.  From there, right click on a cell and select "Property Toolbox" from the right-click menu.  If you click on the little down arrow next to the paint can (for Fill) you will see that the OWC only defines 40 colors and you cannot add custom colors.  I would hate to think that the OWC limits you to 40 colors, but it wouldn't necessarily surprise me.

Hope this helps,
SlyW
0
 
SlyWCommented:
Oh yeah, the function.  I used the function to fire the change on an event (i.e. the button click).  No other reason.  A one liner should work.
0
 
dba123Author Commented:
And is it possible to shove query results into multiple worksheets in one excel document with OWC?
0
 
SlyWCommented:
I have found no way to access multiple worksheets in one excel document using OWC.  This doesn't mean it cannot be done, only that I have found zero documentation telling me how to do it.

Sorry,
SlyW
0
 
dba123Author Commented:
thanks very much!
0
 
SlyWCommented:
You are most welcome.

SlyW
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 10
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now