EXCEL怎样用VBA自动提取文件夹内的文件名？ excel，怎么用vba写段关于查找指定文件夹内的文件名，并...
\u7528EXCEL VBA\u83b7\u53d6\u6307\u5b9a\u76ee\u5f55\u4e0b\u7684\u6587\u4ef6\u540d(\u5305\u62ec\u6587\u4ef6\u5939\u540d)1.\u70b9\u5f00\u59cb\u83dc\u5355\uff0c\u5c31\u80fd\u770b\u5230\u4e0a\u9762\u7684\u754c\u9762\uff0c\u9009\u62e9\u201c\u8fd0\u884c\u201d\uff01\u70b9\u4e86\u8fd0\u884c\u7a0b\u5e8f\u540e\uff0c\u5728\u91cc\u9762\u8f93\u5165\u201ccmd\u201d\u70b9\u51fb\u786e\u5b9a\u4f1a\u8fdb\u5165\u547d\u4ee4\u63d0\u793a\u7b26\u4e32\u53e3\u3002
2.\u6253\u4e2a\u6bd4\u65b9\u8bf4\uff0c\u6587\u4ef6\u5728C\u76d8\u91cc\u9762\u7684111\u6587\u4ef6\u5939\uff0c\u8981\u8bfb\u53d6\u91cc\u9762\u7684\u6587\u4ef6\u7684\u6587\u4ef6\u540d\u5b57\u3002
3.\u5229\u7528cd\u547d\u4ee4\u5230\u8fbe\u8981\u8bfb\u53d6\u6587\u4ef6\u540d\u7684\u6587\u4ef6\u5939\uff0c\u7528\u6cd5\u662f\u8fd9\u6837\u7684\uff1a\u547d\u4ee4\u4e3a\u201ccd c:\111\u201d\u3002
4.\u7136\u540e\u4f1a\u770b\u5230\u4e0b\u9762\u7684\u8fd9\u4e2a\u60c5\u51b5\uff1a
5.\u7136\u540e\u8f93\u5165\u547d\u4ee4\u201cdir /b>d:1.xls\u201d\u7136\u540e\u56de\u8f66\u3002
6.\u5230D\u76d8\u5c31\u80fd\u770b\u5230\u4e00\u4e2a\u540d\u79f0\u4e3a1.xls\u7684\u6587\u4ef6\u3002
7.\u6253\u5f00\u5c31\u662f\u4e86\u3002
Alt+F11\u63d2\u5165\u4e0b\u9762\u7684\u4ee3\u7801\u5230\u6a21\u5757
'\u67e5\u627e\u67d0\u4e2a\u6587\u4ef6\u662f\u5426\u5b58\u5728
Function IsExistFile(ByRef strDir As String, ByRef fileName As String)
Dim s As String
If (Right(strDir, 1) "\") Then
strDir = strDir & "\"
End If
s = dir(strDir & fileName, vbArchive + vbDirectory + vbHidden + vbNormal + vbReadOnly + vbSystem)
If (s "") Then
IsExistFile = fileName
Else
IsExistFile = "\u65e0"
End If
End Function
\u53c2\u6570\u8bf4\u660estrDir\u4e3a\u6587\u4ef6\u5939\u8def\u5f84\uff0cfileName\u4e3a\u6587\u4ef6\u540d
\u4f7f\u7528\u793a\u4f8b\uff1a\u67d0\u4e2a\u5355\u5143\u683c\u8f93\u5165=IsExistFile("E:\doc",B2) \u5373\u53ef\uff0c\u82e5\u4e0d\u5b58\u5728\u8fd4\u56de\u65e0\uff0c\u5b58\u5728\u8fd4\u56deB2\u4e2d\u7684\u6587\u4ef6\u540d\u79f0\u3002\u8def\u5f84\u548c\u6587\u4ef6\u540d\u53ef\u4ee5\u76f4\u63a5\u7528\u5b57\u7b26\u4e32\u6216\u5f15\u7528\u67d0\u4e2a\u5355\u5143\u683c\u5185\u5bb9\u3002
Sub t()
Dim s As FileSearch '定义一个文件搜索对象
Set s = Application.FileSearch
s.LookIn = "c:\" '注意路径，换成你实际的路径
s.Filename = "*.*" '搜索所有文件
s.Execute '执行搜索
Cells.Delete '表格清空
For i = 1 To s.FoundFiles.Count
Cells(i, 1) = s.FoundFiles(i) '每一行第一列填写一个文件名
Next
End Sub
首先你要给我们一个用VBA自动提取文件夹的路径,我给你设的路径是"F:\我的文件\excel文件"
Sub qgrmdtj()
myPath = "F:\我的文件\excel文件" '把文件路径定义给变量
n = 1
myFile = Dir(myPath & "\*.*")
Do While myFile <> ""
Cells(n, 1) = myFile
myFile = Dir '找寻下一个文件
n = n + 1
Loop
End Sub
把上面的代码复制到模块里去，按F5执行,结果就出现在A列了
qgrmdtj的代码生成的是"文件名",这和楼主的要求一样,而"喜欢自作多情 "生成的结果是代路径的,好像和提问有点偏差.
我个人觉得qgrmdtj - 副总裁 十一级 的代码比较好,一楼的也不错,谢谢qgrmdtj和 喜欢自作多情
绛旓細涔熷氨鏄浠栬鎶婂伐浣滆〃閲岀殑绗6琛岋紝姹囨诲湪涓璧枫傛瘮濡傝繖鏍凤細浠栬濡傛灉浣犳病鍔炴硶锛岄偅浠栧彧鑳芥墜鍔ㄦ潵澶嶅埗100涓伐浣滆〃锛鎻愬彇杩欐暟鎹簡銆傛垜鎯充簡涓嬭锛屾垜鐢╒BA缂栫▼璇曡瘯銆傚樊涓嶅10鍒嗛挓鎶樿吘锛屾垜鎶婁唬鐮佹悶瀹氫簡銆侾ublic Sub tiqu()鈥欐寚瀹氳閬嶅巻宸ヤ綔琛ㄦ彁鍙 Application.ScreenUpdating = False Dim LastRow As Integer...
绛旓細鎸堿TL+F11锛屾墦寮VBE缂栬緫鍣紝鍗曞嚮鎻掑叆鈥斺旀ā鍧楋紝澶嶅埗绮樿创涓嬮潰鐨勪唬鐮佸埌鍙宠竟鐨勭紪杈戠獥鍙銆傜劧鍚庡洖鍒癳xcel宸ヤ綔琛紝鍦–2鍗曞厓鏍艰緭鍏ュ叕寮忥細=pizhu(A2)锛屼笅鎷夊鍒跺嵆鍙彁鍙栨壒娉ㄣ侾ublic Function pizhu(i As Range) pizhu = i.Cells.Comment.TextEnd Function 閫氳繃缂栧啓涓婇潰鐨勮嚜瀹氫箟鍑芥暟鏉ユ彁鍙栨壒娉紝鍙柟渚跨殑...
绛旓細1銆佹眽瀛楀湪鍓嶏紝鏁板瓧鍦ㄥ悗闈㈢殑鎯呭舰銆傚彲浠ョ敤MID锛宮in锛 find涓変釜鍑芥暟鏉ュ疄鐜版彁鍙栭噷闈㈢殑鏁板瓧銆傚鍥撅細鍦˙2杈撳叆鈥=MID(A1锛孧IN(FIND({0锛1锛2锛3锛4锛5锛6锛7锛8锛9}锛孉1&鈥0123456789鈥))锛20)鈥2 銆佹妸鍗曞厓鏍奸噷闈㈡墍鏈夌殑鏁板瓧閮芥彁鍑烘潵銆傚彲浠ヤ娇鐢ㄥ畯銆傚厛鎵撳紑VBA缂栬緫鍣ㄣ傚伐鍏封斺斿畯鈥斺攙isual basic 缂...
绛旓細1.鍦excel涓缂栧啓vba浠ｇ爜锛屽垱寤簑ord搴旂敤绋嬪簭瀵硅薄 2.鎵撳紑word鏂囨。(姝ゆ楠ゅ彲浠ヨ缃浐瀹氱殑鏂囦欢璺緞锛屼篃鍙互璁剧疆鑷畾涔夐夋嫨鏂囦欢璺緞)3.璇诲彇word鏂囨。涓殑鍐呭鍒板彉閲 4.濡傛灉杩樻秹鍙婂埌word鏂囨。鍐呭鐨勬敼鍐欙紝杩橀渶瑕佹妸鍙橀噺鐨勫煎洖鍐欏埌word鏂囨。涓 5.灏嗗彉閲忕殑鍊煎啓鍏ュ埌excel涓 6.淇濆瓨word鏂囨。 7.鍏抽棴word鏂囨。 8.濡傛灉涓...
绛旓細1.灏咥鍒椾腑鏈鍚庣殑鍐呭鎻愬彇鍒癈1涓細2.Alt+F11锛岃緭鍏ヤ唬鐮佸涓嬶細Sub m()x = Range("A65536").End(3).Row '灏嗘渶鍚庝竴涓潪绌哄崟鍏冩牸鐨勮鍙疯祴缁欏彉閲弜 Cells(1, "C") = Cells(x, "A") '灏嗘渶鍚庝竴涓潪绌哄崟鍏冩牸璧嬬粰鍗曞厓鏍糀3 End Sub 3.F5鎵ц浠ｇ爜锛屽叧闂VBA鐣岄潰锛岃繑鍥Excel寰楀埌缁撴灉濡備笅锛...
绛旓細i = Cells(Rows.Count, 1).End(3).Row Set fso = CreateObject("scripting.filesystemobject")Set myfolder = fso.GetFolder("D:\a") '寮曞彿鍐呭～鍐欐枃浠跺すa鐨勫畬鏁磋矾寰 For Each myfile In myfolder.Files If myfile.Name Like "*.xls*" Then Set wb = Workbook.Open(myfile.Path)i = i ...
绛旓細澶嶆潅闂杩樻槸寰vba浠ｇ爜鍑洪┈鍟︺俛lt+F11鈥斺斿弻鍑籺hisbook鈥斺旀墦寮宸ヤ綔绨跨殑浠ｇ爜缂栬緫妗嗐3 澶嶅埗杩涘幓濡備笅浠ｇ爜銆係ub ml()Dim sht As Worksheet, k& [a:a] = ""'娓呯┖A鍒楁暟鎹 [a1] = "鐩綍"k = 1 For Each sht In Worksheets '閬嶅巻宸ヤ綔绨夸腑姣忎釜宸ヤ綔琛 k = k + 1 '绱姞K鍊 Cells(k, 1) ...
绛旓細浣犵殑闂澶ぇ浜嗭紝鍥犱负VBA鍦excel灏辨槸鎶撴暟鎹佽绠楁暟鎹殑锛岃鐪嬩綘鍏蜂綋鎬庝箞鎶撱傛瘮濡傛姄鍙栫壒瀹氬瓧娈垫暟鎹紝搴旇鐢ㄥ埌涓変釜瀛楃涓插嚱鏁帮細LEFT銆丷IGHT銆丮ID 1銆丩EFT鍑芥暟锛氱敤娉曪細LEFT锛堣鎻愬彇瀛楃鎵鍦ㄥ崟鍏冩牸,浠庡乏渚у紑濮嬫彁鍙栫殑涓暟)渚嬪锛=LEFT(A1,2) 鎰忔濇槸浠嶢1鍗曞厓鏍兼彁鍙栧墠涓や釜瀛楃銆2銆丷IGHT鍑芥暟锛氱敤娉曪細RIGHT(...
绛旓細璇︾粏姝ラ濡備笅锛1.妯″瀷锛氬伐浣滅翱鍐呮湁鑻ュ共涓伐浣滆〃锛屽皢姣忎釜琛ㄧ殑鏈澶ц鏁鎻愬彇鍒版渶鍚庝竴涓〃涓 2.Alt+F11锛屽湪鏈鍚庝竴涓伐浣滆〃涓紝杈撳叆濡備笅浠ｇ爜 Sub main()Cells(1, 1) = "宸ヤ綔琛"Cells(1, 2) = "鏈澶ц鏁"For i = 1 To 3 Cells(i + 1, "A") = Sheets(i).Name Cells(i + 1, "B...
绛旓細excel涓敤vba瀹炵幇鑷姩鎻愬彇鏂囦欢澶瑰唴鐨勬枃浠跺悕鐨勬柟娉曞涓嬶細1銆佹柊寤轰竴涓獀ba瀹忚剼鏈 2銆佸啓鍏ュ涓嬩唬鐮侊細Function GetFileList(FileSpec As String) As Variant ' Returns an array of filenames that match FileSpec ' If no matching files are found, it returns False Dim FileArray() As Variant ...
