Excel 2007 VBAŪTXTÀɨÃÂà¸m
- ©«¤l
- 5
- ¥DÃD
- 2
- ºëµØ
- 0
- ¿n¤À
- 12
- ÂI¦W
- 0
- §@·~¨t²Î
- win
- ³nÅ骩¥»
- xp
- ¾\ŪÅv
- 10
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2013-6-25
- ³Ì«áµn¿ý
- 2020-5-19
|
Excel 2007 VBAŪTXTÀɨÃÂà¸m
¦bc:\file\¤U¦³1.txt,2.txt....¤£¤@©w¦³´XÓÀÉ®×,ÀɦW¬°¼Æ¦r»¼¼W. ¨CÓÀɮ׮榡³£¬Û¦P,¦p¤U:
¨Ò¦p: 1.txt
¦W¦r ¤ýxx
¼Æ¾Ç 58
^¤å 63
¦a²z 90
¤Æ¾Ç 80
2.txt
¦W¦r ªLx
¼Æ¾Ç 100
^¤å 6
¦a²z 60
¤Æ¾Ç 58
txt¤¤©T©w³£¬O5 rows, 2 columns, column¶¡¥ÎªÅ®æ®æ¶}, n¥Î°j°é±Nc:\file\¤Uªº50ÓÀÉ®×Ū¤JExcel,¨ÃÂà¸m¦¨¤U,row 1©T©w¬OÄæ¦ì¦WºÙ, row 2¶}©l¤À§OŪ¤J©Ò¦³ÀÉÂà¸m, ¦ý¥uŪ¨ì¦a²z, ¤£Åª¤J¤Æ¾Ç¨º¤@row¸ê®Æ.
¦W¦r ¼Æ¾Ç ^¤å ¦a²z
¤ýxx 58 63 90
ªLx 100 6 60
.
.
.
¨ì³Ì«átxt¨ºµ§
³Ì«á¦s¦¨c:\file\final.xls
·PÁÂ! |
|
|
|
|
|
|
- ©«¤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
- 5
- ¥DÃD
- 2
- ºëµØ
- 0
- ¿n¤À
- 12
- ÂI¦W
- 0
- §@·~¨t²Î
- win
- ³nÅ骩¥»
- xp
- ¾\ŪÅv
- 10
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2013-6-25
- ³Ì«áµn¿ý
- 2020-5-19
|
stillfish00±z¦n, Y¸ê®Æ§ïµ{¦p¤U:
1.txt
Àç·~¤H²Î¤@½s¸¹ 11111111
t³d¤H©m¦W ³\xx
Àç·~¤H¦WºÙ xx¥ø·~ªÀ
Àç·~¡]µ|Äy¡^µn°O¦a§} xx¥«xx°Ïxx¨½xx¸ôxx«Ñxx¸¹x¼Ó
¸ê¥»ÃB(¤¸) 100000
²Õ´ºØÃþ ¿W¸ê( 6 )
³]¥ß¤é´Á 1020723
µn°OÀç·~¶µ¥Ø ´¶³qÜÀx¸gÀç( 530100 )
²î¤W³fª«¸Ë¨ø( 525912 )
¥]¸Ë©ÓÅóªA°È( 820914 )
2.txt
Àç·~¤H²Î¤@½s¸¹ 01111111
t³d¤H©m¦W ¶Àxx
Àç·~¤H¦WºÙ xx¥ø·~ªÀ
Àç·~¡]µ|Äy¡^µn°O¦a§} xx¥«xx°Ïxx¨½xx¸ôxx«Ñxx¡Ðx¸¹x¼Ó
¸ê¥»ÃB(¤¸) 60000
²Õ´ºØÃþ ¿W¸ê( 6 )
³]¥ß¤é´Á 0780522
µn°OÀç·~¶µ¥Ø ¨ä¥Lª÷ÄݼҨã»s³y( 251299 )
.
.
.
®Ú¾Ú±zªºµ{¦¡×§ï¦p¤U:
Sub importtxt()
Dim path, folder, fname
With Workbooks.Add '¼ÐÃD¦C
.Sheets(1).Range("A1:H1") = Array("Àç·~¤H²Î¤@½s¸¹", "t³d¤H©m¦W", "Àç·~¤H¦WºÙ", "Àç·~¡]µ|Äy¡^µn°O¦a§}", "¸ê¥»ÃB(¤¸)", "²Õ´ºØÃþ", "³]¥ß¤é´Á", "µn°OÀç·~¶µ¥Ø")
'·s¼W¼È¦s¸ê®Æªí
With .Sheets.Add(after:=.Sheets(.Sheets.Count))
'ÂsÄý¿ï¾Ü¸ê®Æ§¨
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
If .Show = -1 Then path = .SelectedItems(1) & "\"
End With
'¹ï©Ò¦³¸Ó¸ê®Æ§¨¤Uªºtxt³B²z
folder = Dir(path & "*.txt")
Do While folder <> ""
.Cells.ClearContents
'¶×¤J¥~³¡¸ê®Æ
With .QueryTables.Add(Connection:="TEXT;" & path & folder, Destination:=.Range("A1"))
.Name = "¸ê®Æ"
.RefreshPeriod = 0
.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(, 8).Value = Application.Transpose(.Range("B1:B8").Value)
folder = Dir
Loop
'§R°£¼È¦s¸ê®Æªí,¤£Åã¥Üĵ§iµøµ¡
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End With
.Sheets(1).Activate '¨Ï¶}±Ò¸ÓÀɮɪ½±µ¨ì²Ä¤@Ó¤u§@ªí
'¦sÀÉ
fname = Application.GetSaveAsFilename(InitialFileName:=path & "final.xls", FileFilter:="Excel Files (*.xls),*.xls", Title:="Àx¦sÀÉ®×")
'°£«D«ö¨ú®ø, §_«h¦sÀÉ
If TypeName(fname) = "String" Then .SaveAs Filename:=fname, FileFormat:=xlExcel8
End With
End Sub
¦ý¬O·|¦³3Ó°ÝÃD,
1. Y"Àç·~¤H²Î¤@½s¸¹"²Ä¤@½X¬O0, Ū¤J«á²Ä¤@½X0·|¤£¨£.
2. "²Õ´ºØÃþ"ªº¸ê®Æ,¨Ò¦p¬O: ¿W¸ê( 6 ), ¦]¬°(©M6¤¤¶¡¬OªÅ¥Õ, ¸ê®Æ·|¥uŪ¨ì"¿W¸ê("´N°±¤î.
3. "µn°OÀç·~¶µ¥Ø",¤]¦³¹³²Ä¤G¶µ¤@¼Ëªº°ÝÃD,(«á¬OªÅ¥Õ,«á±ªº¸ê®Æ´N·|¤£¨£.
·Q½Ð°Ý¦p¦ó¸Ñ¨M³o¤TÓ°ÝÃD? ¤Ó·PÁ±zªºÀ°¦£¤F~ |
|
|
|
|
|
|
- ©«¤l
- 5
- ¥DÃD
- 2
- ºëµØ
- 0
- ¿n¤À
- 12
- ÂI¦W
- 0
- §@·~¨t²Î
- win
- ³nÅ骩¥»
- xp
- ¾\ŪÅv
- 10
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2013-6-25
- ³Ì«áµn¿ý
- 2020-5-19
|
¦^´_ 2# stillfish00
ÁÂÁÂ, ¦ý¬O¸ê®Æ§ïÅÜ«á, ·|¦]¬°0©MªÅ¥Õ³y¦¨°ÝÃD, ·Q¦A¦V±z½Ð±Ð, ·PÁ±z! |
|
|
|
|
|
|
- ©«¤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 |
|
|
|
|
|
|
- ©«¤l
- 5
- ¥DÃD
- 2
- ºëµØ
- 0
- ¿n¤À
- 12
- ÂI¦W
- 0
- §@·~¨t²Î
- win
- ³nÅ骩¥»
- xp
- ¾\ŪÅv
- 10
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2013-6-25
- ³Ì«áµn¿ý
- 2020-5-19
|
¦^´_ 5# stillfish00
·PÁ±z, §Ú«á¨Ó¥Îlen°µªø«×§PÂ_,¦A¥[¤W¥Î&°µ¦X¨Öªº¤è¦¡¸Ñ¨M,¦ýÁÙ¬O±zªº°µªk¬O¤ñ¸ûeffective. Thanks. |
|
|
|
|
|
|