[µo°Ý] ¨ú¥X¦r¦êªº°ÝÃD

modelcrazyer

¦U¦ì°ª¤â:
°Ý­Ó¤£ºâÃøªº°ÝÃD¡A¦ý¬O¦]¬°¸£³U©¿µMÂण¹L¨Ó©Ò¥H¨Ó³o¸ò¤j®a½Ð¯q
§Ú·Q±q K:\aaaa\bbbb\AAAA.xlsx ©Î K:\aaaa\bbbb\AAAA.xls
¨ú¥XAAAA­n«ç¼Ë³Ì§Ö
ÁÂÁÂ!!

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 ¨ç¼Æ ¶Ç¦^¦b¬Y¦r¦ê¤¤¤@¦r¦êªº³Ì¥ý¥X²{¦ì¸m¡A¦¹¦ì¸m¬° Variant (Long)¡C
   
7.     A = Mid(A, InStrRev(A, "\") + 1)
   
8.     'InStrRev¨ç¼Æ ¶Ç¦^¤@­Ó¦r¦ê¦b¥t¤@­Ó¦r¦ê¤¤¥X²{ªº¦ì¸m¡A±q¦r¦êªº¥½§Àºâ°_¡C
   
9.     MsgBox A
   
10. End Sub

½Æ»s¥N½X

­ã´£³¡ªL

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

©Î
PH = Split("K:\aaaa\bbbb\AAAA.xlsx", "\") '¤å¦r¤À³Î)
PH = Split(PH(UBound(PH)), ".")(0) '¨ú³Ì«á¤@¬q¦A¥H"."¤À³Î¨ú«e¬q

modelcrazyer

«D±`·PÁ¨â¦ìªº¦^µª¡AÅý§Ú¦h¾Ç¤FInStrRev³o­Ó¥Îªk¡A¤£¹L§Ú·Q½Ð°Ý§Ú¹Á¸Õ¥Î³o­Ó»yªk¦p¤U
filename=k:\aaa\AAA.xlsx
test=(filename,"\")
MsgBox test
³£·|¥X²{3
½Ð°Ý°ÝÃD¦b­þ

­ã´£³¡ªL

¦^´_ 4# modelcrazyer


³o¬O­þ¸Ì¾Çªº¡H¡H¡H
VBA¨S¦³³oºØ¥Îªk¡I

modelcrazyer

sorry¥´¿ù¤FÀ³¸Ó¬O
filename=k:\aaa\AAA.xlsx
test=InStrRev(filename,"\")
MsgBox test

Joforn

¦^´_ 6# modelcrazyer

¦pªG§A¬O­n±q¸ô®|¤¤¨ú¥X¤åÀɦWªº¸Ü¡A°e§A¤@¦n¥Î¤@ÂIªº¨ç¼Æ¡A³o­Óµ{¦¡ªº¯SÂI¬O®e¿ù©Ê­n¦n¤@¨Ç¡G

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

½Æ»s¥N½X

¤U­±ªºµ{¦¡¬O´ú¸Õ¥Îªº¡G

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

½Æ»s¥N½X

jackyq

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