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

[µo°Ý] VBA ¶}°_³Ì·s¤å¦r¤å¥óÀÉ ¨Ã­×§ï

[µo°Ý] VBA ¶}°_³Ì·s¤å¦r¤å¥óÀÉ ¨Ã­×§ï

½Ð°Ý VBA ­n¦p¦ó¥´¶} «ü©w¸ê®Æ§¨¤¤ ¤é´Á³Ì·sªº¤å¦r¤å¥óÀÉ
¨Ã¥B­×§ï¥L

§â¤å¦r¤å¥óÀɪº
¦p¹Ï¤¤µe¬õ¦âªº¦a¤è ¥þ³¡·s¼W¤@­ÓªÅ¥ÕÁä
@@

¦^´_ 1# vm3503ru8
¬O³Ì·s¦sÀɪº¤é´Á®É¶¡¶Ü?
  1. Option Explicit
  2. Sub Ex­×§ï¤å¥óÀÉ®×()
  3.     Dim F As Object, E, AR(), i As Integer, A As Variant, xFile As String
  4.     'Set F = CreateObject("Scripting.FileSystemObject").GetFolder(ThisWorkbook.Path).Files
  5.     Set F = CreateObject("Scripting.FileSystemObject").GetFolder("§Aªº¸ê®Æ§¨¸ô®|??").Files
  6.     '.Files ->³o¸ê®Æ§¨¸ô®|¤¤ªº©Ò¦³ªºÀÉ®×
  7.     ReDim AR(1 To 2, 1 To F.Count)
  8.     For Each E In F
  9.         If E Like "*.txt" Then     'ÀÉ®×°ÆÀɦW¬°txt
  10.             i = i + 1
  11.             AR(1, i) = E                        '°}¦C²Ä¤@ºû ¸m¤JÀɮצWºÙ
  12.             AR(2, i) = CDbl(E.DateLastModified) '°}¦C²Ä¤Gºû ¸m¤J¦sÀɪº¤é´Á®É¶¡
  13.         End If
  14.     Next
  15.     A = Application.WorksheetFunction.Index(AR, 2)
  16.     xFile = AR(Application.Match(Application.Max(A), A, 0), 1)  '³Ì·s¦sÀɪºÀɮצWºÙ
  17.     Set F = CreateObject("Scripting.FileSystemObject").OPENTextFile(xFile, 1)   '¶}±Ò¤å¥óÀÉ,Ū¨ú¼Ò¦¡
  18.     A = Split(F.readall, vbLf)         'Ū¨úÀɮפº®e
  19.     F.Close                            'Ãö³¬¤å¥óÀÉ®×
  20.     Set F = CreateObject("Scripting.FileSystemObject").CreateTextFile(xFile, True) '¶}±Ò¤å¥óÀÉ,¥iÂл\­ì¤å¥óÀɼҦ¡
  21.     For i = 0 To UBound(A)
  22.         F.WriteLine IIf(i > 1 And A(i) <> "", Space(1), "") & A(i)
  23.     Next
  24.     F.Close
  25. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

ÁÂÁª©¥D ¨S¿ù¬O³Ì·s®É¶¡
¦]¬°§Ú­Ì°µ§¹¤@¥ó¨Æ ¥L·|¦Û°ÊÀx¦s¦b¤@®æ¸ê®Æ§¨¤º
¦]¬°§Ú¬O¥Î¿ý»s¥¨¶°¨Ó©ñ¤J EXCEL ¥i¬O§Ú¬O¥ÎªÅ¥Õ®æ¨Ó¤À³Î
©Ò¥H»Ý­n«e­±³£­n¦³ªÅ¥Õ@@

ª©¤j ¥i¬O§Ú¿ý»s«á §Ú¶×¤JTXTÀÉ ¶]¨ì  .CommandType = 0
¥L³£»¡©I¥s¤£¥¿½T

TOP

³ø§i ª©¥D °ÝÃD¸Ñ¨M@@


¦ý¬O²{¦b¤]¬O¹J¨ì¤@­Ó°ÝÃD
§Ú­n¶×¤JTXT¨ìEXCEL ¥L¤]­nª¾¹D ³Ì·s¦sÀɤé´Áªº¨º¤@­Ó¦b¶×¤J@@

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2017-9-9 09:33 ½s¿è

¦^´_ 4# vm3503ru8
.CommandType = 0 ¥L³£»¡©I¥s¤£¥¿½T
¨S¬Ý¨ìµ{¦¡,¤£¤F¸Ñ
  1. Option Explicit
  2. Dim xPath As String
  3. Sub Ex()
  4.     xPath = "d:\" 'ThisWorkbook.Path
  5.     Ex_­×§ï³Ì·s¤å¦rÀÉ
  6.     Workbooks.Open Latest_file(xPath)  '¶×¤JTXT¨ìEXCEL
  7. End Sub
  8. Sub Ex_­×§ï³Ì·s¤å¦rÀÉ()
  9.     Dim xFile As String, F As Object,  i As Integer
  10.     Dim My(), myRow As Integer
  11.     xFile = Latest_file(xPath)  'Latest_file¨ç¼Æ(Function) ,¶Ç¦^:³Ì·s¦sÀɪºÀɮצWºÙ
  12.     Open xFile For Input As #1
  13.     Do Until EOF(1)
  14.         ReDim Preserve My(0 To myRow)
  15.         Input #1, My(myRow) ', my(1), my(2), my(3), my(4), my(5), my(6)
  16.         myRow = myRow + 1
  17.     Loop
  18.     Close #1
  19.     Set F = CreateObject("Scripting.FileSystemObject").CreateTextFile(xFile, True) '¶}±Ò¤å¥óÀÉ,¥iÂл\­ì¤å¥óÀɼҦ¡
  20.     For i = 0 To UBound(My)
  21.     F.Writeline My(i)
  22.     Next
  23.     F.Close
  24. End Sub
  25. Function Latest_file(¸ê®Æ§¨¸ô®| As String) As String    '¦Û­q¨ç¼Æ(Function) ,¶Ç¦^:³Ì·s¦sÀɪºÀɮצWºÙ
  26.     Dim F As Object, E, AR(), i As Integer, A As Variant, xFile As String
  27.     Set F = CreateObject("Scripting.FileSystemObject").GetFolder(¸ê®Æ§¨¸ô®|).Files
  28.     ReDim AR(1 To 2, 1 To F.Count)
  29.     For Each E In F
  30.         If E Like "*.txt" Then     'ÀÉ®×°ÆÀɦW¬°txt
  31.             i = i + 1
  32.             AR(1, i) = E                        '°}¦C²Ä¤@ºû ¸m¤JÀɮצWºÙ
  33.             AR(2, i) = CDbl(E.DateLastModified) '°}¦C²Ä¤Gºû ¸m¤J¦sÀɮɶ¡
  34.         End If
  35.     Next
  36.     A = Application.WorksheetFunction.Index(AR, 2)
  37.     On Error Resume Next
  38.     Latest_file = AR(Application.Match(Application.Max(A), A, 0), 1)
  39.     If Err Then MsgBox ¸ê®Æ§¨¸ô®| & "¸ê®Æ§¨,§ä¤£¨ì  txtÀÉ®× !!! ": End
  40. End Function
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

½Ð°Ýª©¤j@@
Sub OP()
Dim myDir As String, myRow As Integer
Dim my(6)
myDir = ThisWorkbook.Path & "\"
myRow = 15
Open myDir & "123.TXT" For Input As #1
Do Until EOF(1)
Input #1, my(0), my(1), my(2), my(3), my(4), my(5), my(6)

Cells(myRow, 1).Value = my(0)
Cells(myRow, 2).Value = my(1)
Cells(myRow, 3).Value = my(2)
Cells(myRow, 4).Value = my(3)
Cells(myRow, 5).Value = my(4)
Cells(myRow, 6).Value = my(5)
Cells(myRow, 7).Value = my(6)

myRow = myRow + 1

Loop
Close #1

End Sub

¬O§_¦³¿ìªk§ï¦¨ ¥ÎªÅ¥Õ®æ¤À³Î
µM«á¤@¼Ë·ÓµÛ ³Ì·s¤é´Á@@

TOP

¥»©«³Ì«á¥Ñ vm3503ru8 ©ó 2017-9-7 14:56 ½s¿è

¤£¦n·N«ä ª©¤j

­è­è6¼Ó·N«ä¿ù¤F

¬O¬°¤°»ò ¦pªG§Ú§â¤¤¤å©ñ¦b«e­± ¥L´NµLªk¤À³Î
¼Æ¦r´N¥i¥H@@

¦p¹Ï¤U¤@¼Ë@@ ·QÅý¥L¥i¥H¦Û¦æ¤À³Î

TOP

¤£¦n·N«äª©¤j

¹J¨ì·sª¬ªp

­n¶}±Ò³æ¤@¸ê®Æ§¨ ¸Ì­± ¤é´Á³Ì·sªº txtÀÉ
¨Ã¥B§â¹Ï¤¤¬õ¦â°Ï°ìªº¦a¤è ¤£­n¦³ªÅ¥Õ®æ


¦b¶×¤J ²{¦b¶}±Òªº³o­Óexcel¸Ì­±
¥HªÅ®æ¨Ó¤À¹j
¸ê®Æ®æ¦¡¥H¤å¦r¶×¤J
¦b«ü©wªºÀx¦s®æ¤º

TOP

[ª©¥DºÞ²z¯d¨¥]
  • GBKEE(2017/9/8 11:15): ½Ðªþ¤W¤å¦rÀɪº¬Ý¬Ý

©êºp@@

¬O¥Htab ¸ò ªÅ®æ¨Ó¤À¹j

TOP

[ª©¥DºÞ²z¯d¨¥]
  • GBKEE(2017/9/9 09:40): ²Ä5©«µ{¦¡½X¤w§ó·s,½Ð°Ñ¦Ò¬Ý¬Ý

³o¸Ì ª©¤j½Ð¬d¦¬

text.rar (537 Bytes)

TOP

        ÀR«ä¦Û¦b : ¡i¥Í©R¦b©I§l¶¡¡j¦òªû»¡¡G¡u¥Í©R¦b©I§l¶¡¡C¡v¤HµLªkºÞ¦í¦Û¤vªº¥Í©R¡A§óµLªk¾×¦í¦º´Á¡AÅý¦Û¤v¥Ã¦í¤H¶¡¡C¬JµM¥Í©R¥h¨Ó³o»òµL±`¡A§Ú­Ì§óÀ³¸Ó¦n¦n¦a·R±¤¥¦¡B§Q¥Î¥¦¡B¥R¹ê¥¦¡AÅý³oµL±`¡BÄ_¶Qªº¥Í©R¡A´²µo¥¦¯uµ½¬üªº¥ú½÷¡A¬M·Ó¥X¥Í©R¯u¥¿ªº»ù­È¡C
ªð¦^¦Cªí ¤W¤@¥DÃD