ASP & Excel Hyperlinks

[KBusaidi]

Hello,

I have a ASP script that reads an Excel file and displays the contents. This works fine. But I need to do is to determain if the cell has a hyperlink and show that hyperlink when I build my page. Has anyone been able to do so. Any assistance us higly appreciated.

 

[QuantumDoja]

hmmmm....why not do something like

if instr(mycell,"[URL unfurl="true"]http://")[/URL] > 0 then
 'hyperlink exists
end if

http://www.demonasp.co.uk

 

[FesterSXS]

I initialy thought of that too but the text of the hyperlink may not be the actual URL

eg) Google

Tony
[red]_________________________________________________________________[/red]
Webmaster -

http://www.SpiraxSarco.com/

 

[grtammi]

Here's how you get the hyperlink information from a cell.

First step is to get the actual count of the hyperlinks located in the cell ... no point checking for anything else if there isn't a hyperlink right?

    dim numLinks as long
 
    numLinks = <excel object>.<worksheet object>.range(<cell reference>).hyperlinks.count

    if numLinks > 0 then call CheckLinks(numLinks, <excel object>.<worksheet object>.range(<cell reference>) )

    ' more code here ...

Here is the CheckLinks subroutine:

Public Sub CheckLinks(ByVal c As Long, ByVal r As Range)
    MsgBox "There are " & c & " hyperlinks " _
        & "in the passed cell."

    dim looper as long

    for looper = 1 to c
        msgbox "Hyperlink info: " _
          & r.Hyperlinks.Item(c).Address & vbcrlf _
          & " Hyperlink text: " _
          & r.Hyperlinks.Item(c).TextToDisplay
    next
    
End Sub

That's just the basics ... you'll need to modify it for what you're doing, but it's all the same really.

HTH



Greg Tammi, IT Design & Consultation
Work:

http://www.atsbell.com

Home:

http://members.shaw.ca/gregandliz

 

[KBusaidi]

Guys,

Many thanks for replies. It is interesting to see different ideas to achieve the same goal. I do have another question that I would like pose:

The spreadsheet contains serveral fields:

Item, Description, location

and for purpose of this example, say I have 11 rows where row 1 is the header info. I need to read the remaining (row 2 thru 11) range and loop to push the results out to the browser. Also checking if a hyperlink exists in the location column.

Once again appreciate any ideas.
Cheers
KB

 

[grtammi]

I actually do this for my work ... there's probably an easier solution, but here's the meat of it:

Public Sub ParseSheet(byval s as excel.worksheet)

  dim maxCol as long 
  maxCol = 72   ' ASCII equivalent of last column letter
  
  dim currRow as long : currRow = 1  ' set this to your starting row number
  dim currCol as long

  dim lastRow as boolean : lastRow = false

  dim currCell as excel.range


  while lastRow = false
     ' check first cell in current row for blank
     ' if so, set lastRow boolean to true 
     if s.range("A" & currRow).value = "" then
        lastRow = true
     else
        ' iterate current row across columns
        for currCol = 65 to maxCol
          currCell = s.range(cstr(chr(currCol) & currRow))

          ' place your code here to grab column values
          ' also check for hyperlink in this cell, using
          ' code that was in previous post

        next
        ' increment row number
        currRow = currRow + 1
      end if
   wend

End Sub

Obviously, it's not as complete as it could be, but I think it gives you an excellent starting point on how to extract the data from your Excel sheet.

Any questions about the code, leave a post!

HTH


Greg Tammi, IT Design & Consultation
Work:

http://www.atsbell.com

Home:

http://members.shaw.ca/gregandliz