Excel 2007 VBAŪTXTÀɨÃÂà¸m
- ©«¤l
- 1018
- ¥DÃD
- 15
- ºëµØ
- 0
- ¿n¤À
- 1058
- ÂI¦W
- 0
- §@·~¨t²Î
- win7 32bit
- ³nÅ骩¥»
- Office 2016 64-bit
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ®ç¶é
- µù¥U®É¶¡
- 2012-5-9
- ³Ì«áµn¿ý
- 2022-9-28
|
¦^´_ 1# alexsas38 - Sub TEST()
- Dim fd, f, fo
-
- With Workbooks.Add
- '¼ÐÃD¦C
- .Sheets(1).Range("A1:D1") = Array("¦W¦r", "¼Æ¾Ç", "^¤å", "¦a²z")
- '·s¼W¼È¦s¸ê®Æªí
- With .Sheets.Add(after:=.Sheets(.Sheets.Count))
- 'ÂsÄý¿ï¾Ü¸ê®Æ§¨
- With Application.FileDialog(msoFileDialogFolderPicker)
- .AllowMultiSelect = False
- If .Show = -1 Then fd = .SelectedItems(1) & "\"
- End With
- '¹ï©Ò¦³¸Ó¸ê®Æ§¨¤Uªºtxt³B²z
- f = Dir(fd & "*.txt")
- Do While f <> ""
- .Cells.ClearContents
- '¶×¤J¥~³¡¸ê®Æ
- With .QueryTables.Add(Connection:="TEXT;" & fd & f, Destination:=.Range("A1"))
- .Name = "¦¨ÁZ"
- .RefreshPeriod = 0
- .TextFileParseType = xlDelimited
- .TextFileConsecutiveDelimiter = True
- .TextFileTabDelimiter = True 'TabÁ䬰¤À³Î¦r¤¸
- .TextFileSemicolonDelimiter = False
- .TextFileCommaDelimiter = False
- .TextFileSpaceDelimiter = True 'ªÅ¥ÕÁ䬰¤À³Î¦r¤¸
- .Refresh BackgroundQuery:=False
- End With
- '§R°£¸ê®Æ³s½u
- .Cells.QueryTable.Delete
- '·s¼W¸ê®Æ¨ì²Ä¤@Ó¤u§@ªí
- .Parent.Sheets(1).Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(, 4).Value = Application.Transpose(.Range("B1:B4").Value)
- f = Dir
- Loop
- '§R°£¼È¦s¸ê®Æªí,¤£Åã¥Üĵ§iµøµ¡
- Application.DisplayAlerts = False
- .Delete
- Application.DisplayAlerts = True
- End With
- .Sheets(1).Activate '¨Ï¶}±Ò¸ÓÀɮɪ½±µ¨ì²Ä¤@Ó¤u§@ªí
- '¦sÀÉ
- fo = Application.GetSaveAsFilename(InitialFileName:=fd & "final.xls", FileFilter:="Excel Files (*.xls),*.xls", Title:="Àx¦sÀÉ®×")
- '°£«D«ö¨ú®ø, §_«h¦sÀÉ
- If TypeName(fo) = "String" Then .SaveAs Filename:=fo, FileFormat:=xlExcel8
- End With
- End Sub
½Æ»s¥N½X |
|
|
|
|
|
|
- ©«¤l
- 1018
- ¥DÃD
- 15
- ºëµØ
- 0
- ¿n¤À
- 1058
- ÂI¦W
- 0
- §@·~¨t²Î
- win7 32bit
- ³nÅ骩¥»
- Office 2016 64-bit
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ®ç¶é
- µù¥U®É¶¡
- 2012-5-9
- ³Ì«áµn¿ý
- 2022-9-28
|
¦^´_ 4# alexsas38
³o¼ËÀÉ®×¥u¯à¦Û¤v¥ÎSplitåªR¡G- Sub TEST()
- Dim fd, f, fo
- Dim ar(), fnum As Integer, i, s
- Dim arData() As String, dataLine As String
-
- ReDim ar(0)
- ar(0) = Array("Àç·~¤H²Î¤@½s¸¹", "t³d¤H©m¦W", "Àç·~¤H¦WºÙ", "Àç·~¡]µ|Äy¡^µn°O¦a§}", "¸ê¥»ÃB(¤¸)", "²Õ´ºØÃþ", "³]¥ß¤é´Á", "µn°OÀç·~¶µ¥Ø")
-
- With Workbooks.Add
- 'ÂsÄý¿ï¾Ü¸ê®Æ§¨
- With Application.FileDialog(msoFileDialogFolderPicker)
- If .Show = -1 Then
- If .SelectedItems.Count > 0 Then fd = .SelectedItems(1) & "\"
- Else
- Exit Sub '¨ú®ø
- End If
- End With
- '¹ï©Ò¦³¸Ó¸ê®Æ§¨¤Uªºtxt³B²z
- f = Dir(fd & "*.txt")
- Do While f <> ""
- 'Ū¨úÀÉ®×
- fnum = FreeFile
- Open fd & f For Input As #fnum
- '¥ÎSplitåªR«e¤K¦æ¸ê®Æ
- ReDim arData(0 To 7)
- For i = 0 To 7
- If EOF(fnum) Then Exit For 'YÀÉ®×¥¼¹F¤K¦æ«h¸õ¥X
- Line Input #fnum, dataLine
- s = Split(dataLine, " ", 2) '¨î³Ì¦h¶Ç¦^ªº¤l¦r¦ê¼Æ¬°2Ó
- If UBound(s) = 1 Then arData(i) = s(1)
- Next
- ReDim Preserve ar(UBound(ar) + 1) '«O¯d¨Ã¼W¤j°}¦C
- ar(UBound(ar)) = arData
- Close #fnum '°O±oÃöÀÉ®×
- f = Dir
- Loop
- With .Sheets(1)
- .Columns("A:A").NumberFormatLocal = "@" 'AÄæ®æ¦¡³]¬°¤å¦r
- .Range("A1").Resize(UBound(ar) + 1, 8).Value = Application.Transpose(Application.Transpose(ar)) '¶ñ¤J¸ê®Æ
- .Range("A1").Resize(UBound(ar) + 1, 8).EntireColumn.AutoFit '½Õ¾ãÄæ¼e
- End With
- '¦sÀÉ
- fo = Application.GetSaveAsFilename(InitialFileName:=fd & "final.xls", FileFilter:="Excel Files (*.xls),*.xls", Title:="Àx¦sÀÉ®×")
- '°£«D«ö¨ú®ø, §_«h¦sÀÉ
- If TypeName(fo) = "String" Then .SaveAs Filename:=fo, FileFormat:=xlExcel8
- End With
- End Sub
½Æ»s¥N½X |
|
|
|
|
|
|