How to create hyperlinks to Notes documents in an Excel spreadsheet
When exporting data to Excel using Integra for Notes, you may want to create a hyperlink to the Notes document from which the data was exported. This FAQ explains how you can achieve this.
1) In your Integra profile, create a computed field which will be used to store hyperlinks
2) Copy & Paste the Advanced Script below into the CB_BFORWRITECOM event. This code will build the Notes URL of the exported documents and stores it in the computed field to be exported into a cell in Excel.
case CB_BFORWRITECOM
'Sets the hyperlink column in Excel
Dim nm as NotesName
Set nm = New NotesName(stats.srcdb.Server)
If Not stats.view Is Nothing Then
'FValue(5) Refers to the 6th field in the field list
export.record.FValue(5) = "Notes://" & nm.Common & "/" & _
stats.srcdb.ReplicaID & "/" & stats.view.UniversalID & "/" & _
stats.expdoc.UniversalId
End If
Note: In export.record.FValue(5) , 5 represents the location of the field in the selected field list. Remember that the first field in the list is at location 0.
3) Copy & Paste the code below into a Macro in your Excel Template. Note that you would need to specify both the start row of the exported data and the column in which the doc links will reside
Const startrow = 5 'first row of exported data
Dim rl As String
Dim cl As String
Dim lb As String
Dim rb As String
Sub CreateLinks()
On Error Resume Next
Call GetSettings
Call CreateHyperLinks
End Sub
Sub GetSettings()
rl = Application.International(xlUpperCaseRowLetter)
cl = Application.International(xlUpperCaseColumnLetter)
lb = Application.International(xlLeftBracket)
rb = Application.International(xlRightBracket)
On Error GoTo ErrorLocalisedRef
Application.Goto rl & "1" & cl & "1"
Exit Sub
ResumeLocalisedRefError:
On Error GoTo ErrorInternationalRef
Application.Goto "R1C1"
ResumeInternationalRefError:
Exit Sub
ErrorLocalisedRef:
rl = "R"
cl = "C"
lb = "["
rb = "]"
Resume ResumeLocalisedRefError
ErrorInternationalRef:
rl = ""
cl = ""
lb = ""
rb = ""
Resume ResumeInternationalRefError
End Sub
Sub GotoReference(ref As String)
On Error Resume Next
Application.Goto ref
End Sub
Sub CreateHyperLinks()
On Error Resume Next
Dim txt As String
Dim st_doclink As String
st_doclink = "6" 'Determine in which column the doc links are computed, in this example - column 6
Call GetSettings
GotoReference (rl & startrow & cl & st_doclink)
Do While True
GotoReference (rl & lb & "0" & rb & cl & "1")
If ActiveCell.Value = "" Then
Exit Do
End If
GotoReference (rl & lb & "0" & rb & cl & st_doclink)
If (Not ActiveCell.Value = "") And (InStr(UCase(ActiveCell.Value), "NOTES") > 0) Then
txt = ActiveCell.Value
ActiveSheet.Hyperlinks.Add Anchor:=Selection, _
Address:=txt, TextToDisplay:="Click here"
End If
GotoReference (rl & lb & "1" & rb & cl & lb & "0" & rb)
Loop
Cells.EntireColumn.AutoFit
Range("A" & startrow).Select
End Sub
|