Solved

Excel VBA - formatting Cell color

Posted on 2004-04-28
18
45,072 Views
Last Modified: 2011-08-18
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
Comment
Question by:dba123
  • 10
  • 6
  • 2
18 Comments
 
LVL 9

Expert Comment

by:p_sie
ID: 10941073
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
 
LVL 1

Author Comment

by:dba123
ID: 10941328
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
 
LVL 1

Author Comment

by:dba123
ID: 10941336
Where did you get this info?  Is this VBA?
0
 
LVL 1

Author Comment

by:dba123
ID: 10941342
Or is that what you mean, run Excel macros to figure out where you got them
0
 
LVL 1

Author Comment

by:dba123
ID: 10941410
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
 
LVL 1

Author Comment

by:dba123
ID: 10941422
Your With statement totally ties up my server and processing continues forever on my asp page
0
 
LVL 1

Author Comment

by:dba123
ID: 10941423
Your With statement totally ties up my server and processing continues forever on my asp page
0
 
LVL 9

Expert Comment

by:p_sie
ID: 10941560
Sorry can't help you any further
0
 
LVL 1

Author Comment

by:dba123
ID: 10941853
darn..anyone else?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 2

Accepted Solution

by:
SlyW earned 500 total points
ID: 10942475
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
 
LVL 2

Expert Comment

by:SlyW
ID: 10942623
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
 
LVL 1

Author Comment

by:dba123
ID: 10943278
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
 
LVL 2

Expert Comment

by:SlyW
ID: 10943361
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
 
LVL 2

Expert Comment

by:SlyW
ID: 10943368
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
 
LVL 1

Author Comment

by:dba123
ID: 10943443
And is it possible to shove query results into multiple worksheets in one excel document with OWC?
0
 
LVL 2

Expert Comment

by:SlyW
ID: 10943569
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
 
LVL 1

Author Comment

by:dba123
ID: 10944085
thanks very much!
0
 
LVL 2

Expert Comment

by:SlyW
ID: 10944091
You are most welcome.

SlyW
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now