Solved

Excel VBA - formatting Cell color

Posted on 2004-04-28
18
45,063 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
Comment Utility
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
Comment Utility
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
Comment Utility
Where did you get this info?  Is this VBA?
0
 
LVL 1

Author Comment

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

Author Comment

by:dba123
Comment Utility
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
Comment Utility
Your With statement totally ties up my server and processing continues forever on my asp page
0
 
LVL 1

Author Comment

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

Expert Comment

by:p_sie
Comment Utility
Sorry can't help you any further
0
 
LVL 1

Author Comment

by:dba123
Comment Utility
darn..anyone else?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 2

Accepted Solution

by:
SlyW earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
And is it possible to shove query results into multiple worksheets in one excel document with OWC?
0
 
LVL 2

Expert Comment

by:SlyW
Comment Utility
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
Comment Utility
thanks very much!
0
 
LVL 2

Expert Comment

by:SlyW
Comment Utility
You are most welcome.

SlyW
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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…
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…

763 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

7 Experts available now in Live!

Get 1:1 Help Now