Creating Hyperlinks strings from VBA 1

[handlebars]

I have got a problem with a document managment system that automates saved file locations.

I have developed a procedure to name, reference and direct documents to be saved to the correct location. Now I want to have this referenced in the form of a hyperlink that can be clicked and will open the document.

I know its possible with to type in the reference, ie:

C:\documents and settings\administrator\my documents\server installation\document1.doc

and this will create a valid hyperlink,

but, if i try to use vba to create a text sting of the exact text above, it will not produce a valid hyperlink....


Does anyone know the reason for this and any possible work around?

Thanks in advance

Andrew

 

[JimFlower]

show us your code
and explain more how are you building your string
what is the user going to press
are you storing this string in a variable or a table
????

 

[handlebars]

Jim

Here is the main code to create, name and save word or excel document:

Key points:

me.textLocation is the final hyperlink text;
lbxfiles is a list box populated from templates folder;
drop down box Me.ComboCorrespondence indicates whether selction is word/excel doc or template.


Public Function RunApplication()
On Error GoTo Err_RunApplication

Application.Screen.MousePointer = 11 ' Hourglass

Select Case Me.ComboCorrespondence

Case "Word Template", "Word Document"

Dim oApp As Object
Dim savedir As String
Dim q As String

Dim WordApp As Word.Application
Dim wordDoc As Word.Document
Dim wordpara As Word.Paragraph

If Me.ComboCorrespondence = "Word Template" Then

On Error Resume Next
Set WordApp = GetObject("C:\Documents and Settings\All Users\Start Menu\Programs\Word.exe&quot

If Err.Number > 0 Then
Err.Clear
Set WordApp = New Word.Application
Set wordDoc = New Word.Document
End If


Set wordDoc = WordApp.Documents.Add("C:\Documents and Settings\Administrator\Application Data\Microsoft\Templates" & "\" & lbxFiles)

Else: Set wordDoc = WordApp.Documents.Add
End If


Set oApp = CreateObject("Word.Application&quot
Set wordpara = WordApp.ActiveDocument.Paragraphs(1)
wordpara.Range.Text = Me.ComboTo.Column(1) & Chr(13) & Me.comboclient.Column(1) & " " & Chr(13) & Me.comboclient.Column(2) & Chr(13) & _
Me.comboclient.Column(3) & Chr(13) & Me.comboclient.Column(4) & Chr(13) & Me.comboclient.Column(5) & Chr(13) & Chr(13) & _
Format(Date, "dd mmm yyyy&quot & Chr(13) & Chr(13) & "Dear " & Me.ComboTo.Column(1) & Chr(13) & Chr(13) & _
"REF: " & Me.Document_Reference & ""

On Error Resume Next
ChDir Me.ComboProject.Column(1)



If Err = 76 Then 'specified directory does not exist
MkDir ("C:\Documents and Settings\Administrator\My Documents" & "\" & Me.ComboProject.Column(1))

Dim directoryname As String


directoryname = "C:\Documents and Settings\Administrator\My Documents" & "\" & Me.ComboProject.Column(1)

'MkDir (directoryname)

End If
WordApp.Options.DefaultFilePath(Path:=wdDocumentsPath) = "C:\Documents and Settings\Administrator\My Documents" & "\" & Me.ComboProject.Column(1)
Err = 0

Dim Filename As String


Filename = Left(Me.Document_Reference, 5) & " " & Me.RefNo & " " & Me.ComboCreated.Column(2)
wordDoc.SaveAs (Filename)

Err = 0

WordApp.Visible = True

Case Else




Dim oExcel As Excel.Application
Set oExcel = New Excel.Application

oExcel.Workbooks.Open ("C:\Documents and Settings\Administrator\Application Data\Microsoft\Templates" & "\" & lbxFiles)
oExcel.Visible = True

End Select

Application.Screen.MousePointer = 10

Me.TextLocation = directoryname & "\" & Filename & ".doc"


Exit_RunApplication:
Exit Function

Err_RunApplication:
MsgBox Err.Description
Resume Exit_RunApplication
End Function

 

[DeanWilliams]

Hi,

You can use a label control instead. These have a .Hyperlink property which has other useful properties: .TextToDisplay and .Address etc.

Dean.

 

[JimFlower]

try this function
docfile takes the full path

Public Sub OpenExcelDoc(docfile As String, boolReadOnlyDoc As Boolean)
Dim MyExcel As Object
DoCmd.Hourglass True
On Error Resume Next
Set MyExcel = GetObject(, "excel.application&quot 'getobject(
If Err.Number > 0 Then
Set MyExcel = GetObject("", "excel.application&quot
Else
MsgBox "Excel is currently open you will have to click on word to view your document"
End If
MyExcel.Visible = False
MyExcel.Workbooks.Open filename:=docfile, ReadOnly:=boolReadOnlyDoc
MyExcel.ActiveWindow.View.Zoom.Percentage = 75
'ActiveWindow.View.Zoom.PageFit
'ActiveWindow.View.Zoom.PageColumns
'ActiveWindow.View.Zoom.PageRows

MyExcel.Visible = True
Set MyExcel = Nothing
DoCmd.Hourglass False

End Sub

 

[handlebars]

Dean

Your idea works, but I want to have the records displayed in a datasheet format, which will not display labels for each hyperlink that I need. Doh!

Thanks anyway,

Andrew

 

[handlebars]

Stupid me - all that i needed to do was enclose the hyperlink string with ##s