標題: [發問] 資料夾尋找文字檔案名稱問題尋問 [打印本頁]

---

作者: rouber590324    時間: 2016-7-27 08:27     標題: 資料夾尋找文字檔案名稱問題尋問

DEAR ALL 大大 -
1.小弟請教之問題說明如下
1.1   \\89.80.70.1\design\add_word 下有一堆文字檔.TXT 檔案. 例: SPL321A  S16987A  Q16222A .....
1.2 EXCEL 之 SHEET.RANGE("A1")=檔案名稱.執行一功能鍵後於\\89.80.70.1\design\add_word 下找尋
    有無相同名稱之文字檔.有於 SHEET.RANGE("A2")代TRUE  無於SHEET.RANGE("A2")代FALSE
     例:當 SHEET.RANGE("A1")=S16987A 時.尋找 \\89.80.70.1\design\add_word 下有一堆文字檔.TXT 檔案
      有符合之檔案名稱.於SHEET.RANGE("A2")代TRUE.
     例:當 SHEET.RANGE("A1")=S19999A 時.尋找 \\89.80.70.1\design\add_word 下有一堆文字檔.TXT 檔案
      無符合之檔案名稱.於SHEET.RANGE("A2")代FALSE.

---

作者: Joforn    時間: 2016-7-27 16:28

1. Option Explicit
   
2. #If VBA7 Then
   
3.   Private Declare PtrSafe Function PathIsDirectoryW Lib "shlwapi.dll" (ByVal pszPath As LongPtr) As Long
   
4.   Private Declare PtrSafe Function PathFileExistsW Lib "shlwapi.dll" (ByVal pszPath As LongPtr) As Long
   
5. #Else
   
6.   Private Declare Function PathIsDirectoryW Lib "shlwapi.dll" (ByVal pszPath As Long) As Long
   
7.   Private Declare Function PathFileExistsW Lib "shlwapi.dll" (ByVal pszPath As Long) As Long
   
8. #End If
   
9. 
   
10. '检测文件是否存在
    
11. Public Function FileExists(ByVal strPath As String) As Boolean
    
12.   strPath = strPath & String(5, vbNullChar)
    
13.   If PathFileExistsW(StrPtr(strPath)) Then
    
14.     FileExists = PathIsDirectoryW(StrPtr(strPath)) = 0
    
15.   End If
    
16. End Function
    
17. 
    
18. Public Sub FilesExists()
    
19.   Dim I As Long
    
20.   For I = 1 To Range("A" & Rows.Count).End(xlUp).Row
    
21.     Range("B" & I).Value = FileExists("\\89.80.70.1\design\add_word\" & Range("A" & I).Value & ".TXT")
    
22.   Next I
    
23. End Sub

複製代碼

---

作者: rouber590324    時間: 2016-7-28 09:47

DEAR  Joforn  大大
100%符合需求.感謝您 THANKS*10000

---

歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)