上回书说到,如何用EXCEL建立自己的工作台(导航与日志),有朋友说,自己的文件太多了,做起导航很费劲。别急,其实我们可以快速提取文件名,以便于我们批量建立超链接。
点击下图延伸阅读:《高效秘技!用EXCEL制作导航页和日志表管理日常工作》
1. 无门槛通用bat法——用批处理脚本提取文件名
提取当前文件夹下的文件、文件夹名称(不含子文件夹)
新建一个文本文档,命名为“提取文件名”。
打开文本文档,输入公式:dir *.*/b>文件清单.txt
dir为列表命令,/b为只保留文件名和扩展名参数,>为将命令结果导入到指定文件内
将文本文档的文件类型改为bat,即将“提取文件名.txt”更改为“提取文件名.bat”。如果你的电脑没有显示文件扩展名,另存为.bat文件即可。
注:bat文件就是批处理文件,DOS年代的命令,不挑系统。
双击bat文件运行,即可得到文件清单:
提取当前文件夹下的文件、文件夹名称(包括子文件夹),输出文件树
上述方法有时候并不能满足我们,比如,有的同事就喜欢将文件分门别类都放到文件夹下。然后,我去查一个文件,就需要一个个文件夹去找,点了十几下,最后发现还是个空文件夹,真是让人心累。
有没有办法能够将文件夹下所有文件的名称都提取出来?
有!而且可以提取成文件树!
步骤同上,公式替换成:tree /F > 文件树1.txt
运行结果如下:
瞧,我就在某人的电脑下发现了秘密文件(上图倒数第三行),抓他个现行。
点开一看
.
.
.
.
.
.
.
这TM什么标题党?
仅提取当前文件夹名称(包含子文件夹)
步骤同上,公式替换成:tree > 文件树2.txt
运行结果如下(不提取文件的名称):
方法小结:
上述三种方法输出文件都可以改变类型,扩展名“.txt”替换成对应的文件扩展名就可以了,比如替换成“.xls”就生成EXCEL文件。
2. WIN10党福利——复制路径
新系统带来新功能,WIN10系统可以直接选中文件,复制路径。
粘贴到EXCEL表格中:
EXCEL 及以上版本可以用快速填充命令,得到文件名。
EXCEL版本低也没关系,可以用替换,将“文件名之前的路径”替换为“空”。
3. 高阶VBA大法——用EXCEL提取文件名
上面两种方法简单快捷,但有一个缺点,每次文件有变化,还需要重新运行一次批处理命令,再粘贴到EXCEL中。
能不能将更简单一点?
可以,用EXCEL的VBA功能即可。
VBA是微软的一种宏语言,EXCEL学会使用VBA就相当于打开了一片新天地。
具体方法:
1)插入一个长方形,输入名称,然后制定宏。(此步骤是为了方便运行,非必须)
2)新建宏。
3)将下面的代码复制代码框中:
Sub 提取文件名()
On Error GoTo 100
Dim wsh As Object, mypath As String, ar, i&, br
mypath = CreateObject('shell.application').BrowseForFolder(0, '请选择要搜索的文件夹', 0).Items.Item.Path '在此指定目录
Set wsh = CreateObject('wscript.shell')
mypath = wsh.exec('cmd /c tree /f ' & Chr(34) & mypath & Chr(34)).StdOut.ReadAll
mypath = Left(mypath, Len(mypath) - 1)
ar = Split(mypath, vbCrLf)
ReDim br(1 To UBound(ar) + 1, 1 To 1)
For i = 0 To UBound(ar)
br(i + 1, 1) = ar(i)
Next
Range('a1').Resize(UBound(br)) = br
Set wsh = Nothing
100:
End Sub
以上代码来自EXhome论坛@yangyangzhifeng(学习VBA编程还是颇耗时间的,网上很多代码可以借鉴)
4)点击长方形按钮运行,选择制定提取文件树的文件夹。
得到文件树:
也有代码可以实现只提取文件名(类似dir批处理命令的效果),下期告诉你。
总结
文件名提取成功了,那如何快速建立超链接呢?且听下回分解!