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

[µo°Ý] txt ¶×¤Jexcel ,¨Ã±N¸ê®Æ¤ÀÄæ¦ì,¦A±N¦P¤@Äæ¦ìªº¸ê®Æ,§@¥­§¡

[µo°Ý] txt ¶×¤Jexcel ,¨Ã±N¸ê®Æ¤ÀÄæ¦ì,¦A±N¦P¤@Äæ¦ìªº¸ê®Æ,§@¥­§¡

[ª©¥DºÞ²z¯d¨¥]
  • Hsieh(2013-10-8 17:32): ½Ð±NtxtÀÉÀ£ÁY¤W¶Ç

¥»©«³Ì«á¥Ñ wj1228.518 ©ó 2013-10-8 17:11 ½s¿è

¦U¦ì¤j¤j,½Ð¨D¨ó§U,¤p§Ì¥ýÁÂÁ±z

¤wª¦¹L¤å--(ºô¸ô¤W±j¤H©Ò¼gªº½d¨Ò,®³¨Ó¥Î)
--------------------------------------------------
Sub ¶×¤J¤å¦rÀÉ()
Dim MySht As Worksheet, uFile, ShtName, xSht As Worksheet, i&
Set MySht = Sheets("TEMP")
Application.ScreenUpdating = False
ShtName = Array("0", "430", "630", "1040", "1050", "2050", "3050")
'---------------------------------------
For i = 0 To UBound(ShtName)
    uFile = ThisWorkbook.Path & "\" & ShtName(i) & ".txt"
    If Dir(uFile) = "" Then GoTo NEXT_FILE
    '----------------------------------------
   
    For Each xSht In Sheets
        If xSht.Name = ShtName(i) Then
           Application.DisplayAlerts = False
           xSht.Delete
           Application.DisplayAlerts = True
        End If
    Next
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = ShtName(i)
    '-----------------------------------------
   
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & uFile, Destination:=Range("A1"))
         .AdjustColumnWidth = False
         .TextFileConsecutiveDelimiter = False
         .TextFileTabDelimiter = True
         .TextFileSemicolonDelimiter = False
         .TextFileCommaDelimiter = False
         .TextFileSpaceDelimiter = False
         .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)
         .Refresh BackgroundQuery:=False
         .Delete
    End With
NEXT_FILE:
Next
'-------------------------------------------------------
MySht.Select
Application.ScreenUpdating = True
MsgBox "¡ã¡ã¶×¤J§¹¦¨¡ã¡ã¡@"
End Sub
--------------------------------------------------

°ÝÃD:
»Ý­n¤ÀÄæ¦ì(¥u»Ý­n°Ï¤À 41366 ³o­ÓÄæ¦ì ),
¥H«K±N¸ê®Æ41336 ³o­Ó¸ê®ÆªºÄæ¦ìªº©Ù¨Ç­È,¨Ó§@¥­§¡
(exp: =AVERAGE($E$161:$E$200))

¸ê®Æ¤º¤å¦pµe
(L)15862 (H)39905 (H-L)24043 |(Code)41366 (PPM)05555 |(TM)25025 (TA)023 (RH)000 (L1)01200 (L2)02000

½Ð±Ð¦U¦ì¤j¤jÀ°¦£,ÁÂÁÂ!
wj1228.518

¦^´_ 1# wj1228.518


    test.rar (48.29 KB)

À£ÁY¸ê®Æ¤º¦³9­ÓÀÉ®×
0.txt
430.txt
630.txt
1040.txt
1050.txt
2050.txt
3050.txt
¬O¥¼¸g¹L¾ã²zªºdata
test.xls ¬O µ{¦¡
code.xls ¬O¤â°Ê¾ã²z«áªºµ²ªG

¶]test.xls ®É,load ¶i¨Óªº¸ê®Æ,Äæ¦ì¥¼¤À³Î
©Ò¥H·Q½Ð±Ð¦U¦ì¤j¤j«üÂI«üÂI,
ÁÂÁÂ
wj1228.518

TOP

¦^´_ 2# wj1228.518


    test1010.rar (7.21 KB)

ÁÂÁ¦U¦ì«e½úªº«ü¾É,
²Ä¤@­Ó°ÝÃD¤w¸g¸Ñ¨M (¥Î¿ý»s¥¨¶°ªº¤è¦¡+¥ý«eªºµ{¦¡)
±µ¤U¨Ó²Ä¤G­Ó°ÝÃD§V¤O¤¤...

ªþ¥[Àɮ׬°²Ä¤@­Ó°ÝÃDªºµ{¦¡

ÁÂÁÂ
wj1228.518

TOP

¦^´_ 3# wj1228.518


    Sub ¶×¤J¤å¦rÀÉ()
Dim MySht As Worksheet, uFile, ShtName, xSht As Worksheet, i&
Set MySht = Sheets("TEMP")
Application.ScreenUpdating = False
ShtName = Array("0", "430", "630", "1040", "1050", "2050", "3050")
'---------------------------------------
For i = 0 To UBound(ShtName)
    uFile = ThisWorkbook.Path & "\" & ShtName(i) & ".txt"
    If Dir(uFile) = "" Then GoTo NEXT_FILE
    '----------------------------------------
   
    For Each xSht In Sheets
        If xSht.Name = ShtName(i) Then
           Application.DisplayAlerts = False
           xSht.Delete
           Application.DisplayAlerts = True
        End If
    Next
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = ShtName(i)
    '-----------------------------------------
   
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & uFile, Destination:=Range("A1"))
         .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 950
        .TextFileStartRow = 1
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileFixedColumnWidths = Array(8, 9, 11, 8, 5, 11, 11, 8, 8, 10)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
         .Delete
    End With
NEXT_FILE:
Next
'-------------------------------------------------------
MySht.Select
Application.ScreenUpdating = True
MsgBox "¡ã¡ã¶×¤J§¹¦¨¡ã¡ã¡@"
End Sub
wj1228.518

TOP

¥»©«³Ì«á¥Ñ yuhuahsiao ©ó 2013-10-11 17:12 ½s¿è

¦^´_ 4# wj1228.518

§Ú§Q¥Î±z¤W­±ªº¤è¦¡Åª¤JÀÉ®×, ¦ý§ÚµLÅv¤U¸ü¨º¨ÇÀÉ®×
©Ò¥H¬O§Q¥Î³Ì°Ó¤è¨º¤@¦C¸ê®Æ¸ÕÅç
¥i¥H¿é¤JÅܰʤ½¦¡¤è¦¡§@·~
¬ù²¤¦p¤U



Sub avg()
For i = 1 To 10
Sheets("0").Cells(i + 2, 12).Formula = "=AVERAGE($E$" & i & ":$E$" & i + 2 & ")"
Next i
End Sub

TOP

¦^´_ 5# yuhuahsiao


    yuhuahsiao «e½ú
   ©êºp,¦]¬°°Q½×°Ï(ª©³W)¦³µ¥¯Å­­¨î(¤p¾Ç¥ÍµLªk¤U¸ü),
   ­Y¬O±z»Ý­n§¹¾ãªºÀÉ®×,½Ð§iª¾ email, ÁÂÁÂ!
   ¤]ÁÂÁ±zªº¦^ÂÐ,§Ú¤]±N±zªºµ{¦¡¥[¤J, ¸Õ¸Õ¬Ý!
    ÁÂÁÂ!
wj1228.518

TOP

        ÀR«ä¦Û¦b : ¯¸¦b¥b¸ô¡A¤ñ¨«¨ì¥Ø¼Ð§ó¨¯­W¡C
ªð¦^¦Cªí ¤W¤@¥DÃD