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

[µo°Ý] ¦p¦ó¥HExcel VBA¦h­«¶×¤J¤å¦rÀɨëü©w¤å¦r¬°Àx¦s®æ¡H

[µo°Ý] ¦p¦ó¥HExcel VBA¦h­«¶×¤J¤å¦rÀɨëü©w¤å¦r¬°Àx¦s®æ¡H

¦U¦ì«e½ú
¤p§Ì¥Ñ©ó¤u§@¤Wªº»Ý­n¡A­n¶×¤J¤j¶q¤å¦rÀÉ¡A¨Ã«ü©w¬Y¨Ç¤å¦r¬°Àx¦s®æªºÄæ¦ì¡C
¦]¤p§Ì¤£´¿±µÄ²vba¡A²Ä¤@¦¸¾ã²z¸ê®Æ®É¯Ó¶O¤£¤Ö®É¶¡¡A±qºô¸ô¤W·j´M¸ê®Æ»P¦Û¤vºN¯Á¤F¦h¤é¡C¥u±o¥X¥b®M¦¨ªG¡]¦pªþ¥ó¡^¡C

¥H¤U¬°¤å¦rÀɮ榡
¤¤²¤
2c23a040 c0004941 c5031a01 0029874c 00001ff8 51211000 20130830 a5a5a5a5
00000018 00000003 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000001 00000000 00000000 00000000 00000000
00000000 00000000 00000002 00000000 00000000 00000000 00000000
Test info, tempature : 32
¤¤²¤
phybist test finish result :    0.................. at [18765]
¤¤²¤
Tester send msg: "<<ErrCode=00>>"

²ÊÅé¦r¬°»Ý­n§ì¨úªº¦r¦ê

¿é¤Jµ²ªG§Æ±æ¦p¹Ï©Ò¥Ü


ªþ¥ó¬°¸ê®Æ­ì©lÀÉ»Pµ{¦¡½X
rowdata.zip (21.72 KB)

ÁٽЦU¦ì«e½ú«ü¾É»P¸Ñ´b

¥»©«³Ì«á¥Ñ stillfish00 ©ó 2014-11-13 14:26 ½s¿è

¦^´_ 1# hellrack
  1. Sub Test()
  2.   Dim vFiles, sFile
  3.   Dim oFSO As Object, sText As String
  4.   Dim oRegex As Object, oMatch As Object, ar
  5.   
  6.   vFiles = Application.GetOpenFilename(Filefilter:="¤å¦rÀÉ (*.log),*.log", Title:="¿ï¾ÜÀÉ®×(¥i¦h¿ï)", MultiSelect:=True)
  7.   If Not IsArray(vFiles) Then Exit Sub
  8.   
  9.   Set oFSO = CreateObject("Scripting.FileSystemObject")
  10.   Set oRegex = CreateObject("Vbscript.Regexp")
  11.   oRegex.Pattern = "DIEID:[\S\s]*?([0-9a-f]{8}\s+[0-9a-f]{8}\s+[0-9a-f]{8})[\S\s]+?" & _
  12.                     "Test info, tempature : ([\d]+)[\S\s]+?" & _
  13.                     "phybist test finish result :.*?\[(\d+)\][\S\s]+?" & _
  14.                     "Tester send msg:\s*""<<(.+)>>"""
  15.    
  16.   With Sheets(1)
  17.     .UsedRange.ClearContents
  18.     .[A1].Resize(1, 4) = Array("¦WºÙ1", "¦WºÙ2", "¦WºÙ3", "¦WºÙ4")
  19.    
  20.     For Each sFile In vFiles
  21.       sText = oFSO.OpenTextFile(sFile, 1).ReadAll
  22.       If Not oRegex.Test(sText) Then MsgBox "Format is not match:" & vbCr & sFile : Exit Sub
  23.       Set oMatch = oRegex.Execute(sText)
  24.       ar = Array(oMatch.Item(0).submatches(0), oMatch.Item(0).submatches(1), oMatch.Item(0).submatches(2), oMatch.Item(0).submatches(3))
  25.       .Cells(.Rows.Count, 1).End(xlUp).Offset(1).Resize(1, UBound(ar) + 1) = ar
  26.     Next
  27.     .UsedRange.EntireColumn.AutoFit
  28.   End With
  29. End Sub
½Æ»s¥N½X
ªí¹F¤£²M¡BÃD·N¤£©ú½T¡B¨SªþÀɮ׮榡¡B¨S¦³°Q½×°ÝÃDªººA«×~~~~~~¥H¤W·R²ö¯à§U¡C

TOP

¦^´_ 2# stillfish00

Stillfish00¤j¤j, ¤µ¤Ñ¯u¬O¾Ç¨ì¤F,
¤£ª¾¤å¦rÀɤ]¥i¦p¦¹Åª¨ú, ¨Ã§Q¥ÎRegular Expression language Ū¨ú.
¤Ó§Q®`¤F.
·PÁÂ.

TOP

¦^´_ 2# stillfish00


    ·PÁÂstillfish00¤j¤j¡A¤j´T´î¤Ö¤p§Ìªº¤u§@¶q¡I¦A¦¸¸U¤À·PÁ¡I

TOP

¦^´_ 2# stillfish00


¥t°Ýstillfish00«e½ú
¥ý«e´£¨Ñªºªþ¥ó¬°¤@Áûªº¸ê®Æ¡A­Y¹³¦¹ª©§ó·sªþ¥ó¤¤¥]§t¦hÁûªº¸ê®Æ¡A§Ú¸Ó«ç»ò­×§ï©O¡H

¦³³Ò«e½ú«üÂI¤F
updateattch.rar (3.21 KB)

TOP

¦^´_ 5# hellrack
  1. Sub Test()
  2.   Dim vFiles, sFile
  3.   Dim oFSO As Object, sText As String
  4.   Dim oRegex As Object, oMatch As Object, ar, i, j
  5.   
  6.   vFiles = Application.GetOpenFilename(Filefilter:="¤å¦rÀÉ (*.log),*.log", Title:="¿ï¾ÜÀÉ®×(¥i¦h¿ï)", MultiSelect:=True)
  7.   If Not IsArray(vFiles) Then Exit Sub
  8.   
  9.   Set oFSO = CreateObject("Scripting.FileSystemObject")
  10.   Set oRegex = CreateObject("Vbscript.Regexp")
  11.   oRegex.Pattern = "DIEID:[\S\s]*?([0-9a-f]{8}\s+[0-9a-f]{8}\s+[0-9a-f]{8})[\S\s]+?" & _
  12.                     "Test info, tempature : ([\d]+)[\S\s]+?" & _
  13.                     "phybist test finish result :.*?\[(\d+)\][\S\s]+?" & _
  14.                     "Tester send msg:\s*""<<(.+)>>"""
  15.   oRegex.Global = True
  16.   
  17.   With Sheets(1)
  18.     .UsedRange.ClearContents
  19.     .[A1].Resize(1, 4) = Array("¦WºÙ1", "¦WºÙ2", "¦WºÙ3", "¦WºÙ4")
  20.    
  21.     For Each sFile In vFiles
  22.       sText = oFSO.OpenTextFile(sFile, 1).ReadAll
  23.       If Not oRegex.Test(sText) Then MsgBox "Format is not match:" & vbCr & sFile: Exit Sub
  24.       Set oMatch = oRegex.Execute(sText)
  25.       ReDim ar(0 To oMatch.Count - 1, 0 To 3)
  26.       For i = 0 To oMatch.Count - 1
  27.         With oMatch.Item(i)
  28.           For j = 0 To .submatches.Count - 1
  29.             ar(i, j) = .submatches(j)
  30.           Next
  31.         End With
  32.       Next
  33.       .Cells(.Rows.Count, 1).End(xlUp).Offset(1).Resize(UBound(ar) + 1, UBound(ar, 2) + 1) = ar
  34.     Next
  35.     .UsedRange.EntireColumn.AutoFit
  36.   End With
  37. End Sub
½Æ»s¥N½X
ªí¹F¤£²M¡BÃD·N¤£©ú½T¡B¨SªþÀɮ׮榡¡B¨S¦³°Q½×°ÝÃDªººA«×~~~~~~¥H¤W·R²ö¯à§U¡C

TOP

¦^´_ 6# stillfish00

it's works
¦A¦¸·PÁ¤j¤jªºÀ°¦£¡I

TOP

        ÀR«ä¦Û¦b : §g¤l¦p¤ô¡AÀH¤è´N¶ê¡AµL³B¤£¦Û¦b¡C
ªð¦^¦Cªí ¤W¤@¥DÃD