链接并抓取外部数据的方法集合:
1. 函数 HyperLink (Win10 复制路径/非Win10)
2. 添加链接(Ctrl+K)
3. VBA Workbooks.Open
4. VBA GetObject
5. VBA ADO外部链接
文件资料链接
1.秦川:如何在Excel中快速创建文件的超级链接? (关注点:DOS命令行获取文档路径)
2.秦川:技巧51 如何在Excel中快速创建文件的快捷链接 (Win10专用 - 视频版)
3.办公中,把 Excel 函数用到极致是怎样一种体验?
4.Steven:【Excel VBA】- 批量插入文件超链接 (关注点: 添加批量删除超链接)
5.每天遇见都是美好:用Excel做目录,最简单的方法!
A.HyperLink (专属Win10使用)
1)选中文件
2) 复制路径
3)粘贴到Excel文件
选中所需文件 - 复制路径
粘贴路径到Excel - 引用HyperLink函数
B. 非Win10系统
1) 直接复制路径
2) 打开浏览器 - 选中网址输入栏
3) 右击 - 粘贴并转到
4) 复制所有路径下的文件
5) 添加文件路径
6) 用HyperLink函数链接
直接复制路径
打开浏览器 - 选中网址输入栏 - 粘贴并转到:
选中并复制路径下的所有文件:
添加路径 (如F列)- 再用HyperLink函数链接:
C. VBA 制作目录(1)- HypeLink函数
Sub ml()
Dim sht As Worksheet, i as long, shtname as string
Columns(1).ClearContents
Cells(1, 1) = "目录"
i = 1
For Each sht In Worksheets
shtname = sht.Name
If shtname <> ActiveSheet.Name Then
i = i + 1
ActiveSheet.Hyperlinks.Add anchor:=Cells(i, 1), Address:="",SubAddress:="'" & shtname & "'!a1",TextToDisplay:=shtname
End If
Next
End Sub
D. VBA 制作目录(2) - 工作簿事件
Private Sub Worksheet_Activate()
Dim Sht as Worksheet
Dim I as long
With Thisworkbook.ActiveSheet
.range("A1") = "目录"
.Range("A2").currentregion.ClearContents
End With
I =1
For Each Sht as Worksheets
If Sht.name <> Activesheet.name then
ActiveSheet.Cells(I,1).value = Sht.Name
I = I + 1
End if
Next
End Sub
Private Sub WorkSheet_SelectionChange(Byval Target As Range)
Dim Irow as long
Irow = ActivateSheet.Range("A65536").end(xlup).row
On Error Resume Next
If Target.Count = 1 and Target.Column = 1 and Target.Row > 1 then
Sheets(Range.text).select
End if
End Sub
E. VBA 工作簿显性打开
Sub Test()
Dim Wb as Workbook
Dim Path as String
Application.screenupdate= false
Path = ""
Set Wb = workbooks.open(Path)
' 该干嘛就干嘛
Wb.Close
Set Wb = nothing
Application.screenupdate= true
End Sub
F. VBA 工作簿隐形打开
Sub Test1()
Dim Wb as Workbook
Dim Path as String
Path =""
Set Wb =getobject(Path)
’该干嘛就干嘛
Wb.Close
Set Wb = nothing
End Sub
G. VBA ADO 外部链接
Sub Test2()
Dim Conn as New Connection
Dim Sql as string
Conn.Open "Provider = Microsoft.ACE.OLEDB.12.0;extended properties=excel 8.0;data source=" & ThisWorkbook.path & "/Database/Database.xls"
Sql = "Select * from [Sheet1$]" '你想干嘛就干嘛
Range("A2").Currentregion.ClearContents
Range("A2").Copyfromrecordset Conn.Execute(Sql)
Conn.Close
Set Conn = nothing
End Sub
H. VBA FSO文本文档外部链接
Sub Test3()
Dim tx as string
On Error Resume Next
tx = ThisWorkbook.Path & "读入的文本.txt"
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.OpenTextFile(tx, 1)
a = f.readall
f.Close
'想干嘛就干嘛
End Sub