vba 在活动工作表上创建文件的超链接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16809049/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create hyperlink to file on active sheet
提问by user803271
I am trying to create a button which prompts the user for a file then creates a hyperlink in the active spreadsheet.
我正在尝试创建一个按钮，提示用户输入文件，然后在活动电子表格中创建超链接。
Goal: after the file is uploaded subsequent users can click on the hyperlink to view the file.
目标：文件上传后，后续用户可以点击超链接查看文件。
What I have tried, create an ActiveX control in Excel, but representing the input as a hyperlink output in a cell is the problem.
我尝试过，在 Excel 中创建一个 ActiveX 控件，但将输入表示为单元格中的超链接输出是问题所在。
Private Sub CommandButton1_Click()
Dim sFullName As String
Application.FileDialog(msoFileDialogOpen).Show
sFullName = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
End Sub
Insert reference to pdfs
插入对 pdf 的引用
Sub InsertObjectAsIcon()
'lets user browse for a file to insert into the
'current active worksheet.
'all are inserted as icons, not "visible" objects, so
'to view they will need an appropriate viewer/reader
'at the recipient end.
'
'This one shows how you could set up to use
'several different icons depending on the type of file
'inserted. You'll have to experiment by recording
'macros while inserting various file types to build
'up a list to use, just add new Case Is = statements
'do deal with the file types. Be sure to enter the
'file type in all UPPERCASE.
'
Dim iconToUse As String
Dim fullFileName As String
Dim FNExtension As String
fullFileName = Application.GetOpenFilename("*.*, All Files", , , , False)
If fullFileName = "False" Then
Exit Sub ' user cancelled
End If
'choose an icon based on filename extension
'get all after last "." in filename
FNExtension = Right(fullFileName, Len(fullFileName) - _
InStrRev(fullFileName, "."))
'select icon based on filename extension
Select Case UCase(FNExtension)
Case Is = "TXT"
iconToUse = "C:\Windows\system32\packager.dll"
Case Is = "XLS", "XLSM", "XLSX"
iconToUse = "C:\Windows\Installer\{91140000-0011-0000-0000-0000000FF1CE}\xlicons.exe"
Case Is = "PDF"
iconToUse = "C:\Windows\Installer\{AC76BA86-1033-F400-7761-000000000004}\_PDFFile.ico"
Case Else
'this is a generic icon
iconToUse = "C:\Windows\system32\packager.dll"
End Select
ActiveSheet.OLEObjects.Add(Filename:=fullFileName, Link:=False, DisplayAsIcon:=True, IconFileName:=iconToUse, IconIndex:=0, IconLabel:=fullFileName).Select3
End Sub
Private Sub CommandButton1_Click()
InsertObjectAsIcon
End Sub
回答by Skip Intro
This code opens the common file dialog, filtered to show .xslxfiles. It picks up the path to the file, then inserts it into the activecell. There's also an inputboxasking for a short text name, if you don't want to see the full path.
此代码打开通用文件对话框，过滤以显示.xslx文件。它选取文件的路径，然后将其插入到活动单元格中。inputbox如果您不想看到完整路径，还可以询问短文本名称。
Sub FileToLink()
Dim strFileName As String
Dim strShortName As String
strFileName = Application.GetOpenFilename("Excel Documents (*.xlsx), *.xlsx")
If strFileName = "False" Then
Exit Sub ' user cancelled
End If
strShortName = InputBox("What do you want to call this link?", "Short Text", strFileName)
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=strFileName, TextToDisplay:=strShortName
End Sub
You can substitute strFileName = Application.GetOpenFilename("All Documents (*.*), *.*")to show all files. It doesn't matter to the link what file it is, as clicking on the link will invoke the application linked with that file type.
您可以替换strFileName = Application.GetOpenFilename("All Documents (*.*), *.*")以显示所有文件。链接是什么文件并不重要，因为单击链接将调用与该文件类型链接的应用程序。
