[µo°Ý] VBA ¶}±ÒÀÉ®×À³¥Î

Jason80Lo

½Ð±Ð¦U¦ì«e½ú

¬Y©T©w¸ê®Æ§¨¤¤¨C¤ÀÄÁ·|¤£©T©w¼W¥[.TXTÀÉ¡A¥BÀɮצWºÙ¤£¦P ¦ý°ò¥»¤WÀɮצWºÙªø«×¬O¤@¼Ëªº
¤p§Ì¥Ø«e¥u¯à¨Ï¥ÎTimerª«¥ó¶}±Ò©T©w¸ô®|ªº.TXT¡A¦p¦ó¨Ï¥ÎVBA ¶}±Ò³o5¤ÀÄÁ¤º¸ê®Æ§¨©Ò¦³²£¥Íªº.TXTÀÉ
¨Ì®É¶¡±N¸ê®Æ²£¥Íªº¶¶§Ç¤À§OÅã¥ÜA1¡BB1¡BC1
EX¡GC:\Users\j\Desktop\test   ¸ê®Æ§¨¤¤
     ¦¹¦¸¤­¤ÀÄÁ¤º¨Ì§Ç²£¥Í >  111.txt   222.txt   333.txt
                              Àɮפº®e>     1               2           3

                      Àx¦s®æÅã¥Ü¬°>     A1           B1        C1
                                                       1               2           3
  

½Ð¦U¦ì«e½úÀ°À°¤p§Ì¡AÁÂÁÂ

­ã´£³¡ªL

¦^´_ 1# Jason80Lo


¯à§_¤W¶Ç³¡¥÷¤å¦rÀɤÎexcel¥DÀÉ¡A¨Ã¤â°Ê¼ÒÀÀ»Ý¨Dµ²ªG¡H

°ò¥»­n¨D¡G
´M¨DVBA¸Ñ¨M°ÝÃD¡A¤W¶ÇÀɮפ~¥i¥¿½T§PŪ¸ê®Æµ²ºc¤Î¨M©w¤èªk¡I

Jason80Lo

¦^´_ 2# ­ã´£³¡ªL

»Ý¨D¡GTest¸ê®Æ§¨¦b¬Y5¤ÀÄÁ¤ºServer¥á¥X²£¥Í 111.txt , 222.txt, 333.txt ¡A¤p§Ì»Ý¨D¬°±N³o5¤ÀÄÁ¤º²£¥ÍªºtxtÀÉ¡A¦Û°Ê¶}±Ò¡A
¤]´N¬O»¡¨C5¤ÀÄÁ µ{¦¡¯à°÷¦Û°Ê¥hCheck  Test¸ê®Æ§¨¬O§_¦³·sªºtxtÀÉ(¤p§Ì¥Ø«e¥u¯à¨Ï¥ÎTimerª«¥ó¶}±Ò©T©w¸ô®|ªº.TXT)¡A¦ýµLªk¤@¦¸¶}±Ò³o»ò¦htxtÀÉ ¥B¦WºÙ³£¤£¤@¼Ë¡C

    Test.rar (8.43 KB)

»Ý¨Dµ²ªG
¤U¸ü¦¸¼Æ: 8

2015-9-28 18:02

­ã´£³¡ªL

µ{¦¡½X½Ð¦Û¦æ¥h®M¡A¦b¤u§@ªí¢°«Ø¨â­Ó«ö¶s¡A¤À§O«ü©w¡e¶}©l¡f¤Î¡e°±¤î¡f¡A
¡e°±¤î¡f¶s¥Î¨Ó¼È°±µ{¦¡¡AÃö³¬Àɮ׫e¤]¥²¶·«ö¡e°±¤î¡f¦AÃö¡I
uP = ThisWorkbook.Path & "\"¡@¬O¤å¦rÀɸê®Æ§¨ªº¡e¸ô®|¡f¡A½Ð¦Û¦æ§ó§ï¹ê»Ú¸ô®|¡I

µ{¦¡½Xªì¬Ý»á½ÆÂø¡A®¤µLªk¤@¤@»¡©ú¡A¦³¥²­n¥i·j¤@¤U½×¾Â¸ê®Æ¥hº¥¶i²z¸Ñ¡I
¡@

1. Public uMode&, uBook As Workbook, uSht As Worksheet, uDic As Object
   
2. '======================================================
   
3. ¡@
   
4. Sub ¶}©l()
   
5. Dim FN, xE As Range
   
6. If uMode = 1 Then Exit Sub
   
7. Set uBook = ThisWorkbook
   
8. Set uSht = uBook.Sheets("¤u§@ªí1")
   
9. Set uDic = CreateObject("Scripting.Dictionary")
   
10. Set xE = uSht.Cells(1, Columns.Count).End(xlToLeft)
    
11. For Each FN In Range(uSht.[B1], xE).Value
    
12. ¡@¡@If FN Like "*.txt" Then uDic(FN) = 1
    
13. Next
    
14. uMode = 1
    
15. Call ºÊµø
    
16. End Sub
    
17. ¡@
    
18. '======================================================
    
19. Sub °±¤î()
    
20. uMode = 0
    
21. Set uDic = Nothing
    
22. End Sub
    
23. ¡@
    
24. '======================================================
    
25. Sub ºÊµø()
    
26. Dim uP$, xE As Range, TM, FL$, TT
    
27. If uMode = 0 Then Exit Sub
    
28. TM = Time
    
29. uSht.[A5] = Format(TM, "hh:mm:ss")
    
30. If Second(TM) = 0 And Minute(TM) Mod 5 = 0 Then¡@'³o¬O¨C¢´¤ÀÄÁ
    
31. 'If Second(TM) Mod 5 = 0 Then¡@'³o¬O¨C¢´¬í
    
32. ¡@¡@uP = ThisWorkbook.Path & "\"
    
33. ¡@¡@Set xE = uSht.Cells(1, Columns.Count).End(xlToLeft)(1, 2)
    
34. ¡@¡@Do
    
35. ¡@¡@¡@¡@If FL = "" Then FL = Dir(uP & "*.txt") Else FL = Dir
    
36. ¡@¡@¡@¡@If FL = "" Then Exit Do
    
37. ¡@¡@¡@¡@If uDic(FL) = "" Then
    
38. ¡@¡@¡@¡@¡@xE = FL:   uDic(FL) = 1
    
39. ¡@¡@¡@¡@¡@Open uP & FL For Input Access Read As #1
    
40. ¡@¡@¡@¡@¡@¡@Line Input #1, TT
    
41. ¡@¡@¡@¡@¡@¡@xE(3, 1) = TT
    
42. ¡@¡@¡@¡@¡@Close #1
    
43. ¡@¡@¡@¡@¡@Set xE = xE(1, 2)
    
44. ¡@¡@¡@¡@End If
    
45. ¡@¡@Loop
    
46. ¡@¡@uBook.Save
    
47. End If
    
48. Application.OnTime Now + TimeValue("00:00:01"), "ºÊµø"
    
49. End Sub

½Æ»s¥N½X

¡@

GBKEE

¦^´_ 3# Jason80Lo
°Ñ¦Ò¤@¤U
¦P¤@­Ó[¤@¯ë¼Ò²Õ]ªºµ{¦¡½X

1. Option Explicit
   
2. Dim Msg As Boolean, xTime As Variant
   
3. '¤@¯ë¼Ò²Õ:Àɮ׶}±Ò®É¦Û°Ê°õ¦æªºµ{§Ç
   
4. Sub AUTO_OPEN()  '¨â­Ó«ö¶s¡e¶}©l¡f
   
5.     If Msg = True Then Exit Sub
   
6.     Msg = True
   
7.     Ex
   
8. End Sub
   
9. '¤@¯ë¼Ò²Õ:ÀÉ®×Ãö³¬®É¦Û°Ê°õ¦æªºµ{§Ç
   
10. Sub AUTO_CLOSE() '¨â­Ó«ö¶s¡A[ °±¤î¡f
    
11.      Msg = False
    
12.      If xTime <> "" Then
    
13.         Application.OnTime xTime, "Ex", Schedule:=False  'Ãö³¬¤U¤@­ÓOnTimeªº°õ¦æ
    
14.         '³oÀÉ®×Ãö³¬«á,Excel¨SÃö³¬ÁÙ¬O·|°õ¦æOnTimeªºµ{¦¡,·|¦A«×¶}±Ò³oÀÉ®×
    
15.         xTime = ""
    
16.      End If
    
17.      ActiveWorkbook.Save  '¨Ï¥Î¤¤¬¡­¶Ã¯¦sÀÉ
    
18. End Sub
    
19. Private Sub Ex()
    
20.     Dim xPath As String, Rng(1 To 2) As Range, xFile As String, i As Integer
    
21.     Dim xString
    
22.     xPath = "d:\test\"  'txt Àɮתº¥Ø¿ý
    
23.     Set Rng(1) = ActiveWorkbook.Sheets("Sheet1").Rows(1)  ''¨Ï¥Î¤¤¬¡­¶Ã¯,³o¤u§@ªíªº²Ä¤@¦C
    
24.     xFile = Dir(xPath & "\*.txt")          '·j´MªþÀɦW
    
25.     Do While xFile <> ""                   '§ä¨ì
    
26.         Set Rng(2) = Rng(1).Find(xFile, LookAT:=xlWhole) '¤ñ¹ï²Ä¤@¦C¤¤ªº txtÀÉ
    
27.         If Rng(2) Is Nothing Then                        '²Ä¤@¦C¤¤¤ñ¹ï¨S¦³³otxtÀÉ
    
28.             i = 1
    
29.            With Rng(1).Cells(Application.CountA(Rng(1)) + 1) '¨Ì§Ç¦b²Ä¤@¦C¤¤
    
30.                 .Cells = xFile                  'ÀɦW¼g¤JÀx¦s®æ¤¤
    
31.                 Open xPath & xFile For Input Access Read As #1  '¶}±Ò¤å¦rÀÉ
    
32.                 Do Until EOF(1)                 '°õ¦æ°j°éª½¨ìÀɧÀ¬°¤î¡C
    
33.                     Line Input #1, xString      '±N¸ê®ÆŪ¤JÅܼƤ¤¡C
    
34.                     .Cells(3 + i, 1) = xString  'ÅܼƼg¤JÀx¦s®æ¤¤
    
35.                     i = i + 1
    
36.                 Loop
    
37.                 Close #1    ' Ãö³¬ÀɮסC
    
38.             End With
    
39.         End If
    
40.         xFile = Dir         '¬d¤U¤@­Ó txtÀÉ
    
41.     Loop
    
42.     xTime = Int(Application.Text(Time, "[m]") / 5) + 1  '²{¦b®É¶¡ªº¤ÀÄÁ¼Æ/5  ªº¾ã¼Æ+ 1
    
43.     xTime = DateAdd("N", 5 * xTime, 0)                  '¤U¤@­Ó5¤À¾ã
    
44.     Application.OnTime xTime, "Ex"
    
45.     Application.StatusBar = "¤U¦¸°õ¦æ®É¶¡ " & xTime
    
46. End Sub

½Æ»s¥N½X

­ã´£³¡ªL

¦^´_ 5# GBKEE


Application.OnTime xTime, "Ex", Schedule:=False  'Ãö³¬¤U¤@­ÓOnTimeªº°õ¦æ¡@

ÁÙ¬O¶Wª©ªºµ{¦¡¡e¦³®Æ¡f¡ã¡ã¡@

Jason80Lo

·PÁ¨â¦ìª©¥D ¦^ÂÐ ¤p§Ì®M®M¬Ý

Jason80Lo

¦^´_ 5# GBKEE

¶W¯Åª©¥D§A¦n¡Aµ{¦¡½X¤¤ Open xPath & xFile For Input Access Read As #1  '¶}±Ò¤å¦rÀÉ ¥X²{¿ù»~½X53 §ä¤£¨ìÀÉ®× ¡A
µ{¦¡½X¤¤xPath¬°¸ê®Æ§¨¸ô®| xFile¬°§ä¨ìªº.txtÀÉ Àɮ׸ô®|³£¬°¥¿½T¡A¦ýÁÙ¬OµLªk¶¶§Q¶}±Ò¡A½Ð°Ý¬O¦³­þÃä¥X°ÝÃD¡H
   

¤U¸ü (8.74 KB)

2015-9-29 23:34

GBKEE

¦^´_ 8# Jason80Lo
d:\test\

   

1. xPath = "d:\test\"  'txt Àɮתº¥Ø¿ý

½Æ»s¥N½X

1. MsgBox xPath & xFile '¬Ý¬Ý
   
2. Open xPath & xFile For Input Access Read As #1  

½Æ»s¥N½X

Jason80Lo

¦^´_ 9# GBKEE

Áª©¤j
¥t¥~§Ú·Q½Ð°Ý¤U¤èµ{¦¡½X
¬°¦ó xTime = Int(Application.Text(Time, "[m]") / 5) + 1 '²{¦b®É¶¡ªº¤ÀÄÁ¼Æ/5  ªº¾ã¼Æ+ 1  ³£»Ý­n±N®É¶¡/5«á­±ÁÙ­n+1 ¡H
¥t¥~xTime = DateAdd("N", 5 * xTime, 0)  §Ú·Q­n§ó§ïµ{¦¡½X¸Ì­±"5" µ{¦¡´N·|·í±¼¡A¦pªG­n¨C30¬í¡B30¤À¡B3¤p®É§ó·s¥i¥H¶Ü ¡H