What makes a column a Hyperlink? 2

[gbettle]

Howdy all,

I'm copying out a DBF (VFP 9) to an XLS file that includes a character field with a url in it. i.e.:

http://www.soccerbase.com/results3.sd?gameid=505813

When I open the XLS file in Excel (2003 SP2), the field is formatted as General and isn't a blue, hightlighted Hyperlink.
To make it a Hyperlink, I have to select a cell, click inside the Formula toolbar and press enter!

Does anyone know the format\formula I should use to make the column a Hyperlink?

Cheers,

Garry

 

[Mike Gagnon]

Is this a Foxpro question or an Excel question?

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.
ReFox XI (www.mcrgsoftware.com)

 

[gbettle]

Hi Mike,

Happy New Year (in case you haven't heard that already!).

Thanks for the reply.

Well, a bit of both really. If I knew how to do it in Excel, I'd like to automate VFP to do it. I've tried to record a macro while in Excel, but have been unable to record the actual hyperlinking event.

Garry

 

[JRB-Bldr]

When you say "I've tried to record a macro while in Excel, but have been unable to record the actual hyperlinking event." -- I don't know why.

When I recorded a Macro of selecting a pre-defined cell (B2) and then setting up a hyperlink I got the following VBA code:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 1/6/2007 by JRB-Bldr
'

'
    Range("B2").Select
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="Tek-Tips", _
        TextToDisplay:="[URL unfurl="true"]www.Tek-tips.com"[/URL]
End Sub

You should have been able to get something like that from Excel.

Now just convert something like the above to VFP and you have got it.

Good Luck,
JRB-Bldr

 

[baltman]

oXL = CREATEOBJECT("excel.application")
WITH oXL
  .Workbooks.Add()

  WITH .activesheet
    .range("B2").Hyperlinks.Add(.range("B2"), "[URL unfurl="true"]http://www.tek-tips.com/index.cfm")[/URL]
    .range("B2").Hyperlinks(1).TextToDisplay = "Tek-Tips Home"
    .range("B:B").EntireColumn.Autofit
  ENDWITH 
  
  .visible=.t.
ENDWITH

Brian

 

[gbettle]

Many thanks JRB-Bldr!

And Brian for the quick VFP conversion.

Cheers,

Garry

 

[gbettle]

Hi Baltman,

Sorry for the hassle ... If I have an entire column (i.e. column A) of URLs, how would I convert it to Hyperlinks?

i.e.

with oExcel
.Range( [B2]).Activate
.Selection.HyperLinks.Add(.Range( .Selection, .Selection.End( xlDown)).Select) .Selection.HyperLinks.Add()
endwith

?

Many thanks!

Cheers,

Garry

 

[JRB-Bldr]

Again, the best process to use it to try doing it within Excel first while recording the operations as a Macro. I don't know why you could not do it before, but my experience shows that recording the Macro works.

My guess is that since each individual cell will require its own separate link URL, you may have to do it cell-by-cell rather than as an operation on a Range.

Doing it cell-by-cell is not too difficult within the VFP automation since you can set up a FOR loop to cycle through each individual cell within your range.

Good Luck,
JRB-Bldr