Excel 2010 Dynamic cross tab with HTTP link ?

bruno_boccara
bruno_boccara used Ask the Experts™
on
Hello,
I have imported data from MSSQL to Excel tab.
one of the column contains an hypertext link.
when importing this data, excel does not automatically treat the data as an hypertext but as simple text.
only after focusing on the link and clicking the return tab the text become a real link.
What should I do to force Excel to treat the link as an hypertext during the import ?

the imported column is like this :
=LIEN_HYPERTEXTE("http://xxxxx.xxxx.xxxx/index.php?type=devis&OpenDevis=148231&file=contenuCaddie/maindevis.php";"148231")
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2013
Awarded 2012

Commented:
Hi,
how did you import the data from SQL into Excel?

AFAIK you will have two options:
- Macro
- Calculated column
The macro will run through all the cells after the import and create the hyperlink, the additional column will look like
=HYPERLINK(CELLINTHISROW)

HTH
Rainer

Author

Commented:
I inport the data using ODBC connection.
when I use a calculated column, the calculated column work.
the problem is after.

when I do a dynamic cross tab, when I click on a result, I lose the calculated column !
all my problem is here !!
Most Valuable Expert 2013
Awarded 2012
Commented:
Then you have to replace the cell values through a macro:
Sub SetHyperlink()
'
' Converts each text hyperlink selected into a working hyperlink
'
    For Each xCell In Selection
        ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
    Next xCell
'
'
End Sub

Open in new window


Perhaps you need to add a check if you have empty cells.
Just select all hyperlink cells and run the macro.
HTH
Rainer
Test.csv
EE-TestHyperLink.xlsm

Author

Commented:
I DO NOT WANT TO USE MACRO

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial