最近,关于超链接的主题出现了几个问题和很多困惑。 具体来说,用户想知道如何从FileDialog Box检索文件名,将名称复制到窗体上的绑定文本框,以及将超链接保存到基础表。 下面的代码演示将做到这一点:从FileDialog框中检索文件的绝对路径,使用基本名称(无扩展名)作为Hyperlink的显示文本,然后通过以下方式将显示文本和超链接地址存储在基础表中:绑定的文本框。 同样重要的是要记住,超链接最多可以包含4个由#符号分隔的部分:Display Text#Address#Sub-Address#Control Tip Text。 在开始之前,有一些假设:
- 表名称:tblSales。
- 表单名称:frmWeeklySales。
- frmWeeklySales的RecordSource是tblSales。
- tblSales中存在一个名为WeeklyData的字段,数据类型=超链接,该字段实际上将存储超链接。
- 绑定到[WeeklyData]的表单字段(文本框)被命名为txtSalesForWeek。
- 出于演示目的,检索到的实际文件名将为:C:\ Invoices \ 2007 \ November \ Week 1 \ Period 11-05-07至11-09-07.xls。
Private Sub cmdPopulateHyperlink_Click()
'First, set a Reference to the Microsoft Office XX.X Object Library
Dim strButtonCaption As String, strDialogTitle As String
Dim strHyperlinkFile As String, strSelectedFile As String
'Define your own Captions if necessary
strButtonCaption = "Save Hyperlink"
strDialogTitle = "Select File to Create Hyperlink to"
With Application.FileDialog(msoFileDialogFilePicker)
With .Filters
.Clear
.Add "All Files", "*.*" 'Allow ALL File types
End With
'The Show Method returns True if 1 or more files are selected
.AllowMultiSelect = False 'Critical Line
.FilterIndex = 1 'Database files
.ButtonName = strButtonCaption
.InitialFileName = vbNullString
.InitialView = msoFileDialogViewDetails 'Detailed View
.Title = strDialogTitle
If .Show Then
For Each varItem In .SelectedItems 'There will only be 1
'Display Text + # + Address of Hyperlink (Display Text#Hyperlink Address)
strSelectedFile = varItem
strHyperlinkFile = fGetBaseFileName(strSelectedFile) & "#" & strSelectedFile
Me![txtSalesForWeek] = strHyperlinkFile
Next varItem
End If
End With
End Sub
Public Function fGetBaseFileName(strFilePath As String) As String
'This Function accepts the Absolute Path to a File and returns the Base File
'Name (File Name without the Extension)
'Make absolutely sure that it is a valid Path/Filename
If Dir$(strFilePath) = "" Then Exit Function
Dim strFileName As String
Dim strBaseFileName As String
strFileName = Right$(strFilePath, Len(strFilePath) - InStrRev(strFilePath, "\"))
strBaseFileName = Left$(strFileName, InStr(strFileName, ".") - 1)
fGetBaseFileName = strBaseFileName
End Function
注意:大部分代码与设置FileDialog Box和提取基本文件名有关。
对于此讨论而言,关键的代码行是第26至28行。 场景和输出:
- 从FileDialog中选择的文件名:
C:\Invoices\2007\November\Week 1\Weekly Data for Period 11-05-07 to 11-09-07.xls
- 通过fGetBaseFileName()生成的基本文件名
Weekly Data for Period 11-05-07 to 11-09-07
- 字符串已复制到[txtSalesForWeek]:
Weekly Data for Period 11-05-07 to 11-09-07#C:\Invoices\2007\November\Week 1\Weekly Data for Period 11-05-07 to 11-09-07.xls
- 显示在[txtSalesForWeek]-(显示文字)中的字符串
Weekly Data for Period 11-05-07 to 11-09-07
- 实际值存储在表中:
Weekly Data for Period 11-05-07 to 11-09-07#C:\Invoices\2007\November\Week 1\Weekly Data for Period 11-05-07 to 11-09-07.xls
From: https://bytes.com/topic/access/insights/741095-how-programmatically-create-hyperlink-form