[發問] 取出字串的問題

modelcrazyer

各位高手:
問個不算難的問題，但是因為腦袋忽然轉不過來所以來這跟大家請益
我想從 K:\aaaa\bbbb\AAAA.xlsx 或 K:\aaaa\bbbb\AAAA.xls
取出AAAA要怎樣最快
謝謝!!

GBKEE

回復 1# modelcrazyer
試試看

1. Option Explicit
   
2. Sub EX()
   
3.     Dim A
   
4.     A = UCase("K:\aaaa\bbbb\AAAA.Xlsx")
   
5.     A = Mid(A, 1, InStr(A, ".XLS") - 1)
   
6.     'InStr 函數 傳回在某字串中一字串的最先出現位置，此位置為 Variant (Long)。
   
7.     A = Mid(A, InStrRev(A, "\") + 1)
   
8.     'InStrRev函數 傳回一個字串在另一個字串中出現的位置，從字串的末尾算起。
   
9.     MsgBox A
   
10. End Sub

複製代碼

准提部林

PH = "K:\aaaa\bbbb\AAAA.xlsx"
PH = Split(Mid(PH, InStrRev(PH, "\") + 1), ".")(0)

或
PH = Split("K:\aaaa\bbbb\AAAA.xlsx", "\") '文字分割)
PH = Split(PH(UBound(PH)), ".")(0) '取最後一段再以"."分割取前段

modelcrazyer

非常感謝兩位的回答，讓我多學了InStrRev這個用法，不過我想請問我嘗試用這個語法如下
filename=k:\aaa\AAA.xlsx
test=(filename,"\")
MsgBox test
都會出現3
請問問題在哪

准提部林

回復 4# modelcrazyer


這是哪裡學的？？？
VBA沒有這種用法！

modelcrazyer

sorry打錯了應該是
filename=k:\aaa\AAA.xlsx
test=InStrRev(filename,"\")
MsgBox test

Joforn

回復 6# modelcrazyer

如果你是要從路徑中取出文檔名的話，送你一好用一點的函數，這個程式的特點是容錯性要好一些：

1. #If VBA7 Then
   
2.     Private Declare PtrSafe Function PathRemoveBackslashW Lib "shlwapi.dll" (ByVal pszPath As LongPtr) As Long
   
3.     Private Declare PtrSafe Function PathStripPathW Lib "shlwapi.dll" (ByVal pszPath As LongPtr) As Long
   
4.     Private Declare PtrSafe Function PathRemoveExtensionW Lib "shlwapi.dll" (ByVal pszPath As LongPtr) As Long
   
5.     Private Declare PtrSafe Function lstrlenW Lib "kernel32.dll" (ByVal lpString As LongPtr) As Long
   
6. #Else
   
7.     Private Declare PtrSafe Function PathRemoveBackslashW Lib "shlwapi.dll" (ByVal pszPath As Long) As Long
   
8.     Private Declare PtrSafe Function PathStripPathW Lib "shlwapi.dll" (ByVal pszPath As Long) As Long
   
9.     Private Declare PtrSafe Function PathRemoveExtensionW Lib "shlwapi.dll" (ByVal pszPath As Long) As Long
   
10.     Private Declare PtrSafe Function lstrlenW Lib "kernel32.dll" (ByVal lpString As Long) As Long
    
11. #End If
    
12. 
    
13. 
    
14. Public Function ExtractFileName(ByVal FileName As String, Optional ByVal ExtensionReturn As Boolean = True) As String
    
15.   Dim I       As Long
    
16.   Dim strPath As String
    
17.   
    
18.   #If VBA7 Then
    
19.     Dim ptrFileName   As LongPtr
    
20.   #Else
    
21.     Dim ptrFileName   As Long
    
22.   #End If
    
23.   
    
24.   FileName = Trim$(FileName) & String(5, vbNullChar)
    
25.   ptrFileName = StrPtr(FileName)
    
26.   PathRemoveBackslashW ptrFileName
    
27.   PathStripPathW ptrFileName
    
28.   If Not ExtensionReturn Then PathRemoveExtensionW ptrFileName
    
29.   I = lstrlenW(ptrFileName)
    
30.   If I > 0 Then ExtractFileName = Trim$(Left$(FileName, I))
    
31. End Function

複製代碼

下面的程式是測試用的：

1. Sub Test()
   
2.   Debug.Print ExtractFileName("K:\aaaa\bbbb\AAAA.xlsx ", False)
   
3.   Debug.Print ExtractFileName("K:\aaaa\bbbb\AAAA.01.xlsx ", False)
   
4.   Debug.Print ExtractFileName("K:\aaaa\bbbb\AAAA\  ", False)
   
5.   Debug.Print ExtractFileName("K:\aaaa\bbbb\AAAA", False)
   
6.   
   
7.   Debug.Print ExtractFileName("K:\aaaa\bbbb\AAAA.xlsx ", True)
   
8.   Debug.Print ExtractFileName("K:\aaaa\bbbb\AAAA.01.xlsx ", True)
   
9. End Sub

複製代碼

jackyq

Debug.Print                                                     createobject("scripting.filesystemobject").getbasename(filename)