Hi all,
I have a column of text entries, which happen to be hyperlinks that Excel
doesn't recognise as hyperlinks: i.e. www.home.com, file://C:\somefile.txt,
etc
I want a means of converting the each cell of the column automatically and
so far in my research the only thing that appears to work is using the F2
function key to re-enter each cell's content.
I recorded a macro on the first cell but instead of refreshing the content
of each subsequent cell, it makes the content of every cell the same as the
first one.
The source of the data is an MS Access Query via MS Query, and the content
of the column has the potential to change every time the Spreadsheet is
opened, so automation is really the only option. I am creating the
Spreadsheet using Excel 2002 but it will be used by users running Excel 2000.
Thanks in anticipation, Sara

Hello
May be with this simple macro it would do the trick (select cells prior to
running macro):
Sub ValidateCells()
For Each c in Selection.Cells
c.Value = c.Value
Next c
End Sub

HTH
Cordially
Pascal

Thanks Pascal, being a total newbie to Macros, I copied from Sub
ValidateCells() down to End Sub and pasted that into a Visual Basic window.
Saved it, closed it, highlighted a chunk of my test data and ran the
ValidateCells macro. There was no change to the data. Am I missing
something?
TIA
Sara

Hi all,

I resolved the issue myself, once I found this article at MS Support:
http://support.microsoft.com/kb/271856/en-us , received inspiration from
http://j-walk.com/ss/excel/odd/odd29.htm and assistance from other posts in
the Excel Programming forum.

I found VBA's SpecialCells method are created the following Macro:

Private Sub Workbook_Open()
' Firstly, refresh the data from MSQuery
Range("B2").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
' Select the cells to convert to Hyperlink
Range("C2:C65536").SpecialCells(xlCellTypeConstant s, xlTextValues).Select
' Convert to Hyperlink
For Each xCell In Selection
ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
Next xCell
' Go Home
Range("A1").Select
End Sub

Being a newbie to VBA, I don't really know how efficient or elegant this
script is, but it does the job well and quickly and works in 2000 and 2002
(2003 untested).

Basically it refreshes my MSQuery from my MSAccess.mdb, selects any cell
filled with text in column C and then converts the text to hyperlinks. Once
it's done that the cursor goes back to Cell A1 and is ready for the user.
From reading, there'll only be a problem with this method if the number of
selections is greater than 8192 (http://support.microsoft.com/kb/832293/en-us)

A few prerequisites - the MSAccess.mdb columns containing the hyperlinks
must be text, all web addresses must be prefixed with 'http://' All
referenced documents must have the full path and file name, but don't need
the 'file://' prefix. Users must have at least read access to the
MSAccess.mdb and all referenced documents.

Sara