Excel Cell Formatting Special Case

Question

I have an interesting dilema and was wondering if someone might know if this is possible. I'm creating a .csv file using java and for one of the comma separated entries for each row is a url.

Example:

item1,item2,item3,http://myurl.com

Originally when I loaded the .csv file in Excel, it loaded just the text for the url and wasn't making it clickable. I was able to solve this by just surrounding the entry with this string while creating the file:

=HYPERLINK("http://myurl.com")

When Excel loads, the link is clickable and the mouse pointer changes to the hand icon. The only problem is that when you are just looking at it and not hovering the cell, it just looks like black text on a white background. Usually when you see a link, it is blue with an underline signifying that it is clickable. I would like to have this visual queue, but I am not sure if there is a way of doing it just by modifying my function above with some sort of cell formatting function or something.

And Yes, I know what conditional formatting is and how to apply it to a cell in Excel. However, this will not work for me, because I just need it to work when the file is loaded. The file is always created new when a user downloads it, because the contents are always changing. Any ideas would be greatly appreciated.

Answer 1

The reason that hyperlinks entered by double-clicking a .csv file are "cold" (not clickable) is that the part of the UI that make a "hot" hyperlink out of typed text has been by-passed. If the .csv has raw URLs rather than =HYPERLINK() functions, they can be made hot by running the following:

Sub HyperIgniter()
    Dim r As Range
    For Each r In ActiveSheet.UsedRange
        If Left(r.Text, 4) = "http" Then
            r.Select
            Application.SendKeys "{F2}"
            Application.SendKeys "{ENTER}"
            DoEvents
        End If
    Next r
End Sub

This is the equivalent of re-typing each of the hyperlinks, thus making them "hot" and giving them the usual format.

Answer 2

You can create templated excel files using jXLS library. it is really easy to use. Sample

Answer 3

This can absolutely be done but it might be more complicated than you expected.

first, you will have to follow the steps found in this post to create a new Custom Number Format which you can use in all new workbooks and so will your customers.

Adding cell format options to Custom list

I would quote the list but I'd rather not take any credit for HansV's great solution.

The option that you want to add will be "[blue]General"

Now, your CSV file should be saving the string like the below example: "=text(hyperlink(""http://www.google.com""),""[blue]General"")"

So, now you are applying the new format (which controls font color). Just replace google.com with your URL. You will notice that there are many quotations as a product of the tricky formatting CSV forces so that it will add quotes where needed as well as not splitting the necessary comma (for the TEXT function) into two cells.