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

[µo°Ý] ½Ð°ÝVBA °w¹ï¤å¥óÀɦp¦ó¨ú¥X«ü©w±ø¥ó¨Ã¼g¤JEXCEL¤º?

[µo°Ý] ½Ð°ÝVBA °w¹ï¤å¥óÀɦp¦ó¨ú¥X«ü©w±ø¥ó¨Ã¼g¤JEXCEL¤º?

§Ú¦³¤@¥÷©T©w²£¥Xªº¤å¥óÀÉ
®æ¦¡¦p¤U,Àɮפwªþ¤W
(ºØÃþ·|¦³¼Æ¤QºØ,»Ý¨ú¨ä¤¤¤@ºØ«ü©wºØÃþ¨Ã­Ó§O²Î­p¶µ¥Ø¼Æ¶q)
¦³§ä¨ì¨â¥÷¦³ÂIÃþ¦üªºµ{¦¡½X¦ý§ï¤£¦n
  1. Sub ReadfiletoExcel()
  2. Dim s As Worksheet
  3. Dim f As Integer
  4. Dim r As String
  5. Dim i As Integer
  6. Dim j As Integer
  7. Dim a() As String
  8. i = 2 '±q²Äi¦C¶}©l¼g¤JÀɮ׸ê®Æ,i¥i¦Û­q¨Ì¦Û¤v»Ý­n,¦b³o½d¨Ò³]i¬°5
  9. Set s = ActiveSheet
  10. f = FreeFile
  11. Open s.Range("A1").Value For Input As #f
  12. Do While Not EOF(f)
  13. Line Input #f, r
  14. a = Split(r, ",") '¸ÓÀÉ®×¥H,¬°¤À¹j²Å¸¹
  15. For j = 0 To UBound(a)
  16. s.Cells(i, j + 1).Value = a(j) 'Ū¨ú¸ê®Æ¨Ì§Ç¦s¤J²Äi¦Cªº²Ä1­Ó¨ìj­ÓÄæ¦ì
  17. Next j
  18. i = i + 1
  19. Loop
  20. Close #f
  21. MsgBox "Ū¨úÀɮ׸ê®Æok"
  22. End Sub
½Æ»s¥N½X
  1. Function PutRowData(strData As String, strSheets As String, strCol As String)
  2. '¶ë¸ê®Æ¨ì«ü©wÄæ¦ì¡A¨Ã©ñ¨ì¸ÓÄ檺³Ì«áªÅ¥Õ¦C
  3. '¸ê®Æ¦ê¥ÑTab¦r¤¸¦Û°Ê¤Á¶}¡A©ñ¸m¤£¦PÄ檺¸ê®Æ¨ì¤£¦PÄæ¦ì¸Ì
  4. '
  5. 'strData ¸ê®Æ¦ê
  6. 'strSheets «ü©w¤u§@ªí
  7. 'strCol «ü©wÄæ¦ì¡]­^¤å¦r¡^

  8.     Dim objDes As Object
  9.     Set objDes = Sheets(strSheets)

  10.     iNewRow = objDes.Range(strCol & "65535").End(xlUp).Row + 1
  11.     If iNewRow = 2 Then
  12.         objDes.Columns("A:B").ColumnWidth = 23
  13.         objDes.Columns("C").ColumnWidth = 8
  14.         objDes.Columns("D").ColumnWidth = 29
  15.         objDes.Columns("E:P").ColumnWidth = 8
  16.         objDes.Columns("Q").ColumnWidth = 29
  17.         'objDes.Columns("A:Q").EntireColumn.AutoFit
  18.         Title = "ºØÃþ"
  19.         tmpTitle = Split(Title, ",")
  20.             For intI = 0 To UBound(tmpTitle)
  21.                 intC = intI + 1
  22.                 objDes.Range(strCol & "2").Offset(0, intC) = tmpTitle(intI)
  23.             Next
  24.         iNewRow = iNewRow + 3
  25.     End If
  26.     tmp = Split(strData, ",")
  27.     For i = 0 To UBound(tmp)
  28.         If i = 16 Then '²Ä16­Ó°}¦C­È¨ú26­Ó¦r¤¸ = 00 00 00 00 00 00 00 00 00 00
  29.         objDes.Range(strCol & iNewRow).Offset(0, i) = Mid(tmp(i), 1, 29)
  30.         Else
  31.         objDes.Range(strCol & iNewRow).Offset(0, i) = tmp(i)
  32.         End If
  33.     Next

  34. End Function


  35. Public Function ReadATextFileToEOF(strKeyWord As String, Optional strPath As String, Optional strFileType As String = "*.*")
  36. 'Åýuser¿ï¾Ü¯Â¤å¦rÀÉ¡A¥´¶}Àɮ׫á¡A³v¦æ·j´M¡A§ä¨ìÃöÁä¦r¡A´N§â¸ê®Æ©ñ¨ì²{¦bªº¤u§@ªí¤º
  37. 'Åܼƻ¡©ú
  38. 'strKeyWord ÃöÁä¦r
  39. 'strPath ¹w³]¶}±Ò¸ô®|
  40. 'strFileType ÀÉ®×Ãþ«¬ *.txt ©Î¨ä¥L
  41. '

  42. Dim intFile As Integer
  43. Dim strFile As String
  44. Dim strIn As String
  45. Dim bnFound As Boolean

  46. booFound = False
  47. strOut = vbNullString
  48. intFile = FreeFile()

  49. Range("A1:IV65536").ClearContents '²M°£ªí®æ¸ê®Æ

  50. Dim fd As FileDialog, FileName As String, FileFolder As String
  51. Set fd = Application.FileDialog(3)
  52. With fd
  53. .Filters.Clear
  54. .Filters.Add "All Text Files", "*.txt"
  55. .AllowMultiSelect = False
  56.     If .Show = -1 Then
  57.         strFile = .SelectedItems(1)  '#1
  58.     Else
  59.         End
  60.     End If
  61. End With
  62. MsgBox "±zªºÀɮ׸ô®|¦p¤U" & vbCrLf & strFile

  63. 'strFile = "d:\1231.txt"
  64. '¨Ï¥ÎOpen¤è¦¡¶}±Ò¯Â¤å¦rÀÉ(¤£¤ä´©UTF8)
  65. Open strFile For Input As #intFile
  66. i = 0
  67. Do While Not EOF(intFile)
  68.     Line Input #intFile, strIn '¨Ì·Ó¡u¦æ¡v¨ÓŪ¨ú¸ê®Æ
  69.     i = i + 1
  70.     j = InStr(strIn, strKeyWord) '¨Ï¥ÎInStr¦r¦ê·j´M¡A¦³§ä¨ìÃöÁä¦r¡A´N±a¤J¨ì¤u§@ªí¤¤
  71.     If j > 0 Then
  72.           'Call PutRowData("¡u" & strKeyWord & "¡v¦b²Ä " & i & "¦æ¡A²Ä " & j & "¦r¤¸," & Mid(strIn, j, 170), ActiveSheet.Name, "B")
  73.            Call PutRowData(Mid(strIn, j, 170), ActiveSheet.Name, "A")
  74.           'MsgBox (strIn)
  75.         bnFound = True
  76.     End If
  77. Loop

  78. Close #intFile

  79. If bnFound = False Then
  80.     MsgBox "§ä¤£¨ìÃöÁä¦r¡I"
  81. End If
  82. End Function

  83. Sub «ö¶s2_Click()
  84. Call ReadATextFileToEOF("ºØÃþ:¦×«~", "", "*.txt")
  85. End Sub
½Æ»s¥N½X
ºØÃþ: ¦×«~
¸H½Þ¦×
--------
²£«~³W®æ: 5/20
--------
XXXXXXXXX-³Æµù
--------
XXXXXXXXX-ª`·N¨Æ¶µ
ºØÃþ: ¦A»s«~
°^¤Y
--------
²£«~³W®æ: 8/20
--------
XXXXXXXXX-³Æµù
--------
XXXXXXXXX-ª`·N¨Æ¶µ
ºØÃþ: ³½Ãþ
ÂD³½
--------
²£«~³W®æ: 8/20
--------
XXXXXXXXX-³Æµù
--------
XXXXXXXXX-ª`·N¨Æ¶µ
ºØÃþ: ¦×«~
½Þ¦×¤ù
--------
²£«~³W®æ: 80/120
--------
XXXXXXXXX-³Æµù
--------
XXXXXXXXX-ª`·N¨Æ¶µ
­ì©lÀÉ®×: CH.rar (210 Bytes)
§e²{µ²ªG: §e²{µ²ªG.rar (6.65 KB)

        ÀR«ä¦Û¦b : ¦n¨Æ­n´£±o°_¡A¬O«D­n©ñ±o¤U¡A¦¨´N§O¤H§Y¬O¦¨´N¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD