Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Transform Text

Posted on 2011-11-01
32
Medium Priority
?
647 Views
Last Modified: 2012-06-27
Hello,

is there an equivalent of CSS's text-transform: none, uppercase, lowercase, capitalize in "mso" format for Excel?

I am trying to import a formatted HTML document in Excel.

Thank You!
Cyril
0
Comment
Question by:CaptainCyril
  • 15
  • 7
  • 7
  • +2
31 Comments
 
LVL 34

Expert Comment

by:Rob Henson
ID: 37062331
See the blocks of code below:

Sub ChangeUpper()

        For Each cell In Selection
            If cell.HasFormula = False And cell.Value <> "" Then
            OT = cell.Value
            NT = StrConv(OT, vbUpperCase)
            Let cell.Value = NT
            Else
            For Count = 1 To 26
            F = Choose(Count, "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", _
            "r", "s", "t", "u", "v", "w", "x", "y", "z")
            R = Choose(Count, "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", _
            "R", "S", "T", "U", "V", "W", "X", "Y", "Z")
    
            Selection.Replace What:=F, Replacement:=R, LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False

            Next Count
            End If
        Next cell
        
End Sub

Sub ChangeLower()

        For Each cell In Selection
            If cell.HasFormula = False And cell.Value <> "" Then
            OT = cell.Value
            NT = StrConv(OT, vbLowerCase)
            Let cell.Value = NT
            Else
            For Count = 1 To 26
            F = Choose(Count, "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", _
            "r", "s", "t", "u", "v", "w", "x", "y", "z")
            R = Choose(Count, "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", _
            "r", "s", "t", "u", "v", "w", "x", "y", "z")
    
            Selection.Replace What:=F, Replacement:=R, LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False

            Next Count
            End If
        Next cell

End Sub

Sub ChangeProper()

        For Each cell In Selection
        If cell.HasFormula = False And cell.Value <> "" Then
            OT = cell.Value
            NT = StrConv(OT, vbProperCase)
            Let cell.Value = NT
        Else
        End If
        Next cell
        
End Sub

Open in new window

0
 
LVL 36

Expert Comment

by:Norie
ID: 37062438
Cyril

Why do you need that to import the document?
0
 
LVL 27

Author Comment

by:CaptainCyril
ID: 37062479
I am not looking for something to do in Excel. I wish to change the CSS of the HTML files.

I have a software that generates formatted HTML files. The font, size, styles, colors, alignments are all imported properly in Excel except for text-transform.

So in case Excel does not have that options in "mso-text-transform" (or similar) style coding, that means the software will have to be changed.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Author Comment

by:CaptainCyril
ID: 37062524
Maybe I did not make myself clear.

Here is a sample that a multiple-line in a TD in HTML export to a single cell in Excel:

<br style="mso-data-placement:same-cell;">

I could not find a reference manual for the mso standard style sheet.
0
 
LVL 36

Expert Comment

by:Norie
ID: 37062568
Cyril

So, you are importing this document into Excel but Excel isn't handling some of the formatting generated by your software?
0
 
LVL 27

Author Comment

by:CaptainCyril
ID: 37062595
It handles all kinds of formatting except "text-transform: lowercase"
0
 
LVL 36

Expert Comment

by:Norie
ID: 37062682
Cyril

So all you are looking for is the mso equivalent of transform-text?
0
 
LVL 27

Author Comment

by:CaptainCyril
ID: 37062720
Yes.
0
 
LVL 36

Expert Comment

by:Norie
ID: 37062847
Well I don't know where you'll find that I'm afraid.

I've looked everywhere I could think of and even downloaded a few things that looked promising.

This is the only think I could find that appears to include mso styles.

http://suika.fam.cx/~wakaba/wiki/sw/n/CSS++%E7%89%B9%E6%80%A7

Actually here's another one:

http://devfiles.myopera.com/articles/532/cssproplist-url.htm

Couldn't see anything on either list but I could easily have missed something.

0
 
LVL 27

Author Comment

by:CaptainCyril
ID: 37062974
I searched for 30 minutes for mso HTML style references and I could not find it. Why is it poorly documented? :-)

I am changing the program so that I change the case by code and not by style. All the browsers support this CSS except Excel :-(

Thanks a lot for your help. I will keep this open for a while.
0
 
LVL 36

Expert Comment

by:Norie
ID: 37063028
No problem.

I wish I knew why half of MS stuff is poorly documented and/or hard to find.

Bet it's there somewhere and someone'll pop along with a link soon.
0
 
LVL 27

Author Comment

by:CaptainCyril
ID: 37063049
I suffer with MS products. I am not able to disable MS-Graph or 2007 charts from refreshing while I change the properties in PowerPoint. It works find in Excel.

No wonder Google and Apple are beating MS.
0
 
LVL 7

Expert Comment

by:DanielleFavreau
ID: 37063861
MS products are notoriously poorly documented, especially their styles.  SharePoint's CSS file is 4,000 lines of uncommented CSS.  Try working with that ; )

How MS CSS works is that it uses extensive sub selectors.  So while you may be referencing the right thing, you may not have all of the sub selectors necessary to actually identify the piece of code you are trying to style.

If you have a sample of the HTML that you are trying to change I can tell you what will work.

I'm one of the few people who is able to fully customize SharePoint to not look like SharePoint so I'm aware of what is needed to make this type of thing work with MS programs.
0
 
LVL 36

Expert Comment

by:Norie
ID: 37064246
Danielle

How do you access the SharePoint's CSS file?
0
 
LVL 7

Expert Comment

by:DanielleFavreau
ID: 37064704
You don't.  If you do you'll void your warranty.  You have to override their code using a different style sheet and master pages.
0
 
LVL 27

Author Comment

by:CaptainCyril
ID: 37065585
Here is a sample of the CSS


.vp { font-family: Tahoma; font-size: 8pt; font-weight: normal; color: #4E60A8; text-align: center }
.hp { font-family: Tahoma; font-size: 8pt; font-weight: normal; color: #00A7A7; text-align: center }
.sigtest{ font-family: Tahoma; font-size: 8pt; font-weight: bold; text-align: center }
.sigtest95 { text-transform: uppercase; color: #CE86C0 }
.sigtest90 { text-transform: lowercase; color: #CE86C0 }

vp is Vertical Percentage
hp is Horizontal Percentage
Sigtest is Significance Tests and there are a whole bunch of them that the user chooses.

The software customizes each confidence level: color, uppercase, lowercase, ...

All is working except text-transform: lowercase or text-transform: uppercase This is how it looks in IE This is how it looks in IEExcel.JPG
0
 
LVL 27

Author Comment

by:CaptainCyril
ID: 37065603
I am sorry. The Excel snapshot I sent you has the case corrected but that via the software and not the CSS. I wish to fix it via CSS so the exports could run faster.
0
 
LVL 27

Author Comment

by:CaptainCyril
ID: 37065620
0
 
LVL 36

Expert Comment

by:Norie
ID: 37065645
Danielle

Yeah, I figured that out after a quick search.
0
 
LVL 27

Author Comment

by:CaptainCyril
ID: 37089909
Any updates?

It seems the best thing to do is program the lower/upper case outside CSS.
0
 
LVL 7

Expert Comment

by:DanielleFavreau
ID: 37091355
Try this, it's a VB solution that can force a column to be all uppercase.

http://danielbaird.com/devblog/2006/04/force-excel-column-to-upper-case.html
0
 
LVL 27

Author Comment

by:CaptainCyril
ID: 37091370
Thanks, Danielle.

What I am looking for is a CSS solution that Excel can understand.

I will leave this open for a few days.
0
 
LVL 7

Accepted Solution

by:
DanielleFavreau earned 2000 total points
ID: 37091416
Unfortunately the only CSS that is valid is text-transform: uppercase, but Excel doesn't understand that.

Excel doesn't use an HTML engine, it uses an XML engine.  But it uses its own version of it.  So, like the new Outlook, it can't translate HTML properly.  Neither can Word.  What it does when you set everything to uppercase then save it out as an HTML file is avoids uppercase all together by making the letters literally uppercase, not sentence case as you entered them.

Try it for yourself, open a Word .doc and type "This is in uppercase." just like that, then select it and choose UPPERCASE from the format menu.  Then do a file save as and save as a .htm.  You'll see that all office products do not understand uppercase only, they cannot translate that to CSS.  It's Microsoft, don't look for a reason why, just know that's the case.

That's why you can't do it via CSS for Excel, it doesn't understand what you're asking it to do.

Your options are to:
- install an add-on that will allow you to press a button and get the same result (http://www.fileguru.com/Excel-Change-Case/download)
- use VB (per my previous link)
- bypass it by setting that column to a font that only displays in caps (a font that has no lowercase letters) you'd just have to find one that exists on all machines.
0
 
LVL 27

Author Comment

by:CaptainCyril
ID: 37091511
I wrote the function that changes to uppercase and lowercase in the original program that generates the HTML files. It is a bit slower when generating 100s of the HTML files but it's OK.

Thanks a lot for your help and time.
0
 
LVL 7

Expert Comment

by:DanielleFavreau
ID: 37091646
Sorry I couldn't help more.  Microsoft makes programs incredibly difficult to work with - especially when it comes to styling.

As said previously, SharePoint alone has a single CSS file with more than 4,000 lines of uncommented code.  And their CSS file has all the code for a CSS item on a single line - so that's over 4,000 unique identifiers.  And when they switched from Office 2003 to Office 2007 they made everything except for IE work off of Microsoft Word's HTML abilities, which are incredibly limited.  That's why it's so difficult to work with their products.  They don't think about how people may use them beyond their limited scope.

I'm glad you found a solution.
0
 
LVL 27

Author Comment

by:CaptainCyril
ID: 37091652
Yes. Can you imagine that Outlook does not play animated GIFs and you have to export the email into HTML to view it?

I have read on many MSDN websites that a lot of things cannot be done but actually they can be. Like attaching part of a video in PowerPoint using automation.

Thanks for your help again.

Vous etes Francaise, Danielle? Je viens du Liban.
0
 
LVL 7

Expert Comment

by:DanielleFavreau
ID: 37091674
There are ways to hack MS stuff to make it work - but that doesn't guarantee it will work in all versions and if you aren't careful, like with SharePoint, you can void your warranty and maintenance agreement.  Not a big deal for an individual but for a corporation that could cost them millions.

Oui, je suis français. Je suis canadien.
0
 
LVL 27

Author Comment

by:CaptainCyril
ID: 37091681
Well I am moving away from MS products. :-)

I like Python which is cross-platform and runs on all systems. I also expect better results from open-source community than MS.
0
 
LVL 7

Expert Comment

by:DanielleFavreau
ID: 37091693
You'll have a much better experience with open source products (as long as they are established and well supported of course).

Personally I'm more keen on PHP rather than Python because more open source products that are used these days are written in PHP and it can be used on any type of server ... though Windows servers are still a pain to work with and I avoid them at all costs!
0
 
LVL 27

Author Comment

by:CaptainCyril
ID: 37091702
Yes we have a php application on a linux server than has been running for years without the need for a restart :-)
0
 
LVL 50
ID: 37419433
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

578 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