PDA

View Full Version : remove hyperlinks



v_gyku
09-20-2005, 12:30 AM
I am trying to remove hyperlink from excel sheet.
I am able to remove hyperlink from auto shapes like callouts, arrow...
its working for images also.
only hyperlinks from organization chart is not goin....

if u see code given below... its not counting the hyperlinks in the organization chart.. i.e. they are not deleted...

MsgBox (wSheet.Hyperlinks.Count)
For i = wSheet.Hyperlinks.Count To 1 Step -1

If cancelPwd = True Then
Exit For
End If
If wSheet.Hyperlinks(i).Type <> 0 Then
wSheet.Hyperlinks(i).Delete
ObjExcel.Save
End If
Next i

malik641
09-20-2005, 06:15 AM
Hey v_gyku,
Just so you know, I placed your code in VB tags :thumb

TonyJollans
09-20-2005, 08:23 AM
Hi v_gyku,

Organization Charts are embedded objects and their properties do not belong to the sheet.

You need to look at the Hyperlink property of the individual Shape.Diagram.Nodes (Org Charts are Diagrams of Type msoDiagramOrgChart) and you'll need to trap some errors as well I think - if there isn't a hyperlink, the property returns an error and it isn't a collection, so no count.

Justinlabenne
09-20-2005, 05:38 PM
Cross-Posted Here (http://www.vbforums.com/showthread.php?t=361378)

v_gyku
09-27-2005, 05:03 AM
I dont want to remove text hyperlinks :

can u just modify this code?


Dim nolinks As Integer
nolinks = 0
Dim Shp As Excel.ShapeRange
Dim IShp As Excel.Shape
Dim i As Integer
Dim j As Integer
On Error GoTo ResNextShp
If wSheet.Hyperlinks.Count = 0 Then
nolinks = nolinks + 1
Else


For j = 1 To AppExcel.ActiveSheet.Shapes.Count
AppExcel.ActiveSheet.Shapes(j).Select
Set Shp = Selection.ShapeRange
If Shp.HasDiagramNode = msoTrue Then
For i = 1 To Shp.DiagramNode.Diagram.Nodes.Count
Set IShp = Shp.DiagramNode.Diagram.Nodes(i).Shape
If IShp.Hyperlink.Address <> "" Then
IShp.Hyperlink.Delete
hashyperlinks = True
End If
Next i
End If
Set IShp = AppExcel.ActiveSheet.Shapes(j)
If IShp.Hyperlink.Address <> "" Then
IShp.Hyperlink.Delete
hashyperlinks = True
'End If
End If
Next j


i = 0
For i = AppExcel.ActiveSheet.Hyperlinks.Count To 1 Step -1
AppExcel.ActiveSheet.Hyperlinks(i).Delete
hashyperlinks = True

Next i


'End If
ResNextShp:
Resume Next

Killian
09-27-2005, 12:42 PM
I don't understand.... you have already posted the code to do this on two forums. If you take out the parts that aren't required, it removes the hyperlinks from diagrams !!!???Dim Shp As Excel.ShapeRange
Dim IShp As Excel.Shape
Dim j As Integer
Dim i As Integer

On Error Resume Next

For j = 1 To ActiveSheet.Shapes.Count
ActiveSheet.Shapes(j).Select
Set Shp = Selection.ShapeRange
If Shp.HasDiagramNode = msoTrue Then
For i = 1 To Shp.DiagramNode.Diagram.Nodes.Count
Set IShp = Shp.DiagramNode.Diagram.Nodes(i).Shape
If IShp.Hyperlink.Address <> "" Then
IShp.Hyperlink.Delete
End If
Next i
End If
Next j