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

Ãö©ó¡GVBA Functionªº°ÝÃD

Ãö©ó¡GVBA Functionªº°ÝÃD

§Ú¦bºô¸ô¤W§ä¨ì¤@­Ó¥i¥H¨ú±oÀɦWªºVBA¡A¦ý¬O¥¦¬O¥ÎFunction¼gªº¡I¡I¡I
¦p¤U¡K¡K
  1. 'Description¡G¨ú±o¬¡­¶Ã¯¥Ø¿ý©Î©³¤U¥Ø¿ýªº¥þ³¡(«ü©w)ÀɮצWºÙ¶°¦X - Ver. 1.05
  2. 'Parameters¡Gwb ¬¡­¶Ã¯¡BstrSplitChar ¤À¹j¦r¤¸¡BstrFolderPath ¬¡­¶Ã¯©³¤U¥Ø¿ýªº¸ô®|¡BstrExtension «ü©wªº°ÆÀɦW
  3. 'Retrun¡GString
  4. 'Package¡G
  5. 'Reference¡GMicrosoft Scripting Runtime
  6. 'Creater¡GDarren.NET - 2009/04/17
  7. 'Modify¡G
  8. Public Function F_File_GetFileNameArry(wb As Workbook, strSplitChar As String, strFolderPath As String, strExtension As String) As String
  9.     Dim fsoFiles As Scripting.FileSystemObject
  10.     Dim fsoExtension As Scripting.FileSystemObject
  11.     Dim files As Scripting.files
  12.     Dim file As Scripting.file
  13.     Dim strCompletePath As String
  14.     Set fsoFiles = New Scripting.FileSystemObject
  15.     Set fsoExtension = New Scripting.FileSystemObject
  16.      
  17.     '§PÂ_¸ô®|(strFolderPath ¬O§_¦³­È)¡A­Y¤£¬°ªÅ§¹¾ã¸ô®|¬°¬¡­¶Ã¯©Ò¦b¸ô®|+©³¤U¥Ø¿ý¸ô®|¡A
  18.     '                                  ­Y¬OªÅ¥Nªí¬°§¹¾ã¸ô®|§Y¬°¬¡­¶Ã¯©Ò¦b¸ô®|
  19.     If strFolderPath <> "" Then
  20.         strCompletePath = wb.Path & strFolderPath
  21.     Else: strCompletePath = wb.Path
  22.     End If
  23.      
  24.     '¨ú±o§¹¾ã¸ô®|¥Ø¿ý¤Uªº¥þ³¡Àɮ׶°¦X
  25.     Set files = fsoFiles.GetFolder(strCompletePath).files
  26.      
  27.     '§PÂ_¬O§_¦³«ü©w°ÆÀɦW(strExtension ¬O§_¦³­È)¡A­Y¤£¬°ªÅ(¦³«ü©w)«h¶·¹LÂoÀɮסA
  28.     '               ­Y¬O«ü©wªº°ÆÀɦW¤~¥[¤J¦r¦ê¡F­Y¬°ªÅªí¥Ü¥þ³¡ÀɮצWºÙ¥[¤J¦r¦ê
  29.     If strExtension <> "" Then
  30.         For Each file In files
  31.             If UCase(fsoExtension.GetExtensionName(file.Name)) = UCase(strExtension) Then
  32.                 If F_File_GetFileNameArry <> "" Then
  33.                     F_File_GetFileNameArry = F_File_GetFileNameArry + strSplitChar + file.Name
  34.                 Else: F_File_GetFileNameArry = file.Name
  35.                 End If
  36.             End If
  37.         Next
  38.     Else
  39.         For Each file In files
  40.             If F_File_GetFileNameArry <> "" Then
  41.                     F_File_GetFileNameArry = F_File_GetFileNameArry + strSplitChar + file.Name
  42.             Else: F_File_GetFileNameArry = file.Name
  43.             End If
  44.         Next
  45.     End If
  46.      
  47.     Set fsoFiles = Nothing
  48.     Set fsoExtension = Nothing
  49.     Set files = Nothing
  50.     Set file = Nothing
  51. End Function
½Æ»s¥N½X
¤p§Ì²Ä¤@¦¸¬Ý¨ì³oºØªF¦è¡A½Ð±Ð¤@¤U
¤@¡BFunctionªº¥Î³~¬O¤°»ò¡H¡H
¤G¡BFunction¦p¦ó¨Ï¥Î¡H
¤T¡B¤°»ò¼Ë¤lªº±¡ªp¤U·|¥Î¨ìFunction¡H
ªì¾ÇVBA¥E¨ìtwbts

¦^´_ 1# play9091
Function: ¨ç¼Æ  ,Excel ¤w«Ø¥ß³\¦hªº¨ç¼Æ , ¥i¦Û­q Function ¥i¨Ì­Ó¤H»Ý¨D¶q¨­­q°µ ,¨Ó¨Ï¥Î
¨Ò: ¦b¤@¯ë¼Ò²Õ (Module)   ¤º¦³ Function Play9091()  
                                              '''''
                                              '''
                                      End Function
¥i¦b¦p¹Ï¨Ï¥Î

TOP

¦^´_ 2# GBKEE

­è­è¸Õ¥Î¤F¤@¤Uªº½T¬O¥i¥H¥Îªº¡A¤S¾Ç¨ì¤@©Û¤F¡K¡K

¥t¥~¡AÃö©ó¬Q¤Ñ½Ð±Ð¥ý¶iªº¡u¶K¤W­Èªº¡v°ÝÃD¡A¤µ¤Ñ¤S¹J¨ì¤F¡A§Ú¯uªº¤£ª¾¹D¬O­þ¸Ì¦³°ÝÃD¤]¡K¡K§â¹Ï¶K¥X¨Óµ¹±z¬Ý¤@¤U¡I

¥¼©R¦W.png (108.83 KB)

¥¼©R¦W.png

ªì¾ÇVBA¥E¨ìtwbts

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2011-7-27 13:43 ½s¿è

¦^´_ 3# play9091
°}¦C¯Á¤Þ¶W¥X½d³ò    ªº·N«ä¬O§ä¤£¨ì
Àˬd WorkBooks(Main ).Sheets(Re). Cells(NoR) ....ªºÅܼƬO§_¥¿½T

TOP

¥»©«³Ì«á¥Ñ play9091 ©ó 2011-7-27 14:25 ½s¿è

¦^´_ 4# GBKEE

¬O§Ú²Ê¤ß¡K¡K¨S¦³¥´¹ï¥¿½TªºÅܼơK¡K

·Q¦A½Ð±Ð¡K¡K¦pªG¶}±ÒÀɮתº®É­Ô·|°Ý¡A§ó·s³sµ²¡C¨º­Óµøµ¡¦³¨S¦³¿ìªk¤£Åý¥¦¥X²{¡K¡K
ªì¾ÇVBA¥E¨ìtwbts

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2011-7-27 15:30 ½s¿è

¦^´_ 5# play9091
  Option Explicit ³¯­z¦¡  ¦b¼Ò²Õ¼h¦¸¤¤±j­¢¨C­Ó¦b¼Ò²ÕùتºÅܼƳ£¥²¶·©ú½Tªº«Å§i¡C  ¦p¦¹ Åܼƥ´¿ù¥ß¨èª¾¹D


¦p¹Ï

TOP

¬Ý°_¨ÓSheets(Re). ¤u§@ªí¦W¤£¦Xªk¡C¬°¤°»ò¦³°Ý¸¹²£¥Í¡H

TOP

¦^´_ 5# play9091
§ó·s³sµ²¡C¨º­Óµøµ¡¦³¨S¦³¿ìªk¤£Åý¥¦¥X²{¡K¡K
DEE³sµ²  = Excel|aa.xls!aa    ¨S¿ìªk

¤½¦¡³sµ²='D:\¸Õºâªí\aa.xls'!aa
  1. ActiveWorkbook.UpdateLinks = xlUpdateLinksAlways
½Æ»s¥N½X



TOP

¦^´_ 8# GBKEE

«¢¡IÁÙ¬O¤£¦æ¡A§Ú¥s¥L­Ì¦Û¤w«ö¡u¤£­n§ó·s¡v¦n¤F¡I¡I¡I
ªì¾ÇVBA¥E¨ìtwbts

TOP

¦^´_ 9# play9091


    Workbooks.Open Filename:=¸ô®|,UpdateLinks:=False
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : °µ¸Ó°µªº¨Æ¬O´¼¼z¡A°µ¤£¸Ó°µªº¨Æ¬O·Mè¡C
ªð¦^¦Cªí ¤W¤@¥DÃD