Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel VBA - formatting Cell color

Posted on 2004-04-28
18
Medium Priority
?
45,158 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
LVL 2

Accepted Solution

by:
SlyW earned 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

963 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