VBA 将文件夹内的文件放入到表格中link

最新推荐文章于 2023-03-22 16:42:14 发布

robot_大菜鸟

最新推荐文章于 2023-03-22 16:42:14 发布

阅读量269

点赞数

分类专栏： VBA 文章标签： excel

本文链接：https://blog.csdn.net/weixin_45498306/article/details/128370852

版权

VBA 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

在项目中，需要使用vba来将文件夹内的文件批量放入到系统中，
于是使用如下代码


Sub Step001_GetALllink()
'********************Get all the file to link of excel
'Columns(1).Delete
On Error Resume Next
Dim f As String
Dim file() As String
Dim i, k, x
x = 1
i = 1: k = 1
ReDim file(1 To i)
file(1) = Application.ActiveWorkbook.Path & "\"

'file(1) = InputBox("Where is the document:") & "\"
Do Until i > k
    f = Dir(file(i), vbDirectory)
        Do Until f = ""
            If InStr(f, ".") = 0 Then
                k = k + 1
                ReDim Preserve file(1 To k)
                file(k) = file(i) & f & "\"
            End If
            f = Dir
        Loop
    i = i + 1
Loop
For i = 1 To k
    'f = Dir(file(i) & "*.*")
    f = Dir(file(i) & "*.txt")
    Do Until f = ""
       'Range("a" & x) = f
       Range("a" & x).Hyperlinks.Add Anchor:=Range("a" & x), Address:= _
        file(i) & f, TextToDisplay:=file(i) & f
        x = x + 1
        f = Dir
    Loop
Next

End Sub