ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

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

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

¦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«ç¼Ë³Ì§Ö
ÁÂÁÂ!!

¦^´_ 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
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2016-10-20 10:17 ½s¿è

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

TOP

«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­þ

TOP

¦^´_ 4# modelcrazyer


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

TOP

[ª©¥DºÞ²z¯d¨¥]
  • GBKEE(2016/10/21 12:26): filename="k:\aaa\AAA.xlsx"

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

TOP

¦^´_ 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. Public Function ExtractFileName(ByVal FileName As String, Optional ByVal ExtensionReturn As Boolean = True) As String
  13.   Dim I       As Long
  14.   Dim strPath As String
  15.   
  16.   #If VBA7 Then
  17.     Dim ptrFileName   As LongPtr
  18.   #Else
  19.     Dim ptrFileName   As Long
  20.   #End If
  21.   
  22.   FileName = Trim$(FileName) & String(5, vbNullChar)
  23.   ptrFileName = StrPtr(FileName)
  24.   PathRemoveBackslashW ptrFileName
  25.   PathStripPathW ptrFileName
  26.   If Not ExtensionReturn Then PathRemoveExtensionW ptrFileName
  27.   I = lstrlenW(ptrFileName)
  28.   If I > 0 Then ExtractFileName = Trim$(Left$(FileName, I))
  29. 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
¥@¬É¨º»ò¤j¡A¥i§Ú·Q¥h­þ¡H

TOP

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

TOP

        ÀR«ä¦Û¦b : ¤Hªº¤ß¦a¬O¤@²¥¥Ð¡A¤g¦a¨S¦³¼½¤U¦nºØ¤l¡A¤]ªø¤£¥X¦nªºªG¹ê¡C -
ªð¦^¦Cªí ¤W¤@¥DÃD