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

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

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

½Ð±Ð¦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ÁÂÁÂ

¦^´_ 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

TOP

¦^´_ 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)

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2015-9-28 20:53 ½s¿è

µ{¦¡½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
¡@

TOP

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

TOP

¦^´_ 5# GBKEE


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

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

TOP

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

TOP

¦^´_ 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
   

TOP

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

TOP

¦^´_ 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

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

¥¼©R¦W.png

TOP

        ÀR«ä¦Û¦b : ­n§åµû§O¤H®É¡A¥ý·Q·Q¦Û¤v¬O§_§¹¬üµL¯Ê¡C
ªð¦^¦Cªí ¤W¤@¥DÃD